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

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

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

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

[@ziggle314]

Wonderful technique. Thanks Mark.

[@ExcelOffTheGrid]

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

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

[@iankr]

Ingenious! Many thanks, Mark.

[@ExcelOffTheGrid]

Thanks Ian 👍

[@jasperchangco1072]

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

[@ExcelOffTheGrid]

Yay!! Great news 👍

[@LotfyKozman]

Very useful and practical video

[@ExcelOffTheGrid]

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

[@eduardomunoz2764]

Wonderful technique, thank you!..

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

[@RajKumar-hw4bt]

Awesome

[@albertogp84]

Unbelievable Trick! Thank you! You are phenomenal

[@ExcelOffTheGrid]

I hope you can put it to good use.

[@vacilando86]

Amazing, 🎉

[@syrophenikan]

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

[@ExcelOffTheGrid]

Thank You 😁

[@rrrraaaacccc80]

Great 💯👍

[@ExcelOffTheGrid]

You’re welcome.

[@mudassirsyedrashidali9787]

Brilliant

[@ladvozer]

thanks!

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

[@user-dy1qb7py8c]

Hey I need help so I have loaded files from folder in power query and after transforming i have 3 columns one is mobile number 2nd is status and 3rd is circle . Now in this 3rd column i have only two locations say A and B. Now all the rows are above 3million and what i want is that based on that 3rd column i want to get sheets containing data for A in one sheet and for B in 2nd sheet in once click .I can do this by duplicating table and then but i want to approach in different manner . Feel free to ask any question about the question and Please reply soon . My humble request

[@ExcelOffTheGrid]

Why don’t you want to duplicate ?

[@user-dy1qb7py8c]

A lot of manual work I have 33 states so i have to duplicate 33 times and then select individual states one by one

[@ExcelOffTheGrid]

@@user-dy1qb7py8c why do you need to separate the query at all? Why not return a single query as a Table, and use a FILTER function to get what you need from that 1 Table?

[@user-dy1qb7py8c]

my task is to get data for individual states in different sheets . If data was less than 1m i might have done that using pivot tables but here data is above 3m

[@ExcelOffTheGrid]

@@user-dy1qb7py8c load the query into the data model in PowerPivot, it can handle unlimited rows.