Transform nested tables in Power Query (without writing M coding)

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

Рет қаралды 11,607

Күн бұрын

★ 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

Пікірлер: 38

@datawizibility 21 күн бұрын

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

@ExcelOffTheGrid 21 күн бұрын

Thank You. I’m glad you like them.

@chilaw2004 11 ай бұрын

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 11 ай бұрын

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 11 ай бұрын

Wonderful technique. Thanks Mark.

@ExcelOffTheGrid 11 ай бұрын

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

@kebincui 11 ай бұрын

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 11 ай бұрын

Thanks Kebin. I though you might like it 😀

@IvanCortinas_ES 11 ай бұрын

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

@ExcelOffTheGrid 11 ай бұрын

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

@iankr 11 ай бұрын

Ingenious! Many thanks, Mark.

@ExcelOffTheGrid 11 ай бұрын

Thanks Ian 👍

@eduardomunoz2764 5 ай бұрын

Wonderful technique, thank you!..

@teinoxtube 11 ай бұрын

Brilliant and clearly explained... thanks a lot!

@ExcelOffTheGrid 11 ай бұрын

Thanks, I’m glad it was helpful 😀

@ladvozer 10 ай бұрын

thanks!

@jasperchangco1072 11 ай бұрын

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

@ExcelOffTheGrid 11 ай бұрын

Yay!! Great news 👍

@ExcelUnlocked 11 ай бұрын

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 11 ай бұрын

You are very welcome

@albertogp84 9 ай бұрын

Unbelievable Trick! Thank you! You are phenomenal

@ExcelOffTheGrid 9 ай бұрын

I hope you can put it to good use.

@mudassirsyedrashidali9787 6 ай бұрын

Brilliant

@vacilando86 Ай бұрын

Amazing, 🎉

@LotfyKozman 11 ай бұрын

Very useful and practical video

@ExcelOffTheGrid 11 ай бұрын

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

@RajKumar-hw4bt 3 ай бұрын

Awesome

@syrophenikan 4 ай бұрын

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

@ExcelOffTheGrid 4 ай бұрын

Thank You 😁

@GrainneDuggan_Excel 11 ай бұрын

This is definitely a keep for future reference video.

@ExcelOffTheGrid 11 ай бұрын

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

@rrrraaaacccc80 8 ай бұрын

Great 💯👍

@ExcelOffTheGrid 8 ай бұрын

You’re welcome.

@emilmubarakshin49 10 ай бұрын

That’s a cool trick

@ExcelOffTheGrid 10 ай бұрын

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

@ExcelUpNorth 11 ай бұрын

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 11 ай бұрын

Yes, that should work.

@arpwable 5 ай бұрын

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!