Power Query - Advanced Transformation of Nested Tables

Рет қаралды 3,568

5 ай бұрын

🚀 Unlock the Power of MQuery: Dive into the depths of Power Query with this tutorial! 🤓 In this KZfaq video, I guide you through the advanced magic of transforming nested tables without relying on the user interface. 🔄💡 Elevate your MQuery skills and gain a deeper understanding of how this powerful tool works behind the scenes. 📊✨ Let's transform data like pros! #PowerQuery #MQueryMastery #DataTransformation 🚀 #powerbi #powerbidesktop #excel #dataanalytics #data
File Links:
Source Files
github.com/jbotes/powerbiTuto...
github.com/jbotes/powerbiTuto...
github.com/jbotes/powerbiTuto...
Solution File
github.com/jbotes/powerbiTuto...
Links for Mquery Function documentation:
Getting files from folders - Folder.Files
learn.microsoft.com/en-us/pow...
Transforming existing columns - table.transformColumns
learn.microsoft.com/en-us/pow...
Select specific rows in a table - Table.SelectRows
learn.microsoft.com/en-us/pow...
Skip a number of rows in a table - Table.Skip
learn.microsoft.com/en-us/pow...
Promote table headers . Table.PromoteHEaders
learn.microsoft.com/en-us/pow...
Unpivot Data in a table - Table.UnpivotOtherColumns
learn.microsoft.com/en-us/pow...
Add a column to a table - Table.AddColumns
learn.microsoft.com/en-us/pow...
Split a column in a table - Table.splitcolumn
learn.microsoft.com/en-us/pow...
Combine nexted tables as one - table.combine
learn.microsoft.com/en-us/pow...

Пікірлер: 38

@thebhaskarjoshi 5 ай бұрын

Your unique way of teaching M language never fails to amaze me.

@basensei8699 5 ай бұрын

thanks man!

@larsaustin 5 ай бұрын

Your channel should have 50x more subscribers at least with the quality of your content. I hope you don’t stop making useful tutorial videos like this.

@basensei8699 5 ай бұрын

Glorious thank you!

@fajnefajne4339 Ай бұрын

Wow! So much nice content presented straight to the business. Thank you.❤

@mnowako 2 ай бұрын

WOW! This is much more than amazing! I think one of the best video on the subject. Many thanks!

@roywilson9580 5 ай бұрын

Thanks for another great video. I always prefer to do as much work in the nested tables as possible, it seems quicker and has the great bonus that it dicourages the guys who think they know what they are doing from editing and breaking your query as they see all the brackets and indentations and can't work out what part of the code they want to change.

@basensei8699 5 ай бұрын

hahaha i totally do the same thing!

@roywilson9580 5 ай бұрын

@@basensei8699 Lol I like to make myself as indispensible as possible!

@williamarthur4801 3 ай бұрын

New viewer / subscriber, loved the use of record, I've always just piled transformations on top of each other, putting them in the form of a record is so much easier to read.

@basensei8699 2 ай бұрын

Awesome! Thank you!

@larmondoflairallen4705 3 ай бұрын

That was a wild ride! Awesome!

@FRANKWHITE1996 3 ай бұрын

power query Genius 😊

@whizzohk 5 ай бұрын

Pretty powerful stuff indeed! So much more interesting and useful that you didn't just take the easy way. Thank you Sensei!

@basensei8699 5 ай бұрын

what a pleasure!

@tterrabend 2 ай бұрын

I appreciate your “get right to business” method of teaching. This video was mind-blowing! When I navigated to “Like” the video, I discovered I was not yet a subscriber?!?! Oops! I am subscribed now, because I always learn something new from you. Also, I see musical instruments behind you. I’m compelled to support a fellow musician and Power Query teacher-two of my favorite pastimes! Thank you!!

@basensei8699 Ай бұрын

how glorious!

@jazzista1967 5 ай бұрын

This is the best example of using very powerful Mcode functions and nested tables in one tutorial... I will download the file and practice. I have several files like this at work and i would like to redo them using these techniques. Thanks

@basensei8699 5 ай бұрын

thats so great !

@pamphlex 2 ай бұрын

Thank you, this video has helped me a lot! Those are tricks I use on a regular basis now.

@basensei8699 Ай бұрын

Glad it was helpful!

@raulparra60 4 ай бұрын

Otros tutorial muy bueno. Gracias por compartir

@kebincui 5 ай бұрын

Another excellent video 👍👍 Thank you so much for sharing. It is always enjoyble and beneficial watching your videos. Keep up the good work ❤🌹

@basensei8699 5 ай бұрын

Thank you very much!

@alexrosen8762 5 ай бұрын

A great and very useful tutorial 👌

@aart_analyst 5 ай бұрын

Brilliant, as usual.

@richp2894 4 күн бұрын

Great video! Thanks for all your content! I've been looking for a way to pull in a value from the 'outer table' into my nested table for hours. All examples seem to have to add a new column to the outer table first. Is there a way to add the "PortfolioAndSource" column in your example directly to the nested table? - Best.

@victorsolano9420 Ай бұрын

@BA Sensei Great video. Very clear explanation Quick question. Can you take a column from the newly created nested table and transform the column while the table is nested.

@SamehRSameh 2 ай бұрын

Plz need another video use table.transformcolumns & if statement with multiple condition

@jimfitch 3 ай бұрын

Lots of great stuff here. Thank you! Which is more performant: traditional methods (using UI) or nested table transformations?

@basensei8699 3 ай бұрын

definitely Nested table transformations!

@Giridharan952 3 ай бұрын

Curated and Sharp....

@whizzohk 5 ай бұрын

In that final transformation step to populate Portfolio and Source in the inner table, you did that by adding a column in the outer table. Is it possible to instead use TransformColumns on the outer table, so that you don't need to add a column to it in order to extend the inner table? I had a go at this but I couldn't get the syntax to work as Name.1 and Name were not in scope.

@basensei8699 5 ай бұрын

yes this is totally do able!

@SamehRSameh 5 ай бұрын

Why you don't use let & in in start but use it in final ??

@basensei8699 5 ай бұрын

i want to show there are many ways to do the same thing ;)

@SamehRSameh 5 ай бұрын

@@basensei8699 marvelous, plz always give us such these