Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Post Partisan
3 years ago
Solved

Replace Column Names through a mapping table

Hello,

I am trying to replace the column names in my table with new columns names coming from a previous step.

In practice, I have an initial table with column names, a second table with 2 columns - one the original column name as found in the original table, and the other one is the replacement.

 

 

So in my final query, the column called "2023 +1Q1" should be replaced with "2024 Q1".

I think I need to use Table.TransformColumnNames, but I can't come up with the right way.... or maybe there is a different way?

 

Thanks!

Kind regards

Valeria

 

 

 

Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    Hello, ValeriaBreve use Table.RenameColumns. It accepts a list of column name pairs (list of lists {"old", "new"}) as a parameter.

    let
    original = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJWitWJVjICskyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"2023+1Q1" = _t, #"2023+1Q2" = _t]),
    second = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY2DDRU0gExTRSArFgduLARXNhIKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Original = _t, Final = _t]),

    renames = List.Zip( Table.ToColumns( second )),
    final = Table.RenameColumns( original, renames)

in
    final

2 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    Hello, ValeriaBreve use Table.RenameColumns. It accepts a list of column name pairs (list of lists {"old", "new"}) as a parameter.

    let
    original = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJWitWJVjICskyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"2023+1Q1" = _t, #"2023+1Q2" = _t]),
    second = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY2DDRU0gExTRSArFgduLARXNhIKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Original = _t, Final = _t]),

    renames = List.Zip( Table.ToColumns( second )),
    final = Table.RenameColumns( original, renames)

in
    final
  • ValeriaBreve's avatar
    ValeriaBreve
    Post Partisan
    3 years ago

    Thanks!!!! works perfectly 🙂 I learned something today 🙂