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

Подписаться 27 тыс.

Просмотров 11 тыс.

50% 1

★ 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

Опубликовано:

29 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 44

@datawizibility 13 дней назад

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

@ExcelOffTheGrid 12 дней назад

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 😀

@ExcelUnlocked 10 месяцев назад

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 10 месяцев назад

You are very welcome

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

@LotfyKozman 11 месяцев назад

Very useful and practical video

@ExcelOffTheGrid 11 месяцев назад

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

@iankr 10 месяцев назад

Ingenious! Many thanks, Mark.

@ExcelOffTheGrid 10 месяцев назад

Thanks Ian 👍

@jasperchangco1072 11 месяцев назад

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

@ExcelOffTheGrid 11 месяцев назад

Yay!! Great news 👍

@teinoxtube 11 месяцев назад

Brilliant and clearly explained... thanks a lot!

@ExcelOffTheGrid 11 месяцев назад

Thanks, I’m glad it was helpful 😀

@eduardomunoz2764 5 месяцев назад

Wonderful technique, thank you!..

@albertogp84 9 месяцев назад

Unbelievable Trick! Thank you! You are phenomenal

@ExcelOffTheGrid 9 месяцев назад

I hope you can put it to good use.

@syrophenikan 3 месяца назад

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

@ExcelOffTheGrid 3 месяца назад

Thank You 😁

@RajKumar-hw4bt 3 месяца назад

Awesome

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

@vacilando86 Месяц назад

Amazing, 🎉

@ladvozer 9 месяцев назад

thanks!

@rrrraaaacccc80 8 месяцев назад

Great 💯👍

@ExcelOffTheGrid 8 месяцев назад

You’re welcome.

@mudassirsyedrashidali9787 6 месяцев назад

Brilliant

@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 4 месяца назад

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 7 месяцев назад

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 7 месяцев назад

Why don’t you want to duplicate ?

@user-dy1qb7py8c 7 месяцев назад

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 7 месяцев назад

@@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 7 месяцев назад

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 7 месяцев назад

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