Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
gogrizz
Advocate I
Advocate I

Rename Column Based on Column Values

I'm trying to rename the column headers based on the values inside the column.

To start with, I've imported CSV data from a text file (sample below), but the file doesn't have headers, so Power Query assigns generic headers: Column1, Column2, Column3

 

Column1Column2Column3
Online2/10/2022 1:30:46 AM299-US
Offline1/10/2019 5:01:28 AM458-CA
Online1/26/2022 1:49:46 PM284-US
Offline12/15/2021 6:24:21 AM296-US
Online2/4/2022 10:28:33 AM296-US

 

Multiple people will be updating the source file, so the data might not always be in the same order, but it'll alwasy have the same information.  For example, the Online/Offline column might be at the end of the table, so when it gets refreshed, it'll get assigned the header "Column3".  If I rename the columns manually, then maybe next time the Online/Offline status will be in the "Account" column.

 

I want to rename the headers strictly based on the column values so that, for example,

  • Whatever column has the word "Offline" as one of the values, THAT specific column, regardless of position, is renamed to "Status".
  • Whatever column has the word "296-US" as one of the values, THAT specific column, regardless of position, is renamed to "Account".

This is a just sample, there are 9 columns in total so I could write a bunch of IF statements to say:

  • if "Column1" contains "Offline" rename to "Status",
    • buuut if "Column1" contains "296-US", rename to "Account"
      • buuut if "Column1" contains X, rename to Y, etc. etc.
  • if "Column2" contains "Offline"...etc. etc.

But there has to be somethign more efficient.  I'm trying to find a more simple approach.

 

Is this possible?

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

You can do a transformation on the list of column names and then rename the columns based on that new list.

 

Here's some M code you can paste into the Advanced Editor on a new query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc5BC4QgEIbhvyKei2bGUXRu0XlpITpFx4Jg8f8fV9Nagj0J8vDOtyx6jJ8jbrrR1CF0BEQKxYCwU/0rf4fQzpNem0T3vVosFoOyAijki2Xr26EvNt6U3JXlkLPvM+v5TzZtsBmjckIs6a0b3I1/c7lmId0XY550/QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    OriginalColumnNames = Table.ColumnNames(Source),
    NewColNames =
        List.Transform(
            OriginalColumnNames,
            each if List.Contains(Table.Column(Source, _), "Offline") then "Status"
            else if List.Contains(Table.Column(Source, _), "296-US") then "Account"
            else _
        ),
    UpdateColNames = Table.RenameColumns(Source, List.Zip({OriginalColumnNames, NewColNames}))
in
    UpdateColNames

View solution in original post

3 REPLIES 3
AlexisOlson
Super User
Super User

You can do a transformation on the list of column names and then rename the columns based on that new list.

 

Here's some M code you can paste into the Advanced Editor on a new query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc5BC4QgEIbhvyKei2bGUXRu0XlpITpFx4Jg8f8fV9Nagj0J8vDOtyx6jJ8jbrrR1CF0BEQKxYCwU/0rf4fQzpNem0T3vVosFoOyAijki2Xr26EvNt6U3JXlkLPvM+v5TzZtsBmjckIs6a0b3I1/c7lmId0XY550/QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    OriginalColumnNames = Table.ColumnNames(Source),
    NewColNames =
        List.Transform(
            OriginalColumnNames,
            each if List.Contains(Table.Column(Source, _), "Offline") then "Status"
            else if List.Contains(Table.Column(Source, _), "296-US") then "Account"
            else _
        ),
    UpdateColNames = Table.RenameColumns(Source, List.Zip({OriginalColumnNames, NewColNames}))
in
    UpdateColNames

Thanks Alexis - that's what I was looking for!

To take things a step further - is it possible to do the same thing but not rely on a full cell value?

 

Example:

IF a column has a cell values containing "296" (not the full "296-US"), 

THEN rename that column to "Account"

 

I've tried a bunch of different ways and spent the last 6 hours searching forums but can't figure it out.

Thanks!

 

Try using List.MatchesAny like this:

if List.MatchesAny(Table.Column(Source, _), each Text.Contains(_, "296-US")) then "Account"

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.