Forum Discussion

Hrb-9's avatar
Hrb-9
New Member
2 years ago
Solved

Rename columns from list

New to power query, so hoping for some help whilst I learn. Thanks in advance

 

I have a master table with column names which are codes e.g. XCGY, CHTY, HGFT, etc

 

i have a secondary table where column 1 is the list of codes and column 2 contains the useful column name I would like to use. 

Can I use power query to quickly rename all columns in table 1 based on the info from table 2?

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    Assume your translation table is named Translate with the columns Code and Friendly

     

    Table.RenameColumns(#"Step to Rename",List.Zip({Translate[Code], Translate[Friendly]}), MissingField.Ignore)

     

    Translate Table

     

    Table with code names

    Sample Code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RYzBDoAgDEP/ZWf+CDigloxEIRnj4N87lxgv3fraNEZSbpPCdxiClOwp/aAcIjn9pbZezvP2SBlGJvZh3UB9XRvEp+B5bTLVvCPBW65jiTLl/AA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [abc = _t, ghi = _t, def = _t, jkl = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"abc", type text}, {"ghi", type text}, {"def", type text}, {"jkl", type text}}),
    
    #"Rename Columns" = Table.RenameColumns(#"Changed Type",List.Zip({Translate[Code], Translate[Friendly]}), MissingField.Ignore)
in
    #"Rename Columns"

     

    with Columns Renamed

     

     

     

1 Reply

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    Assume your translation table is named Translate with the columns Code and Friendly

     

    Table.RenameColumns(#"Step to Rename",List.Zip({Translate[Code], Translate[Friendly]}), MissingField.Ignore)

     

    Translate Table

     

    Table with code names

    Sample Code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RYzBDoAgDEP/ZWf+CDigloxEIRnj4N87lxgv3fraNEZSbpPCdxiClOwp/aAcIjn9pbZezvP2SBlGJvZh3UB9XRvEp+B5bTLVvCPBW65jiTLl/AA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [abc = _t, ghi = _t, def = _t, jkl = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"abc", type text}, {"ghi", type text}, {"def", type text}, {"jkl", type text}}),
    
    #"Rename Columns" = Table.RenameColumns(#"Changed Type",List.Zip({Translate[Code], Translate[Friendly]}), MissingField.Ignore)
in
    #"Rename Columns"

     

    with Columns Renamed