Chandeep v/s Mark | Who will win? Power Query Tricks Battle ⚔ |

[@GoodlyChandeep]

Don't forget to give us points in the comments and let's see who wins! 😉 Mark's channel - www.youtube.com/@ExcelOffTheGrid Promote Double Headers in Power Query | Solution - kzbin.info/www/bejne/nKLPaYBupsmJiNk Download the file ⬇ - goodly.co.in/Chandeep-vs-Mark-Power-Query-Tricks-Battle Master M in Power Query - goodly.co.in/learn-m-powerquery/ Don't forget to hit the like button if you enjoyed the challenge, and subscribe for more awesome content!

[@thicketroad]

I think we're all winners with this vid.

[@jeromeastier462]

Totally!

[@ratulbanerjee]

Yes

[@ziggle314]

Two of my favorite Excel gurus having a friendly competition. It doesn't get any better than this. Thanks!

[@nizar_arena]

Timestamps to help : trick 1: 4:19 trick 2: 8:25 trick 3: 10:55 trick 4: 14:51 trick 5: 18:10 trick 6: 21:17 trick 7: 26:10 trick 8: 29:26 trick 9: 31:01 trick 10: 32:58 trick 11: 35:44 trick 12: 39:33 trick 13: 41:20 trick 14: 47:36

[@jean-claudebrunet1645]

Guys, you are genius . You are equally brilliant. This is a draw but the real winner is me a I am the beneficiary of your tricks 😃

[@GeertDelmulle]

For me it’s Mark for the win because of the following canons he pulled out of his hat of tricks: “flatten headers”, “query in a step” and “Merge does the fastest exact match”. They made the difference for me. All the tricks are good, but those stood out for me.

[@ratulbanerjee]

How does “flatten headers “ makes difference, it’s a custom function

[@NeshaStanic]

This is great. You should practice this often. No winners here, only us, audience.

[@omarbbah]

This is just awesome! Just to add to Mark's first trick, instead of manually typing the date you can create a Table of dates from the files in the folder by creating a new query and maintaining the file name column. You output this as a table and create a data validation from it. This will eliminate the manual input of dates and mitigated errors.

[@Nousername88]

I rarely have enough attention span to sit and watch anything longer than 20mins video. This was one of the rare ones where I was able to do that. Thanks mark and chandeep, you guys should keep the collaboration going !

[@michaeloloishuro6915]

Hi Team of Champs>Mark and Chandeep. Having been following the two, I know you are both Giants and the synergy here is explosive. Great job and justice done to educating us on various employable tricks. Thanks.

[@zuzanasmekalova4632]

Great video of two great PQ masters! From my current user level/knowledge I give 4 points for Chandeep for "add more columns in one step", "automatically promoted headers", "is number/is text"; "transform all columns" and 3 points to Mark "filtering data with right outer merge", "get the info/date from removed rows", "query in custom column" As somebody wrote before me - we are all winning here! :) thanks!

[@txreal2]

I follow and admire both. Chandeep got the bonus +1 point just because he promotes this ideas ✨ and shares with us. Now that's Goodly stuff 👍

[@jist6953]

Watching again and again. The concatenation and appending columns in one step was very cool. Both of you bringing some great content. Mark's is awesome for solving tricky situations. Chandeep's tips I will probably use more often. Tie contest waiting for the tie-breaker.

[@txreal2]

Yes, another video for tie-breaker :)

[@williamarthur4801]

Really liked the add columns, I was thinking of your 'creating dynamic columns ' challenge, where I'd changed it so then names were header and 'hobbies ' row headers, done this a few ways, and think this may be a new way. find 'off the grid ' really useful long before I found mark on youtube

[@bendreessen2809]

Awesome tricks from both Chandeep and Mark! Several tricks are immediately applicable to my queries. The most helpful include Projections, Local Grouping, Dynamic File Path, Fetching Date Columns, and Use Merge to Filter. Thank you both for the tips! As for the points Mark - 4 Chandeep - 3

[@shirleymoreman6725]

Mark 7 Chandeep 7! You guys! Those were 14 absolutely awesome tricks. Thanks for putting this together.

[@muhammadtambawala6379]

Thanks

[@BIGorilla]

