Renaming colomns

[victoire0]

Hi everyone, 

Do you know how I can do the following ? : I would like to rename specific columns by keeping only the 2 digits left and adding a "W" in front (I'm working on weeks so 012020 will be W01).

I guess I need to do it in the advanced editor, unfortunately I'm a beginner in the M language.... 

Could you please help me 🙂 ?

Thanks in advance !

Victoire

[edhans]

Here is one way that is fairly simple, and may or may not work depending on how many rows and column you have. It changes this:

into this:

The code is this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSAeMkIFaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"012020" = _t, #"022020" = _t, #"032020" = _t, #"Other Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"012020", type text}, {"022020", type text}, {"032020", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "New Column Name", each if Text.End([Column1], 4) = "2020" then "W" & Text.Start([Column1], 2) else [Column1]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"New Column Name", "Column2"}),
    #"Transposed Table1" = Table.Transpose(#"Removed Other Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"W01", type text}, {"W02", type text}, {"W03", type text}, {"Other Column", type text}})
in
    #"Changed Type2"

It does this by:

1. demoting the column names to the first row
2. transposing the table (this likely won't work if you have thousands of records as it will try and turn them to thousands of columns)
3. Renaming the columns
4. Keeping the new names and removing the old
5. Transposing the table back
6. Promoting the first row back to the headers.

I am working on another solution that is a bit more complex but more dynamic, but wanted to give you this option as it is a great and easy to understand transformation if your records not too large.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[edhans · Accepted solution]

@victoire0 - here you go. This is a bit shorter. It is 100% manual coding though vs my previous example which was 100% UI driven.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSAeMkIFaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"012020" = _t, #"022020" = _t, #"032020" = _t, #"Other Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"012020", type text}, {"022020", type text}, {"032020", type text}}),
    OriginalName = 
        List.Select(
            Table.ColumnNames( #"Changed Type"), 
            each Text.End(_,4)="2020"
            ),
    NewName = 
        List.Transform(
            OriginalName, each "W" & Text.Start(_,2) 
            ),
    #"Dynamic Rename" = 
        Table.RenameColumns(
            #"Changed Type", 
            List.Zip({OriginalName, NewName})
            )
in
    #"Dynamic Rename"

Here is is by step:

• Source and #Changed Type are just me keying in the same table as above example
• OriginalName generates a list of the column names that ends in 2020. You would need to change the List.Select here to fit your needs. My code will break in 2021. You could use something like this:
  • Text.Start(Text.End(_,4),3) = "202"
  • That will find all columns that end in 202x. This code will break Jan 1, 2030.
• NewName uses a "W" and adds the first two chars of the columns from the OriginalName list.
• DynamicRename uses Table.RenameColumns and uses List.ZIp to use the lists above for the rename. List.Zip returns the following list of lists in memory.
  • 
  • So 012020 is paired with W01, 022020 is paired with W02, etc. So rename 012020 to W01. Same as this:
    • Table.RenameColumns(#"Changed Type",{{"012020", "W01"}})

Hopefully that gets you started.

[Greg_Deckler]

You can right-click the columns and rename them in the Query Editor. Or are we talking about a bunch of columns here? Are the columns always named the same?

[victoire0]

Hi Greg_Deckler, thanks for your reply !

Indeed, those columns names are changing every week that's why I can't change it directly as you suggested...

[Greg_Deckler]

@victoire0 - OK, in that case perhaps @ImkeF  or @edhans have some ideas. Dynamically changing columns is going to present a challenge. Can you provide an example of the column names and how they change week-to-week?