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

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

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

@ExcelOffTheGrid

Жыл бұрын

Wow, thanks!

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

@ExcelOffTheGrid

5 ай бұрын

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

@ExcelOffTheGrid

Жыл бұрын

Thanks Sumanth, I really appreciate that feedback. 😁

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

@ExcelOffTheGrid

4 ай бұрын

I’m glad you like it. 👍

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

This is awesome! Thanx for sharing your knowledge

@ExcelOffTheGrid

10 күн бұрын

My pleasure! 😁

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. ✅

amazing solution!! thank you!

@ExcelOffTheGrid

3 ай бұрын

You're welcome! 👍

Great techniques there, Mark. Many thanks.

@ExcelOffTheGrid

3 ай бұрын

You’re welcome, glad it was helpful. 😁

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

Great one! Very useful!

@ExcelOffTheGrid

6 ай бұрын

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

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

@ExcelOffTheGrid

2 ай бұрын

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

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

@ExcelOffTheGrid

Жыл бұрын

Thanks Grainne 👍

Genius

Amazing trick👏👏👏

@ExcelOffTheGrid

Жыл бұрын

Thanks Alter

thanks for sharing

@ExcelOffTheGrid

Жыл бұрын

You're welcome Frank.

Awesome 👏

@ExcelOffTheGrid

Жыл бұрын

Thanks 👍

Muito bom!!

@ExcelOffTheGrid

Жыл бұрын

Thanks 😊

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.

👍👍

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?

Help me thanks

@ExcelOffTheGrid

Жыл бұрын

Thanks.

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: kzread.info/dash/bejne/h5WE1NKKf8bOdqw.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])

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.

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.