Dynamically expand table column in Power Query (advanced)

[@wayneedmondson1065]

This is great! Thanks for the awesome lesson. Note that it is possible by slightly modifying your single table method to account for multiple tables using List.Union in the Table.Expand step, as follows: = Table.ExpandTableColumn(#"Removed Other Columns", "Data", List.Union(Table.AddColumn(#"Removed Other Columns", "ColumnNames", each Table.ColumnNames([Data]))[ColumnNames]), List.Union(Table.AddColumn(#"Removed Other Columns", "ColumnNames", each Table.ColumnNames([Data]))[ColumnNames]). This will give you the same result as your method and dynamically handle column changes to any of the source tables. If you want, you can also wrap the List.Union construction with List.Sort to get the columns sorted left to right either ascending or descending with the Order.Ascending or Order.Descending argument inside List.Sort. Just another way to get there. Thanks for sharing your tips! Learned a lot. Thumbs up!!

[@ExceedLearning]

Hi Wayne, A nice way of doing the dynamic expansion of the columns, thanks for sharing!

[@wayneedmondson1065]

@@ExceedLearning Glad to contribute! Cheers!!

[@spilledgraphics]

@@wayneedmondson1065awesome seeing on this channel as well !

[@wayneedmondson1065]

@@spilledgraphics Cool! Cheers!!

[@Hacks_at_work]

i would like to say big big thank you. i stuck in power query exactly i am facing like this problem. i search a lot in you tube finally your video solve my problem.

[@malchicken]

Very concise and understandable, thank you for being so clear. The “hold Alt to open a second excel” trick alone will save me many hours of frustration :D I have watched many videos, but never seen that mentioned before. Thank you!

[@ExceedLearning]

It also bothered us a lot, and were quite amazed when we would out about this feature!

[@fcoatis]

This channel is exactly what I was looking for. Thank you

[@ExceedLearning]

Thank you Fabio! Glad you like it!

[@ALex-fp2zx]

Thank you! Your chanel ist the very useful for andvanced stady of POwer Query! Coll technuques!

[@Alan.DL7]

Great tip. I have used some similar technique which it's using the same logic as end result but it took me way more steps than yours. I have a weekly report with 52 rolling weeks meaning that for each new report, 1 week will disappear and a new one will be added. It was a headache to have it solved! But, for sure I will be replacing mine with this solution :)

[@rick_.]

I like your channel, very informative! For this sort of thing I like to use Table.Combine and pass in the name of the column holding the tables.

[@syedaneesdurez9880]

Hello, Thanks for sharing this technique. Amazing..

[@nishantkumar9570]

It's really awesome amd informative. Thank you very much.

[@FarshidMeidani]

Hi , You can use CTRL, SHIFT, +/- to zoom in / out power query editor or advanced editor , And this useful when you recording these videos.

[@paspuggie48]

Fantastic tip to open another excel workbook. I never you could do that 👌

[@ExceedLearning]

Thank you Paul, that's one of the best tricks we've learned about PQ in Excel so far!

[@paspuggie48]

@@ExceedLearning I've been using it for 2 years and watched and read so many articles in my time and that is the first time I've known about this. So thank you so much for sharing :)

[@paspuggie48]

And you can open 2 instances of the Query Editor too..woohoo

[@qasimjan5258]

Hi friend. Thank you so much for sharing your knowledge with us. Could you also do a video on " Dynamically expanding table rows".

[@ExceedLearning]

Hi, thank you for your comment! Could you please explain in more detail what do you mean by dynamically expanding rows?

[@zaydarendse2812]

amazing! thanks for sharing

[@joseeduardoacostarodriguez3031]

Excelente, no se inglés y lo entendí perfectamente. Una solución K.I.S.S.

[@kennethstephani692]

Great video!

[@cosladacity1]

you are a genius man!! id love to invite you a beer now ;)

[@antoniofelez4674]

What if I want to do other transformation on the nested tables before expanding?

[@SaniGarba]

Thanks

[@vladtolievich8039]

Hello, thanks for sharing this technique. But I just tested it and realized that comparing to regular (hard coded and not dynamic) column expansion way, this is too slow. My refresh took almost one minute instead of 20 seconds for 2161 rows and 36 columns (also i had to convert column list into table and back to list in order to filter out some not needed columns). But still interesting.

[@ExceedLearning]

Hi Vlad, thanks for the input. Like we mentioned in the video, this technique won't always produce better results. I'm guessing it would be faster in case you need to pivot only a few columns and w/o any intermediate steps, but we haven't tested it enough to have a general approach on when exactly to use it compared to a regular Pivot.

[@amani1997]

Wow thanks

[@MehmetAliMD]

Thank you so much. How about if I wanna dynamically expand the table columns from different excel or CSV files?

[@kebincui]

Super ❤

[@retamapark]

Nice!

[@KoolGuyism]

How can I expand columns without using any code..or.. can you give this code in writing as i am not good with codes.

[@SamehRSameh]

Scrren is very small plz zoom in

[@Softwaretrain]

Thanks but you did it in a long way, simple and easy way is useing Table.Combine function.

[@ExceedLearning]

Hi, Thank you for your comment! :) This technique describes a solution to dynamic table column expansion, not combining tables. The three tables used in the video are just for demonstration purposes, i.e. for demonstrating how to expand all columns dynamically when nested tables are different. This is applicable in many different scenarios such as merging, invoking custom functions, processing folders, etc. In those scenarios, we cannot append the tables with Table.Combine, but we need to expand the column that contains nested tables. You can see another use case (merging queries) in our previous video: kzbin.info/www/bejne/n6G1goKEr5l6hac