Promote headers in nested tables before expanding columns | Power Query | Excel Off The Grid
Вставка
- Опубліковано 3 лип 2024
- ★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Get the example file ★
exceloffthegrid.com/promote-h...
★ Check out the dynamic column expand post ★
exceloffthegrid.com/expand-co...
★ About this video ★
In Power Query, promoting headers of nested tables before expanding columns leads to fewer and easier transformations. However, these options are not available in the user interface. Therefore, we need to make a few changes to the M code ourselves.
This video shows two ways to promote headers before expanding the columns.
0:00 Introduction
0:25 Example & problem
2:30 Promote nested Table headers - Excel files
3:34 Promote nested Table headers - Other file types
6:50 Dynamic column expand method
7:19 Wrap-up
★ 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
Always amazing stuff!!
Thanks Chris 😁
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: ua-cam.com/video/UaPrpQOchFI/v-deo.html
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. 👍
Great tips! Thanks Mark!
Thank you. 👍
отлично!
Used it today! Great timing :) Many thanks.
Fantastic! Glad I could help. 😁
Great video as always 👍. Thanks Mark.
Thanks Kebin 😁
Thanks Mark, fantastic explanation as usual 👍
Glad you enjoyed it.
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?
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.
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.