Forum Discussion

gogrizz's avatar
gogrizz
Icon for Advocate I rankAdvocate 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
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
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
    Icon for Advocate I rankAdvocate 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
      Icon for Super User rankSuper User
      4 years ago

      Try using List.MatchesAny like this:

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