Managing changing column names in Power Query #10: (M)agic (M)ondays

In today's video, I will show how to manage column headers that changes names so your power bi file does not break when you refresh it.

This tip is from Lars Schreiber and here is the link to his blog post and how to do this in power query:

SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

ABOUT CURBAL:

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo

#SUBSCRIBE #CURBAL

[arielarce]

Thank you for your help Ruth. It worked perfectly for me. I set up all the renaming steps in a single line. I'll show the previous and the next step for context:

#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"RenameCols" = Headers", {{"F", "FUND"}, {"OF ", "OFFICE"}, {Table.ColumnNames(#"Promoted Headers"){2}, "THIS MONTH"}, {Table.ColumnNames(#"Promoted Headers"){3}, "ACCUMULATED"}}),
#"Changed Type" = Table.TransformColumnTypes(#"RenameCols", {{"OFFICE", type text}, {"THIS MONTH", Int64.Type}, {"ACCUMULATED", Int64.Type}}),


Best regards.

[fathimamoideen]

Hi Ruth! Thanks for this....It really helped solve the issue that I was facing. However my requirement was for multiple columns and here's what i did:

DynamicNameHeader = Table.ColumnNames (#"Promoted Headers1") {4},
DynamicNameHeader1 = Table.ColumnNames (#"Promoted Headers1") {5},

DynamicNameHeader2 = Table.ColumnNames (#"Promoted Headers1") {6}

Just repeated the steps for the same no of columns that need to be dynamic.
This may not be the ideal solution however it does work perfectly when we need to urgently fix it :)

[darshantalreja]

Dear Ruth! It's a fantastic and fundamental solution that the regular Power Query books dont address and only harp about the Power of the tool but never really demonstrate these important aspects when dealing with dynamically changing data!

Brilliant! Kudos to you for addressing this in such a easy way!

[jackeroo]

This works! If you don't have promoted header since your excel already contains a table then you can use this: In the advance editor, type line 1 &2
1. DynamicHeaderChange = Table.ColumnNames(Source),
2. DynamicTableFillup = Table.Fillup(#"Change Type", DynamicHeaderChange),
then in the formula bar {DynamicHeaderChange{0} - REMEMBER in the row the first row listed as "1" but it should be 0 when changing the formula or if you preferred everything in the advance editor then add line 3.
3. #"Change Type" = Table.TransformColumType(Source, {{DynamicHeaderChange{0}, type date}}),

[learnspreadsheets]

Love this trick. Changed column names account for about 80% of the reasons that my queries break!

[bostjanbrumec]

Perfect! I needed to grab the name of the last column dynamically, since I grab these data out of Excel Workbook names. I adjusted the code like this:
#"Grab The Last Column" = Table.ColumnNames(#"Promoted Headers") - 1}

[anw]

Ruth, it's 2021, but you can't imagine how happy I am that I found your video and how relevant it is. Was doing a Power BI demo and power query had hard coded headers which were dynamic/changing. Anyhow, failed to account this and during refresh my demo FAILED. Throughout the time I was trying to fix it but it was too late. Anyhow, lesson learned. Thanks a lot for this video.

[samiraslan]

2017 and Ruth still EPIC as always, thanks my lady!

[sivaperumal]

This 6 year old video just helped me today, Thank you

[WanLazyBear]

I'm watching your VDO from Thailand. Thank you so much

[thedataminersunion]

Cool Approach!
Im lazy and typically just catch this before the headers are promoted, Name the numbered default column's to what i want and remove the first row from the table...
with that being said I like the idea of capturing and holding elements of the table in dynamic variables :).
Thanks again Ruth

[bcippitelli]

One more time you solved my problem Ruth!. Your videos are gold! Thank you again! Gracias totales!

[kennyquango]

Wow this is great and so useful Ruth ! Changing column names has caused me a lot of problems!!!

[rakeshrao]

Beautiful explanation .. Got helped me with my assignment. Got the actual thing was looking for and found no where else on the web. Thanks a lot.

[joanneosborne]

Ruth, Thank you so much for sharing all your amazing tips and tricks. All of your videos are priceless and really do help. Take care and stay safe.

[excelisfun]

Thanks for the Table.ColumnsNames Dynamic Fun : )

[capitalmantra]

Hi Ruth, Tried this and it worked magic for me!!! love

[ShaDoWZD]

This video solved my problem, I understood how Table.ColumnNames work, as a list of the headers.
Just to add what was implicity in your video, but wasn't show:
You can make a variable store that list and after that u can use that variable to pick the header.
In the video example the variable was "DynamicHeaderChange".
Instead of keep using the formula Table.ColumnNames, just use the variable "DynamicHeaderChange{0}" so it will pass the first head name. If u want to target all the headers (using a fuction like fill up, just remove the {} and u are good to go, example: Table.FillUp(#"laststep", DynamicHeaderChange)

[martintaylor]

Thanks for this Ruth - just what I needed. Great work on giving due credit to Lars also

[mohamedaboobackersiddique]

thank you so much, exactly i face this issue and found this video on right time.thank you once again.