Expand columns dynamically in Power Query (THE RIGHT WAY!)

Expand columns dynamically in Power Query (THE RIGHT WAY!) | Excel Off The Grid

Рет қаралды 20,692

Күн бұрын

★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Check out the blog post★
exceloffthegrid.com/expand-co...
★ About this video ★
Expanding table columns is a common Power Query action. It even has its own icon. But, there is a problem lurking here. Clicking this expand data button hardcodes the column names into the M code. If new columns are added to the source at a later date they don't show up.
So, in this video, we are looking at how to expand columns dynamically in Power Query, so that we can get all the data we want.
0:00 Introduction
0:37 The Problem
4:32 The solution: Expanding columns dynamically
12:04 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

Пікірлер: 54

@stinhuffine4422 5 ай бұрын

You are the genius. First it requires out of the box, vivid thinking to feel it as a problem. Then it requires determination to make a video about it. And then it takes a pure one in Billion genius to make your solution as future proof as imaginable!

@user-gg8ys1fc4d Жыл бұрын

It makes your day when the first video appears from your search has the solution you're looking for. Great solution! Thank you

@ExcelOffTheGrid Жыл бұрын

And it's the weekend tomorrow!!! Surely that's too much good fortune for one day 😂

@user-gg8ys1fc4d Жыл бұрын

@@ExcelOffTheGrid Thank you & it's for sure but the weekend has more questions. Could you please advise how do I sort column headers dynamically that every time a new column is added, it gets sorted based on calendar month. For example, it should be sorted like Apr23, May23, Oct23, May24, May25 etc...

@ExcelOffTheGrid Жыл бұрын

@@user-gg8ys1fc4d In the final data, column headers should never be dates. As that is not a structured data layout. Dates are attributes about each record. Therefore you should have a month column with all the dates listed in that column. You should check out the unpivot feature to achieve that.

@walteradamsbe 4 ай бұрын

This method is great! Thanks for the tutorial. However, I recently discovered a necessary extra step to take to make sure it's done correctly. When anyone applies filters on the sheets you're importing, Excel (behind your back) creates additional hidden sheets in the workbook with names starting with "_xlnm._Filterdatabase". These will be listed in your query and if not filtered out, their data will be incorporated in the final result (resulting in double data). I've added an extra step after listing the tables stating "Name column should not begin with _xlnm" to filter these out before actually going into the steps which get the data from each indivisual sheet. Hope it helps for anyone

@LaloinLondon Жыл бұрын

I have seen quite a few videos about expanding columns dynamically, but yours is the best so far. Thanks for sharing.

@ExcelOffTheGrid Жыл бұрын

Wow, thanks!

@Sumanth1601 Жыл бұрын

Brilliant 😮 I really loved the way you showed in detail what can go wrong first. These are real scenarios we came across. Excellent

@ExcelOffTheGrid Жыл бұрын

Thanks Sumanth, I really appreciate that feedback. 😁

@wmcnabb 5 ай бұрын

Wow, this will be my new standard pattern for importing data from Excel. Thank you very much sir.

@ExcelOffTheGrid 5 ай бұрын

Great stuff. 😁

@user-tg1vt5tw8c 5 ай бұрын

ماشاء الله تبارك الله شرح رائع وجميل ومشكور جدا وربنا يهديك ان شاء الله

@ziggle314 Жыл бұрын

Absolutely superb. Thanks Mark! I love videos where I pick up techniques that I can apply immediately.

@ExcelOffTheGrid Жыл бұрын

Great news. I'm glad you can put it to use straight away. ✅

@IvanCortinas_ES Жыл бұрын

Great tutorial Mark. Learning from a great teacher. Thank you!!

@ahdianpuguh 4 ай бұрын

Man.. I learn this for hours.. Thank a lot..

@ExcelOffTheGrid 4 ай бұрын

I’m glad you like it. 👍

@iankr 2 ай бұрын

Great techniques there, Mark. Many thanks.

@ExcelOffTheGrid 2 ай бұрын

You’re welcome, glad it was helpful. 😁

@mirrrvelll5164 6 ай бұрын

Great one! Very useful!

@ExcelOffTheGrid 6 ай бұрын

Glad you think so! I find it comes in handy quite often.

