How to Rename Column Headings with Power Query - the quick automated way

⏬ Click Show More to check out the description for info and links⏬
Excel and Power BI users sometime need to rename multiple columns automatically rather than manually. e.g. auto replacing parts of the names, or using a manual table of "Before and After" to rename your columns.
I explain 2 methods List.Zip and the simpler Table.ToRows
00:00 Intro
00:20 The Scenario & the general approach
02:11 Create the From and To table
04:34 Creating the List of Lists using List.Zip
07:54 Table.ToRows method
'**************
Nice single line of code solution from Geert in the Comments to extract everything after the underscore
= Table.TransformColumnNames(Source, each Text.AfterDelimiter(_,"_"))
'****************
File to Download
aasolutions.sharepoint.com/:f...
Matt Allington's video
• Rename all columns in ...
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/
accessanalytic.com.au/

[@HungLe-pk3oz]

I've watched many related videos but the way you explain is way more clear and simple. Thank you so so much

[@AccessAnalytic]

No worries, thanks for taking the time to leave a kind comment

[@zahoorsarbandi2982]

very nice lecture specially: Shift+Down Arrow selects the step name, then Shift+9... wonderful

[@AccessAnalytic]

Thank you Zahoor

[@RahulForPWBI]

Easy Peasy; Thank You so much!

[@AccessAnalytic]

You’re welcome.

[@alexbarbucristi]

Hi Wyn, My approach was: Use Headers as first Row, Transpose, Apply transformations needed to column 1 which contains the names of the original columns. Transpose, Use first row was headers

[@AccessAnalytic]

Nice technique with a small data set. Not sure what would happen with 500,000+ rows

[@alexbarbucristi]

@@AccessAnalytic Thanks for the heads up, will stop using it

[@radu_sirbu]

That's a lifesaver! It's exactly what I need for my current task at work.

[@AccessAnalytic]

Excellent

[@GeertDelmulle]

Hi Wyn, Here's a one step solution that gets straight to the point. In your rename step use this formula: = Table.TransformColumnNames(Source, each Text.AfterDelimiter(_,"_")) and be done with it. 🙂 You're welcome.

[@AccessAnalytic]

Nice Geert

[@AccessAnalytic]

I added it to the description section

[@txreal2]

Where is the time stamp for this step? Thanks Geert

[@AccessAnalytic]

@@txreal21:21

[@marcelakarinalopezortiz3373]

@@AccessAnalytic do you have a similar solution when you want to add a prefix/suffix?

[@paulsingleton6071]

Hi Wyn, great video, really useful. Thank you. 👍👏

[@AccessAnalytic]

You’re welcome

[@mcwahaab]

Awsome! I have seen other long approaches but this is the one. Thanks a lot Wyn

[@AccessAnalytic]

You’re welcome

[@user-ez5os8nm2z]

Excellent

[@erdiaz39]

Awesome tips!!!

[@AccessAnalytic]

Cheers 😀

[@josecarlosconejo5724]

Very Useful 😮 Impressive 👏

[@AccessAnalytic]

Cheers

[@rudi-gruber]

very helpful, thanks

[@AccessAnalytic]

You're welcome 😀

[@telclissonperes]

Thank you so much!!!!

[@AccessAnalytic]

No worries

[@felipesignorellireis7839]

Love it

[@AccessAnalytic]

Thanks

[@dbalkin777]

Great video! Related to your “clicks” comment, how much M do you write in your work now? I feel like I’ve reached a place with PQ that that is what I should focus on now, but seldom feel like I have a use case that can’t be solved with the “clicks.”

[@AccessAnalytic]

Thanks, probably 10% on average is hand written code

[@kalyanreddy6260]

Still getting error. I jave two tables 2022sales, 2023 sales. In first file i have columns carname, year, number of units sold, country. In secnd file i have diffent name number of units as (units) and county as (place) . Ihave created maping tabme exactly and turn it to rows and using in my sample file query. Now the second still shows error not displaying records.a red line across all headers. All date types are checked though

[@AccessAnalytic]

If you’re running this on a folder of files then you might want to add MissingField.Ignore Something like Table.RenameColumns(_,{{"Product","Item"}}, MissingField.Ignore) This should handle the fact that the columns you’re renaming don’t exist on one file

[@danishnawaz3651]

Sir, plz make video for below. If 1 table has 9 column, 2 table has 10 and 3 table has 8 . Then how can we make equal all column for all table.. like we want to insert dummy column for missing column in tables and make equal like 10 column for each table.

[@AccessAnalytic]

That should happen automatically. Tables don’t need the same number of columns

[@PeterWalker]

How would you handle the error that the field name already exists in the record? I have that error and although I can tell the rename function how to handle missing values there isn't anything tat will say this is what to do when you have an existing name.

[@AccessAnalytic]

Not sure, maybe some sort of pre check on the renaming table and add a 2 if text already exists if List.Contains( Table.ColumnNames(OriginalTable), [NewName] ) then [NewName] & "2" else [NewName]

[@PeterWalker]

@@AccessAnalytic sounds like a course of action. Thanks for the response!

[@exceltina]

When I refresh query once i get a new file, will it read the data?

[@AccessAnalytic]

Yes it will

[@bembeyerenkhoma]

I have a question based on the power query how can I get in touch with you? Do you have an email address or any other means of communication apart from this platform?

[@AccessAnalytic]

Check out the description for my links, also for most questions I point folks to techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat?WT.mc_id=M365-MVP-5002589 And community.powerbi.com/t5/Power-Query/bd-p/power-bi-services

[@FredericLEGUEN-Excel]

Nice trick. And I love Shit + 9 😉

[@AccessAnalytic]

Cheers Frédéric

[@GrainneDuggan_Excel]

Fantastic timesaver and frustration remover! Wish I knew Shift+9 sooner! Thanks Wyn!!!!!! 👏👏👏👏👏👏

[@mcnater]

@@GrainneDuggan_Excel what does Shift + 9 do?

[@GrainneDuggan_Excel]

@@mcnater watch at about 2:35 min when you type a function name then Shift+Down Arrow selects the step name, then Shift+9 puts the (before and )after!!

[@AccessAnalytic]

Thanks Grainne, i wish I knew it sooner too 😆