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!
@@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...
@@VineshParekh 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.
What was missing in this tutorial was looking at the overall steps. I lost track of what query you were working on and what step was being created or edited. Maybe it was off screen and wasn't captured in the video?
I have 2 columns. Second column has tables. But I want to expand them into columns. By default, it'll always add extra rows and spill the data. What if I want my data to spill into multiple columns? Is there an option to do that?
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.
@@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])
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.
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...
@@VineshParekh
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.
Wow, this will be my new standard pattern for importing data from Excel. Thank you very much sir.
Great stuff. 😁
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. 😁
I have seen quite a few videos about expanding columns dynamically, but yours is the best so far. Thanks for sharing.
Wow, thanks!
Man.. I learn this for hours.. Thank a lot..
I’m glad you like it. 👍
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. ✅
This is awesome! Thanx for sharing your knowledge
My pleasure! 😁
Great techniques there, Mark. Many thanks.
You’re welcome, glad it was helpful. 😁
Great tutorial Mark. Learning from a great teacher. Thank you!!
slick solution Mark and not too tough to implement for the MCode beginner
Thanks Grainne 👍
Great one! Very useful!
Glad you think so! I find it comes in handy quite often.
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.
What was missing in this tutorial was looking at the overall steps. I lost track of what query you were working on and what step was being created or edited. Maybe it was off screen and wasn't captured in the video?
amazing solution!! thank you!
You're welcome! 👍
I hope the Excel team will create GUI solution to handle dynamic changes
It would be nice, but I think it’s unlikely.
Amazing trick👏👏👏
Thanks Alter
thanks for sharing
You're welcome Frank.
I have 2 columns. Second column has tables. But I want to expand them into columns. By default, it'll always add extra rows and spill the data. What if I want my data to spill into multiple columns? Is there an option to do that?
Genius
Awesome 👏
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?
Muito bom!!
Thanks 😊
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.
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:
ua-cam.com/video/UaPrpQOchFI/v-deo.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])
👍👍
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.
Help me thanks
Thanks.