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

★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Check out the blog post★
exceloffthegrid.com/transform...
★ 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 ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel

[@Shriraj27]

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

[@chilaw2004]

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!

[@ExcelOffTheGrid]

Exactly! It's like creating a parameter free custom function, but inserting all the code into the main query rather than in a separate query.

[@ziggle314]

Wonderful technique. Thanks Mark.

[@ExcelOffTheGrid]

Thanks Ziggle. I hope you can put it to good use. 😁

[@LotfyKozman]

Very useful and practical video

[@ExcelOffTheGrid]

Thanks - I hope you can put it to good use.

[@datawizibility]

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

[@ExcelOffTheGrid]

Thank You. I’m glad you like them.

[@iankr]

Ingenious! Many thanks, Mark.

[@ExcelOffTheGrid]

Thanks Ian 👍

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

[@ExcelOffTheGrid]

Thanks Ivan - I hope you can put it to good use. 👍

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

[@ExcelOffTheGrid]

You are very welcome

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

[@ExcelOffTheGrid]

Thanks Kebin. I though you might like it 😀

[@jasperchangco1072]

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

[@ExcelOffTheGrid]

Yay!! Great news 👍

[@RajKumar-hw4bt]

Awesome

[@teinoxtube]

Brilliant and clearly explained... thanks a lot!

[@ExcelOffTheGrid]

Thanks, I’m glad it was helpful 😀

[@GrainneDuggan_Excel]

This is definitely a keep for future reference video.

[@ExcelOffTheGrid]

Thanks - I think it's a really simple way to solve a complex problem 😁

[@eduardomunoz2764]

Wonderful technique, thank you!..

[@albertogp84]

Unbelievable Trick! Thank you! You are phenomenal

[@ExcelOffTheGrid]

I hope you can put it to good use.

[@vacilando86]

Amazing, 🎉

[@ladvozer]

thanks!

[@rrrraaaacccc80]

Great 💯👍

[@ExcelOffTheGrid]

You’re welcome.

[@syrophenikan]

Great job!!!!!! Great explanation!!!!

[@ExcelOffTheGrid]

Thank You 😁

[@mudassirsyedrashidali9787]

Brilliant

[@emilmubarakshin49]

That’s a cool trick

[@ExcelOffTheGrid]

Hopefully you can use it to simplify some complex queries in then future.

[@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?

[@ExcelOffTheGrid]

Yes, that should work.

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