Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to code Power Query for Headers Changing Names on a monthly basis?

Hello Everyone,

 

I have a file with some column names that are changing every months as it is showing the next 12 months for forecast and also the previous months. My issue is that Power Bi cannot recognize some column names that are no longer in the report and crashes my query. I have been looking for a way to list the column names prior to any change type/removal of columns so it will not look for the exact names... however I could not find a solution to my problem. I must keep the columns starting by RSLF and DPFC, ACTD will be removed.

 

Thanks for your help!

 

Here is the beginning of the query:

 

let
Source = Folder.Files("C:\Documents\Forecast"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Location", type text}, {"ACTD 9/20", Int64.Type}, {"ACTD 8/20", Int64.Type}, {"ACTD 7/20", Int64.Type}, {"ACTD 6/20", Int64.Type}, {"ACTD 5/20", Int64.Type}, {"ACTD 4/20", Int64.Type}, {"ACTD 3/20", Int64.Type}, {"ACTD 2/20", Int64.Type}, {"ACTD 1/20", Int64.Type}, {"ACTD 12/19", Int64.Type}, {"ACTD 11/19", Int64.Type}, {"ACTD 10/19", Int64.Type}, {"ACTD 9/19", Int64.Type}, {"ACTD 8/19", Int64.Type}, {"ACTD 7/19", Int64.Type}, {"ACTD 6/19", Int64.Type}, {"ACTD 5/19", Int64.Type}, {"ACTD 4/19", Int64.Type}, {"ACTD 3/19", Int64.Type}, {"ACTD 2/19", Int64.Type}, {"ACTD 1/19", Int64.Type}, {"ACTD 12/18", Int64.Type}, {"ACTD 11/18", Int64.Type}, {"ACTD 10/18", Int64.Type}, {"ACTD 9/18", Int64.Type}, {"ACTD 8/18", Int64.Type}, {"ACTD 7/18", Int64.Type}, {"ACTD 6/18", Int64.Type}, {"ACTD 5/18", Int64.Type}, {"ACTD 4/18", Int64.Type}, {"ACTD 3/18", Int64.Type}, {"ACTD 2/18", Int64.Type}, {"ACTD 1/18", Int64.Type}, {"ACTD 12/17", Int64.Type}, {"ACTD 11/17", Int64.Type}, {"ACTD 10/17", Int64.Type}, {"RSLF 10/20", Int64.Type}, {"RSLF 11/20", Int64.Type}, {"RSLF 12/20", Int64.Type}, {"RSLF 1/21", Int64.Type}, {"RSLF 2/21", Int64.Type}, {"RSLF 3/21", Int64.Type}, {"RSLF 4/21", Int64.Type}, {"RSLF 5/21", Int64.Type}, {"RSLF 6/21", Int64.Type}, {"RSLF 7/21", Int64.Type}, {"RSLF 8/21", Int64.Type}, {"RSLF 9/21", Int64.Type}, {"DPFC 10/20", Int64.Type}, {"DPFC 11/20", Int64.Type}, {"DPFC 12/20", Int64.Type}, {"DPFC 1/21", Int64.Type}, {"DPFC 2/21", Int64.Type}, {"DPFC 3/21", Int64.Type}, {"DPFC 4/21", Int64.Type}, {"DPFC 5/21", Int64.Type}, {"DPFC 6/21", Int64.Type}, {"DPFC 7/21", Int64.Type}, {"DPFC 8/21", Int64.Type}, {"DPFC 9/21", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ACTD 9/20", "ACTD 8/20", "ACTD 7/20", "ACTD 6/20", "ACTD 5/20", "ACTD 4/20", "ACTD 3/20", "ACTD 2/20", "ACTD 1/20", "ACTD 12/19", "ACTD 11/19", "ACTD 10/19", "ACTD 9/19", "ACTD 8/19", "ACTD 7/19", "ACTD 6/19", "ACTD 5/19", "ACTD 4/19", "ACTD 3/19", "ACTD 2/19", "ACTD 1/19", "ACTD 12/18", "ACTD 11/18", "ACTD 10/18", "ACTD 9/18", "ACTD 8/18", "ACTD 7/18", "ACTD 6/18", "ACTD 5/18", "ACTD 4/18", "ACTD 3/18", "ACTD 2/18", "ACTD 1/18", "ACTD 12/17", "ACTD 11/17", "ACTD 10/17"}),

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    5 years ago

    Hi Anonymous ,

     

    Really complicated.

     

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\lionelch\Desktop\Sample data\3.xlsx"), null, true),
    Sheet5_Sheet = Source{[Item="Sheet5",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet5_Sheet, [PromoteAllScalars=true]),
    #"ColumnNames" = Table.ColumnNames(#"Promoted Headers"),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Promoted Headers",
        {
            {#"ColumnNames"{0}, Int64.Type}, 
            {#"ColumnNames"{1}, Int64.Type}, 
            {#"ColumnNames"{2}, Int64.Type}, 
            {#"ColumnNames"{3}, Int64.Type}, 
            {#"ColumnNames"{4}, Int64.Type}, 
            {#"ColumnNames"{5}, Int64.Type}, 
            {#"ColumnNames"{6}, Int64.Type}, 
            {#"ColumnNames"{7}, Int64.Type}, 
            {#"ColumnNames"{8}, Int64.Type}, 
            {#"ColumnNames"{9}, Int64.Type}
        }
    ),
    #"Renamed Columns" = Table.RenameColumns(
        #"Changed Type",
        {
            {#"ColumnNames"{0}, "S1"}, 
            {#"ColumnNames"{1}, "S2"},
            {#"ColumnNames"{2}, "S3"},
            {#"ColumnNames"{3}, "S4"}, 
            {#"ColumnNames"{4}, "S5"},
            {#"ColumnNames"{5}, "S6"},
            {#"ColumnNames"{6}, "S7"},
            {#"ColumnNames"{7}, "S8"},
            {#"ColumnNames"{8}, "S9"}, 
            {#"ColumnNames"{9}, "S10"}
        }
    )
in
    #"Renamed Columns"

     

     

     

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

12 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi Anonymous 

    Maybe I misunderstood, your initial request didn't mention renaming columns? Only changing type or deleting them?

    If you want to change type without naming the columns, you can use a recursive function.  This code includes the earlier code I wrote to delete columns too.

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("DcS3EQAwCASwXai9EUdBDvsP4FchZlKlR2bIHUWgTFSFutEM2kV3JPIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ACTD 9/20" = _t, #"ACTD 8/20" = _t, #"ACTD 7/20" = _t, #"ACTD 6/20" = _t, #"RSLF 10/20" = _t, #"RSLF 11/20" = _t, #"RSLF 12/20" = _t, #"DPFC 10/20" = _t, #"DPFC 11/20" = _t, #"DPFC 12/20" = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"RSLF 10/20", "Location"}}),
    RemovedColumns = Table.RemoveColumns(#"Renamed Columns", Table.ColumnNames(Table.SelectColumns(#"Renamed Columns", List.FindText(Table.ColumnNames(#"Renamed Columns"),"ACTD")))),

    ChangeTypes = (Tab as table, loop as number) =>

        let
            Temp =  if Table.ColumnNames(Tab){loop} = "Location" 
                    then Table.TransformColumnTypes(Tab, {Table.ColumnNames(Tab){loop}, type text})
                    else Table.TransformColumnTypes(Tab, {Table.ColumnNames(Tab){loop}, Int64.Type}),
                
            result = if loop >= Table.ColumnCount(Tab)-1 then Temp else @ChangeTypes(Temp, loop + 1)
        in
            result,

    Res = ChangeTypes(RemovedColumns,0)

in
    Res

     

     

    If you need to rename columns too I can try writing a function for that but it would require that the columns be in a known order so that they can be referenced by position rather than by name.

    Regards

    Phil

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Phil, sorry if I was unclear. To summarize my request, there are 2 topics:

      - Remove ACTD columns ==> Resolved thanks to your initial function

      - Consider all columns DPFC and RSLF, no matter what month/year follow. For instance, "ACTD 10/17" will no longer appear in the new report and "ACTD 10/20" will appear. If I leave the full names hard coded, the query will crash once I refresh it with the new column names.

       

      Hope I am clear ğŸ™‚

      Thanks

       

      • PhilipTreacy's avatar
        PhilipTreacy
        Super User
        5 years ago

        Hi Anonymous 

        No worries, all good.

        Have you tried using the last code I posted which included the function to change type for the columns, as well as delete the ACTD columns?

        Regards

        Phil

        If I answered your question please mark my post as the solution.
        If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Paul, Thanks. I have already watched it a couple of times but I think my skills are not good enough to replicate it into my model. In her example, she is referring to only 1 column that changes hence her function fx= Table.ColumnNames(#"Promoted Headers"){0} to select the first column. How could I add all the columns into these curly brackets?

      Thanks

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi Anonymous 

    To look for any column with ACTD in it's name and remove it you can use this line

     

    RemovedColumns = Table.RemoveColumns(Source, Table.ColumnNames(Table.SelectColumns(Source, List.FindText(Table.ColumnNames(Source),"ACTD"))))

     

    Regards

    Phil

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Philip,

      Thanks for that, it works for removing the ACTD columns.

      Still need to find the solution for having a dynamic list of headers and it would be sorted.

       

      Thanks,

       

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        5 years ago

        Anonymous 

        To change several columns, you need to use the "Table.ColumnNames" expression for each column that needs a name change.

        For example, to change the first three column names of a table (beware that Power Query references rows and columns starting at "0"):

        Or You can do this in the "Changed Type" line which also references column names:

        This is the whole code for this particular example:

         

         

        let
    Source = Excel.Workbook(File.Contents("C:\Users\Paul\OneDrive\1 Shared web\Comm PBIs\1 Community files\Channel Item actuals target all 2020 2 tables.xlsx"), null, true),
    Actuals_Table = Source{[Item="Actuals",Kind="Table"]}[Data],
    #"Renamed Columns" = Table.RenameColumns(Actuals_Table,{{Table.ColumnNames(Actuals_Table){0}, "Date"}, {Table.ColumnNames(Actuals_Table){1}, "Sales"}, {Table.ColumnNames(Actuals_Table){2}, "Dist Channel"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Sales", Int64.Type}, {"Dist Channel", type text}})
in
    #"Changed Type"

         

        (The Naming trick in this case is applied at the column renaming stage)

         

         

         

         

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    Really complicated.

     

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\lionelch\Desktop\Sample data\3.xlsx"), null, true),
    Sheet5_Sheet = Source{[Item="Sheet5",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet5_Sheet, [PromoteAllScalars=true]),
    #"ColumnNames" = Table.ColumnNames(#"Promoted Headers"),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Promoted Headers",
        {
            {#"ColumnNames"{0}, Int64.Type}, 
            {#"ColumnNames"{1}, Int64.Type}, 
            {#"ColumnNames"{2}, Int64.Type}, 
            {#"ColumnNames"{3}, Int64.Type}, 
            {#"ColumnNames"{4}, Int64.Type}, 
            {#"ColumnNames"{5}, Int64.Type}, 
            {#"ColumnNames"{6}, Int64.Type}, 
            {#"ColumnNames"{7}, Int64.Type}, 
            {#"ColumnNames"{8}, Int64.Type}, 
            {#"ColumnNames"{9}, Int64.Type}
        }
    ),
    #"Renamed Columns" = Table.RenameColumns(
        #"Changed Type",
        {
            {#"ColumnNames"{0}, "S1"}, 
            {#"ColumnNames"{1}, "S2"},
            {#"ColumnNames"{2}, "S3"},
            {#"ColumnNames"{3}, "S4"}, 
            {#"ColumnNames"{4}, "S5"},
            {#"ColumnNames"{5}, "S6"},
            {#"ColumnNames"{6}, "S7"},
            {#"ColumnNames"{7}, "S8"},
            {#"ColumnNames"{8}, "S9"}, 
            {#"ColumnNames"{9}, "S10"}
        }
    )
in
    #"Renamed Columns"

     

     

     

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.