Transform nested tables in Power Query (without writing M coding) | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★

★ Check out the blog post★

★ About this video ★
In Power Query, it is common to have nested Tables. These are Tables contained within a column, where each row contains a separate sub-Table. Sometimes we just want to expand the data; that is easy. But sometimes, we need to transform the data in the nested Table before expanding; that is NOT so easy. So, how can we achieve this?

That's what we are looking at in this video; how to transform nested Tables in Power Query without writing the M code.

0:00 Introduction
0:34 Example Data
1:57 Apply to one table
3:46 Get the code
5:00 Apply to each nested table
7:30 Conclusion

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[shubhabratadey]

Very helpful video. Thanks for sharing

[chilaw]

This is a phenomenal video if you have multiple tables that need to be transformed in the same way. It’s essentially like creating a custom function but embedding it in the query with the trick at the end using the switcheroo between the Name and Data columns to get the code applied to the nested tables. Bravo!

[Shriraj]

Used this in one of the first record lookup in the nested table, that too in PDF file.... Loved the trick

[IvanCortinas_ES]

As powerful as ever. Absolute mastery of Power Query.
Thank you Mark, for so much important information that you offer with these tutorials.

[ExcelUnlocked]

Dear Mark, amazing video as usual, with crystal clear explanation, you made it sound so easy..., thank you for your time and for sharing the knowledge.

[kebincui]

Excellent Tip. Previously I used a converted function from the single table transformation step. Mark idea is mind blowing and thank you for sharing your creative approach 👍.

[datawizibility]

Love your videos, it makes life so simple...Thanks Marks

[GrainneDuggan_Excel]

This is definitely a keep for future reference video.

[jasperchangco]

Amazing! This really solves my problem on the project that I'm currently working.

[albertogp]

Unbelievable Trick! Thank you! You are phenomenal

[teinoxtube]

Brilliant and clearly explained... thanks a lot!

[dorotakantnerova]

Do you have some ideas how to dynamically transform each line from table to sheets using power query? (table is small 15-25 rows, but I get it repetitively)

[ExcelUpNorth]

Great video! Thank you for sharing this. Is it possible to add a column (let's say containing TRUE or FALSE) to a nested table and then apply a filter to that column, all before the expanding step?

[arpwable]

I've been trying to get this to work, but am running into a problem. When I set up the individual table transformation in the duplicate query it works perfectly for that table and for any others I try. But once I embed the code in my main query, the tables throw an error (we cannot convert the value #date(2024, 4, 1) to type List. Details show that Type=[Type]). Any idea what could be causing this? It's baffling given that it works perfectly without errors when not embedded!

[MuhammadRizwan-tczp]

This tutorial helped me a lot. But I have a situation in which i have files having different number of columns. I have YEARs as column names. So let say 1 file has 4 years columns and 2nd file has 7 years columns.
Your solution gives me result picking 4 columns for each file. it should pick 4 columns from 1st file and 7 columns from 2nd file making total columns 11 in merged file. currently its giving me 8 columns. Can you plz help me solving this scenario!!?? Thanks.