What a video the Power Query community much needed. Two giants, one battle. Both with endless moves to shoot at each other. Im glad you finally agreed to a truce. The world gets to live another day. I bet this battle could have lasted for weeks. 😉

[@jist6953]

Wonderful contest between two Grand Master wizards casting their M magic. Removing Null columns is sick and I am using today. This would be such a fantastic regular series!

[@frankschadler9407]

Wow. Serveral awsome tips, thank you, Chandeep and Mark. A voting is difficult. I know most of your tips, Chandeep. And as a former SAP-R3 user, I can imagine that Mark's tips are more than helpful for those who suffer from these special tables where essential information is above the table. Please allow some questions: Why did you, Mark, use merge / right outer instead of inner join? (Battle 6) What is the benefit of the code optimizations? I ask because I always have to correct code to respond to changes in the data. If you summarize everything, checking and correcting becomes difficult.

[@retamapark]

Thanks!

[@rajanpradeepankarath8846]

It is not about the points, it is rather about the value you both imparted to us. Thanks both

[@anillpatel]

What a privilege to learn from two awesome 'Powerhouses'. I'm definitely a winner out of this...., Thank you Chandeep and thank you Mark....,

[@baskis69]

Brilliant, spectacular tricks, thanks for sharing. My favourite is Mark's trick #7.

[@McIlravyInc]

I was searching just today for a way to clean all columns without hard coding and couldn't find anything so the the empty list brackets tip is very timely! And this was fun to watch too which actually makes it easier to remember. Bravo to you both!

[@nicolasyans7986]

Same here you guys are both giants. Each with its own specificities and coding style. Discovered the table.profile function that will come handy at times (thumbs up for Mark). Everlasting thanks to Chandeep for his video on Table.transformcolumns tutorial. I didn’t’ know Mark at the start of my PowerQry journey, so my vote goes to Chandeep who has taught me so much. But keep it up hoth if you your work is so inspiring.

[@dharmmu]

Mark tricks was more which we require in day today professional work....

[@sivakumarilluru560]

Thanks Chandeep and Mark ...For my Prospective both are winners .. Once again Thank you for sharing Best tricks in power Query.

[@Ratnakumarwrites]

Nice Collab... I learnt lot from both of you... I like Chandeep's great way of teaching style, I learnt more from him.. Simple and elegant, superb. Mark I like his tricks great.

[@PeterTurrell-cg9ji]

Both of you presented thoughtful and worthwhile tricks. A tie well done.

[@navisalomi]

Table.Profile from Mark, Groupkind.local from Chandeep. Awesome. Great guys 🎉

[@whizzohk]

Entertaining AND educational... thank you both! You are both winners in my book. Really appreciate you sharing the actual files you were working with. Plenty of useful nuggets in there.

[@AnbarasuAnnamalai]

Awesome tips. Thank you Chandeep for bringing Mark. You both are rocking.

[@fajnefajne4339]

The score is 10:10. All tricks are amazing and I love them. Thank you both for this great competition. I feel a winner too, because I have learnt a lot. ❤

[@JUHILLAPSIWALA007]

You both are my idol as both sides of the coin. ❤

[@isaacmireku5805]

Very Impressive @GoodlyChandeep. Bless you

[@vsrinivasan574]

Very Very Useful tips, both have made us win, excellent, Thanks,. Removing blank columns made my life easy

[@VesBuk28]

Great tricks ! Need part 2🎉

[@rachidwatcher5860]

Thanks to both of you. Amazing tricks!

[@kaushikbarot9653]

Getting a single column as a table was new to me. It's great !!

[@irenegurne8953]

What a wonderful combination!! very amazing tricks! All of each are what I need! I give Mark 7 and Chandeep 7 pts.

[@oleksandrbielov4680]

Great format, thanks a million! All tricks are awesome. My score 7-7.

[@FsoOmar]

Wow! The GOATs of PQ showing their magic in front of our mesmerized eyes! This is a compact video, total of 14 into one AWESOME video. You guys were both great and sport. Chandeep deliberately threw a weak first hand, as he's the host.😉 😄 Mark 7, Chandeep 6. 😅

[@sebfromgermany3819]

