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

[Anonymous]

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"}),

[v-lionel-msft · Accepted solution]

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.

[PhilipTreacy]

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]

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]

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.

[PhilipTreacy]

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]

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]

@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)

[Anonymous]

Thanks Paul. I have 24 columns that could potentially change, do I need to include the 24 columns in this function? It will be pretty long. Is there a solution where we could list all the columns and refer to this function or parameter maybe?

[PaulDBrown]

@Anonymous 

Yes, you will need to include the expression for all the columns (and only chage the column reference for each in the "{ }" ).

I am not aware of another way of doing this. Maybe @ImkeF  can chime in.

[PaulDBrown]

@Anonymous 

See if this video from Curbal can help:

Managing changing column headers in Power Query 

[Anonymous]

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

[amitchandak]

@ImkeF , Can you help on this