Forum Discussion

gogrizz's avatar
gogrizz
Advocate I
4 years ago
Solved

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 Quer...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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's avatar
gogrizz
Advocate I
4 years ago

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's avatar
AlexisOlson
Super User
4 years ago

Try using List.MatchesAny like this:

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