Expand columns dynamically in Power Query (THE RIGHT WAY!) | Excel Off The Grid
ฝัง
- เผยแพร่เมื่อ 15 มิ.ย. 2024
- ★ 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
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
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!
ماشاء الله تبارك الله شرح رائع وجميل ومشكور جدا وربنا يهديك ان شاء الله
It makes your day when the first video appears from your search has the solution you're looking for. Great solution! Thank you
And it's the weekend tomorrow!!! Surely that's too much good fortune for one day 😂
@@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...
@@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.
I have seen quite a few videos about expanding columns dynamically, but yours is the best so far. Thanks for sharing.
Wow, thanks!
Brilliant 😮 I really loved the way you showed in detail what can go wrong first. These are real scenarios we came across. Excellent
Thanks Sumanth, I really appreciate that feedback. 😁
Wow, this will be my new standard pattern for importing data from Excel. Thank you very much sir.
Great stuff. 😁
Great tutorial Mark. Learning from a great teacher. Thank you!!
Absolutely superb. Thanks Mark! I love videos where I pick up techniques that I can apply immediately.
Great news. I'm glad you can put it to use straight away. ✅
I hope the Excel team will create GUI solution to handle dynamic changes
It would be nice, but I think it’s unlikely.
Man.. I learn this for hours.. Thank a lot..
I’m glad you like it. 👍
Great techniques there, Mark. Many thanks.
You’re welcome, glad it was helpful. 😁
slick solution Mark and not too tough to implement for the MCode beginner
Thanks Grainne 👍
amazing solution!! thank you!
You're welcome! 👍
Great one! Very useful!
Glad you think so! I find it comes in handy quite often.
Amazing trick👏👏👏
Thanks Alter
Excellent, thank you. Does this capture column field name changes as well?
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.
thanks for sharing
You're welcome Frank.
Awesome 👏
Thanks 👍
Muito bom!!
Thanks 😊
Niceeee! Thank you very much! I have question, is there any workaround for "Combine Files" from a folder/sharepoint?
What kind of work around?
You can combine files from a folder in SharePoint, what’s the issue?
Genius
👍👍
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
I would transform the nested Table before expanding. Watch this video:
th-cam.com/video/UaPrpQOchFI/w-d-xo.html
@@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])
Help me thanks
Thanks.
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.
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.
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.
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.