Lovely idea to have a competion that boosts creativity. I follow both of these geniuses-each with its own admirable style👏. This competition shows once more both gentlemen know ALL the awesome tricks of the trade 🪄. Mark 7-Chandeep 7 + 1 bonus point, because you can't beat the simplicity of the trick of using records to add columns. Great of you both - Cheers guys! The audience is the ultimate winner 🎉

[@wynhopkins4023]

Bravo! An honourable draw covering some great tricks

[@syedbarkathussaingilani324]

You are both awesome and have excellent skills in Power Query

[@ianaugusto888]

Vocês são INCRIVEIS!!! 7 x 7 😍😍😍😍😍😍

[@edsta714]

King Kong vs Godzilla. Let them fight.

[@arokiarajan1230]

Flattenheaderrows - Mark Great-tips. Thanks mate.

[@valeriastrafe5104]

Wow wow wow… LOVE THIS!! 200 points each!

[@christiankramer8526]

You are amazing, you are both winners !!! Well done !!! 👍👍👍

[@rubenmunozverdu7528]

Very cool bag of tricks! However, I must say I disagree with Mark's 6th trick. If you use List.Buffer in addition to List.Contains that will be faster than merging. Something like each List.Contains(List.Buffer(yourListName),[columnToBeFiltered])

[@advrohitowhal9794]

Mark 7 | Chandeep 7. Wonderful job done. Thanks a lot!

[@husnainmanzoor8665]

That add query into custom column. Lovely

[@jaybuddhadev3543]

both were great . got to learn many new tricks

[@shellycastille4991]

More, more, more. This was an absolutely fabulous idea for a video.

[@nadeemkhaja7633]

Guys, you both are brilliant.

[@rrrraaaacccc80]

Great both of you 💯👍

[@priyeshsanghvi8424]

Clash of the Titans ! Chandeep is my Guru......Goodly = Godly....... but Mark is also no less. Both get 5/5 Stars. The last tricks from both were awesomely mindblowing (all were).....hoping to see more of these type of videos ( in addition to your regular one :) )

[@SeelanRobert]

You guys are Awesome Amazing PQ Tricks at the end everyone is benefited. Thank you so much

[@leemorgan8007]

Amazing work to both of you - some of this blows my mind and I now need to see how I can translate this into my work. I scored Mark 6 - Chandeep 6 - a worthy draw.

[@seamushand8439]

Great tips and a very interesting and engaging format.

[@mikesnow5698]

What a great video! Awesome tricks all the way around. I'm always looking for ways to cut out steps in my queries and these tips blow my mind! Mark - 6 Chandeep - 6

[@kebincui]

For Mark's tip No. 4 (Removing Null Columns), if you first change the data type to whole number, then you can apply filter to [NullCount] column without the bug error message (at 27':32'' of the video)

[@User-jr5xf]

Just WOW ! I think i have several uses for this, and thanks Gents for taking the time. I'd say 7-7. I need to test what i've learned, and may have a good example, for a follow up video, if i can't understand it myself.

[@Maartenmarsje]

Brilliant video! Thanks you so much! I’ve learned a lot of new tricks due to your competition.

[@gennarocimmino]

You were simply fantastic and this idea of a trick 'challenge' is really exciting. You undoubtedly have two different styles of approaching solutions to everyday problems that arise in the world of data cleansing. My point scale leans towards Chandeep and for me the score is: Chandeep: 6 Mark: 5 But kudos to both of you and applause to Chandeep for organising this kind of really interesting meeting. Good luck to both of you

[@peterbebbington3435]

Excellent. Can you add the timings for each trick?

[@PowerMacro]

Most of the tricks you Chandeep already covered, which mark has shown. Full points to Chandeep. But, knowledge is knowledge. Wherever you get it Grab it. Thanks guys for wonderful healthy competition. Thank you thank you and millions of thank you.

[@skyking2202]

Further to Mark's point about performance on his Tip 5; I now try to avoid an use of the List.PositionOf function as a "lookup replacement." I too have found there is no comparison between merge performance and lookup performance. My understanding is that merging queries makes use of table optimizations that the List functions do not. Even a fairly small iteration count of lookups will bog your refreshes down dramatically.

[@GlenCoulthard]

