Dynamically expand table column in Power Query (advanced)

In this solution, instead of using only one of the nested tables, we are using all the nested tables to extract all possible column names.

You can also find the written blog on basic and advanced solutions at the following link:

To learn more about Power Query, check out our Power Query fundamentals course!

#powerquery #powerbi #etltools #data #dataanalysis

[wayneedmondson]

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: = Other Columns", "Data", Other Columns", "ColumnNames", each Table.ColumnNames([Data]))[ColumnNames]), Other Columns", "ColumnNames", each 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!!

[fcoatis]

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

[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!

[ALex-fpzx]

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

[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.

[nishantkumar]

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

[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.

[syedaneesdurez]

Hello, Thanks for sharing this technique. Amazing..

[Alan.DL]

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 :)

[paspuggie]

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

[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.

[qasimjan]

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

[antoniofelez]

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

[Dumlumehmet]

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

[cosladacity]

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

[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.

[vladtolievich]

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.

[joseeduardoacostarodriguez]

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

[Softwaretrain]

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