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

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

[@iankr]

Ingenious! Many thanks, Mark.

[@ExcelOffTheGrid]

Thanks Ian 👍

[@alejguz1552]

Great Job!

[@LotfyKozman]

Very useful and practical video

[@ExcelOffTheGrid]

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

[@Shriraj27]

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

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

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

[@kennethstephani692]

Excellent video!

[@shubhabratadey]

Very helpful video. Thanks for sharing

[@jasperchangco1072]

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

[@ExcelOffTheGrid]

Yay!! Great news 👍

[@teinoxtube]

Brilliant and clearly explained... thanks a lot!

[@ExcelOffTheGrid]

Thanks, I’m glad it was helpful 😀

[@datawizibility]

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

[@ExcelOffTheGrid]

Thank You. I’m glad you like them.

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

[@albertogp84]

Unbelievable Trick! Thank you! You are phenomenal

[@ExcelOffTheGrid]

I hope you can put it to good use.

[@syrophenikan]

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

[@ExcelOffTheGrid]

Thank You 😁

[@eduardomunoz2764]

Wonderful technique, thank you!..

[@v2pumo817]

Simple à dire vers useful

[@nelson_k_d]

Super Awesome!!

[@ExcelOffTheGrid]

Thank you. 😁

[@ladvozer]

thanks!

[@vacilando86]

Amazing, 🎉

[@mudassirsyedrashidali9787]

Brilliant

[@rrrraaaacccc80]

Great 💯👍

[@ExcelOffTheGrid]

You’re welcome.

[@MUAISC]

Awesome

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

[@emilmubarakshin49]

That’s a cool trick

[@ExcelOffTheGrid]

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

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

[@ExcelOffTheGrid]

If I understand correctly, you want PQ to send the results to multiple sheets. Unfortunately it doesn't do this.

[@dorotakantnerova]

@@ExcelOffTheGrid yes, I want to send results into sheets

[@dorotakantnerova]

@@ExcelOffTheGrid so only macro?

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

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.

[@ExcelOffTheGrid]

Your data is not in a normalized format, so you're not using Power Query the right way. Years should be column and the values for each Year should be a column. Use the Unpivot action on each nested table to normalize the data become you combine them.

[@MuhammadRizwan-tc3zp]

@@ExcelOffTheGrid Hi, thanks for response. My issue resolved. I was making table from binary using = Table.AddColumn(#"Filtered Rows5", "Transform File (12)", each #"Transform File (12)"([Content])) which is somehow neglecting few columns. After I tried using Transform File (6), all the columns are now started appearing. however, I don not know the difference between 6 and 12 and what does Transform File do. And you solution in the video really helped me a lot to achieve my task. Great work.