Nice one Mark- this caught me out a few weeks ago - I found out the hard way that the excel argument for promoting headers is not available for CSVs. Great solution 👌
Having spent the last 6 years using Power Query, it took me a long time to realize that it's always better to clean a nested table first. It's just a shame that cleaning nested tables is difficult. You should check out this video - it might give you some further ideas: th-cam.com/video/UaPrpQOchFI/w-d-xo.html
Hi Mark Really useful. Question: we have datamart extracts in flat xlsx format that come with information above the header row, same format but in multiple files (so need to exclude, say, first 4 rows *before* promoting). Every so often the report column order changes, so correctly labelling the columns before expanding data would be really handy ... 🙂 Any thoughts on adapting the process you describe? Thanks, Chris (in Bristol)
In a column I have 3 data types, Payment, failed payment and refunds I want to separate them into 2 columns First one is payment less failed payment And second one refunds?
Good spot. It's because I expanded explicitly and should have clicked Load More in the column list before clicking OK. I would normally expand dynamically (which would ensure this never happens), but I wasn't covering that in this video.
Another great video!
Thank you Mark for this nice trick to promote headers especially in the PDF file which is new and didn't see before ...
Great stuff, I'm glad you were able to learn something new. 👍
Nice one Mark- this caught me out a few weeks ago - I found out the hard way that the excel argument for promoting headers is not available for CSVs. Great solution 👌
Having spent the last 6 years using Power Query, it took me a long time to realize that it's always better to clean a nested table first.
It's just a shame that cleaning nested tables is difficult.
You should check out this video - it might give you some further ideas: th-cam.com/video/UaPrpQOchFI/w-d-xo.html
Always amazing stuff!!
Thanks Chris 😁
Great tips! Thanks Mark!
Thank you. 👍
Used it today! Great timing :) Many thanks.
Fantastic! Glad I could help. 😁
Thanks Mark, fantastic explanation as usual 👍
Glad you enjoyed it.
Is it possible to use power query to use a row that isnt the first row as the header - ie use row 7 as headers
Yes, it’s possible. But might take a bit of data wrangling to get row 7 to the top. Then you can promote it.
Great video as always 👍. Thanks Mark.
Thanks Kebin 😁
Wow... Nice 👍👍
Thank you! Cheers! 👍
Hi Mark
Really useful.
Question: we have datamart extracts in flat xlsx format that come with information above the header row, same format but in multiple files (so need to exclude, say, first 4 rows *before* promoting). Every so often the report column order changes, so correctly labelling the columns before expanding data would be really handy ... 🙂
Any thoughts on adapting the process you describe?
Thanks, Chris (in Bristol)
If you look at the blog post it gives an additional example of removing rows at the top for a nested table.
@@ExcelOffTheGrid Sorry, read that - I must be reading past it somehow ... where?
отлично!
In a column I have 3 data types,
Payment, failed payment and refunds
I want to separate them into 2 columns
First one is payment less failed payment
And second one refunds?
Pivot the columns without aggregation, then perform the calculation.
you lost year 2024 :)
Good spot.
It's because I expanded explicitly and should have clicked Load More in the column list before clicking OK.
I would normally expand dynamically (which would ensure this never happens), but I wasn't covering that in this video.