Rename Column Based on Column Values

[gogrizz]

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

Column1	Column2	Column3
Online	2/10/2022 1:30:46 AM	299-US
Offline	1/10/2019 5:01:28 AM	458-CA
Online	1/26/2022 1:49:46 PM	284-US
Offline	12/15/2021 6:24:21 AM	296-US
Online	2/4/2022 10:28:33 AM	296-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?

[AlexisOlson · Accepted solution]

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

[gogrizz]

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!

[AlexisOlson]

Try using List.MatchesAny like this:

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