Amazing tips -- one of the most useful videos I've watched in a long time!

[@elvyrorie1432]

Wow. Great video that Ive now saved for future reference. My scoring criteria was based on whether I knew the trick already. Chandeep 6 Mark 3. Although...if you wanted to weight these youd come out about equal in this competition. Good stuff y'all.

[@vaibhavthakur_1997]

Amazing 🙏

[@adityachouhan5968]

For me you both are the winner !!! but since 5 to 6 month i am following Chandeep and learnt lots of things , hence my softer side always goes to my guru (Chandeep).

[@GrainneDuggan_Excel]

Battle of the Titans! 14 fabulous tricks - thanks for this Mark and Chandeep!!! My score: Mark = 7, Chandeep = 7. However, I actually think it should be much higher for each of you. Definitely keeping this video in my refer to again file!

[@MrLuuHai]

Brilliant Both❤❤❤

[@danielblanc1370]

Thanks to both of you for the shared tricks. It's difficult to decide between you, the themes covered were different but all interesting. I would tend to give the victory to Chandeep because Mark's header trick was encapsulated in a predefined function so Chandeep =7 and Mark =6.

[@009hjs]

Both of you are super, thank you so much

[@umesh2403]

Am a happy boy too and am I did indeed have that rabbit out of hat moment in all those tricks. .guys I wish more of these short but useful titbits

[@erivaldolopes632]

@Chandeep, just because you didn't know about "is" keyword and what it does, I feel much knowledgeable as well. I have been using for testing what an object is M is. A tip for you @Chandeep, work with "as" keyword too. very helpful.

[@DinoDelight]

Thsnk you for this video, I keep coming back to this video as I keep requiring things that you have covered here, for example I used mark column location trick today....was a came changer for what I was working with

[@williamarthur4801]

Jus to update really loveed the add colun as record, one example; Table.AddColumn( TableofColumnHeaders , "Custom", each Record.FromList( Source [Item], Source [Name] )) then to check if value in each row / column = column header ; Table.ReplaceValue( #"Expanded Custom", each [ColumnHeader], "Attended", (x,y,z)=> if Text.Contains( x, y) then z else "" , Source [Name] )

[@sudbn]

That flattened header using function is very useful. Is there any way to learn

[@GoodlyChandeep]

m.kzbin.info/www/bejne/nKLPaYBupsmJiNk

[@bhagipatel5180]

Great Learning, you both are winners

[@valerylambert4673]

Excellent! I give all the points to each of you. For my point of view, you are ex aequo! It is an excellent idea. Feel free to do another challenge of this type whenever you want!

[@azeezwahab5188]

Two great mentors.

[@victor_wang_1]

Mark's trick number 6 is so important and so common to miss. No amount of custom M code will be better than simple joins.

[@sravankumar4860]

Damn I feel the video is literally short..keep doing this man. many thanks to you ❤❤

[@DanielBula1964]

You are geniuses. I call it a draw. Blessings!

[@piotrm7330]

Personally for me ... Mark 7, Chandeep 4 (tricks 2, 5-7), and not because I don't like you Chandeep ;), but because I find Mark's tricks more usefull. Anyway, both of you are worth to follow. Good job

[@waleedalhasan85]

You are amazing, you are both winners !!! ⭐️⭐️⭐️⭐️⭐️⭐️ Chandeep ⭐️⭐️⭐️⭐️⭐️ Mark

[@mohammedelsakally540]

Thank you Chandeep and Mark to give us the opportunity to watch this valuable content!

[@SundarP-pc1mr]

Hi Chandeep, Do we have any option to remove the promoted header option while connecting Google sheet,

[@jonallen9910]

Thank you for sharing your abilities

[@Rice0987]

Nice to meet you guys all together!🤗 You both are super boss!✊😁

[@RonnySadamHusen]

Both are the best. I give the highest point for both of them.

[@DinoDelight]

Every trick was great, but the one I know I will use constantly from know is the change all data by {}

[@justapasserby69420]

Unstoppable force, meet immovable object

[@chrism9037]

Two PQ masters!

[@thierrysouchard471]

Great tricks 👏 Thank you so much to keep on pushing the boundaries. To me it is a draw