@jeromeastier462 9 күн бұрын

This is awesome! Thanx for sharing your knowledge

@ExcelOffTheGrid 4 күн бұрын

My pleasure! 😁

@kevingodsave8893 Ай бұрын

I hope the Excel team will create GUI solution to handle dynamic changes

@ExcelOffTheGrid Ай бұрын

It would be nice, but I think it’s unlikely.

@huseyinburaktasci1638 2 ай бұрын

amazing solution!! thank you!

@ExcelOffTheGrid 2 ай бұрын

You're welcome! 👍

@GrainneDuggan_Excel Жыл бұрын

slick solution Mark and not too tough to implement for the MCode beginner

@ExcelOffTheGrid Жыл бұрын

Thanks Grainne 👍

@alterchannel2501 Жыл бұрын

Amazing trick👏👏👏

@ExcelOffTheGrid Жыл бұрын

Thanks Alter

@tlee7028 Жыл бұрын

Awesome 👏

@ExcelOffTheGrid Жыл бұрын

Thanks 👍

@FRANKWHITE1996 Жыл бұрын

thanks for sharing

@ExcelOffTheGrid Жыл бұрын

You're welcome Frank.

@ericrobbins6953 Жыл бұрын

Excellent, thank you. Does this capture column field name changes as well?

@ExcelOffTheGrid Жыл бұрын

Yes, it would. However if you've got other steps based on those column names (e.g. Change Type), then it will cause an query error.

@Ypurenete Жыл бұрын

Muito bom!!

@ExcelOffTheGrid Жыл бұрын

Thanks 😊

@bernardo01233211 Жыл бұрын

Niceeee! Thank you very much! I have question, is there any workaround for "Combine Files" from a folder/sharepoint?

@ExcelOffTheGrid Жыл бұрын

What kind of work around? You can combine files from a folder in SharePoint, what’s the issue?

@RichardJones73 2 ай бұрын

Genius

@chrisa8203 11 ай бұрын

9:40 what would you do if the row headers is in row 3? Think I need a combination of Promote Headers + Skip First 2 rows…I’m stuck haha

@ExcelOffTheGrid 11 ай бұрын

I would transform the nested Table before expanding. Watch this video: kzfaq.info/get/bejne/i8eApdOCtMnMdn0.html

@chrisa8203 11 ай бұрын

@@ExcelOffTheGrid thanks for the quick reply! I was able to solve with formula below…basically wrap PromoteHeaders w/TableSkip Table.PromoteHeaders(Table.Skip(_,2), [PromoteAllScalars=true])

@kebincui 7 ай бұрын

👍👍

@user-gg8ys1fc4d 11 ай бұрын

Hello, I have few columns and I want to sort columns based on the month & Year. For example, 0723, 0823, 1023, 0124 etc.. My data is connected to a folder so everytime a new PDF is added with MMYY, the data will add a new column for that month. For example, I have columns below. Name, Category, Serial Number, then months 0723, 0823, 1023, 0124, Total So I want to keep all the columns as it and the month columns should be sorted as the earliest month should appear first. Could you please help me with this solution? I have been searching from few days with no solution found so far.

@ExcelOffTheGrid 11 ай бұрын

Needing to have columns in a specific order is an indication that you’re using Power Query as a presentational tool instead of a data preparation tool. While it is possible, I don’t think it’s the right solution as you’ll still end up with issues later in the process.

@prashantsuthar7 Жыл бұрын

Help me thanks

@ExcelOffTheGrid Жыл бұрын

Thanks.

@victor_wang_1 Жыл бұрын

You did all those additional steps to keep the column flowing in dynamically...BUT then...Table.TransformColumnTypes hard codes them all again! So any new column won't have a data type attached to it. I've come to accept that Power Query is simply not good at these sorts of tasks. The amount of hoops you have to jump through to avoid hard coding column names gets ridiculous rather quickly. Honestly, if the number of columns changes so much, you may be better off using an EAV model via unpivoting.

@ExcelOffTheGrid Жыл бұрын

Visibility of new data is 99% of the problem. Once you know it’s there you can decide what to do with it. We’ve got a separate solution for dealing with data types dynamically. But that is outside the scope of this video. The less uniformity in data sets the more complex the solution to clean it. That’s just life.