Column Names Change by Month, which throws off PowerQuery. How do I handle this?

[basicquestions]

Greetings,

I hope y'all are all doing well!

I've got a quick question. I've got a file in which the column names change by month. That is, each month, the column names. For example, last month, the columns were august, september, october, november, ... but this month they're september, october, november, etc... 

The problem is this throws off my query in PowerQuery. I'm wondering how to handle this. Any thoughts? Thanks!

Best,

Sam

[IanTaylor]

Hi all - I am pretty much a PQ novice and know just enough to be dangerous.  I am having this issue as well and cannot hack my way around it.  I tried the solution from @ronrsnfld but that just gave me a strange error.

Problem:  I get a CSV every month of monthly data.  Column 1 is text and the header doesn't change.  The headers  for columns 2-13 change every month, pretty standard.  Column 14 is the total column, the header doesn't change.

What I want to do:  Instead of the ChangeType after PromotingHeaders looking for "2023-02" etc., PQ should be reference the column position, e.g., "2" and change the type of "2" to number.

Example of the working/not desired PQ to show the structure:

= Table.TransformColumnTypes(#"Promoted Headers",
{
{"Business Unit", type text}, # static
{"2022-02", type number}, # changes each month
{"2022-03", type number}, # changes each month
{"2022-04", type number}, # changes each month
{"2022-05", type number}, # changes each month
{"2022-06", type number}, # changes each month
{"2022-07", type number}, # changes each month
{"2022-08", type number}, # changes each month
{"2022-09", type number}, # changes each month
{"2022-10", type number}, # changes each month
{"2022-11", type number}, # changes each month
{"2022-12", type number}, # changes each month
{"2023-01", type number}, # changes each month
{"Total", type number} # static
}
)

[ronrsnfld]

"strange error" is not helpful. The text of the actual error, and the line on which it occurs, might be.

How did you try to adapt my method? What code did you try? Which line returned the error?

Below would seem to be the obvious adaptation. What did you try?

types = {{"Business unit", type text}} & List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Promoted Headers"),1), each {_, number}),

[IanTaylor]

Hi @ronrsnfld - I got the PQ working.  As I said, I'm still learning this on the fly.  Trying to make this work in the normal editor was painful, but in the advanced editor I was able to get it to work.  Your code did the trick, it was me messing it up.

This is what I have now:

let
    Source = Csv.Document(Web.Contents("https://REDACTED"),[Delimiter=",", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Removed Top Rows" = Table.Skip(Source,1),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Bottom Rows", [PromoteAllScalars=true]),
    types = {{"Business Unit", type text}} & List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Promoted Headers"),1), each {_, Int64.Type}),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", types),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Business Unit", Order.Ascending}})
in
    #"Sorted Rows"

[v-yanjiang-msft]

Hi @basicquestions ，

Is your problem solved? If so, would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

[ronrsnfld]

There are many ways to refer to columns that change their names.  In general, you start with a List of the column names.  What you do with that list depends on what your code is doing.

For example, imagine

• Column 1-2 are dates named `Start Date` and `End Date`
• columns 3-7 headers are variably named and contain integers
• You want to set the data types

Code like below should do that:

ypes = {{"Start Date", type date}} & {{"End Date", type date}} & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source),2), each {_, Int64.Type}),

#"Changed Type" = Table.TransformColumnTypes(Source, types)

You would use slightly different methods for other functions, but you can get away from having to hard code column headers with variations on this.

[AlexisOlson]

One way to approach this is to unpivot the columns that can change (i.e. unpivot other columns selecting the columns that won't change), do whatever other filtering/transforming you need (if any), and pivot at the end if you really need to (unpivoted months are almost always easier to work with in the model though).

Here's a simple example that shouldn't break if you add or subtract months to the starting table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoE4gogzlSK1YlWMgKykoC4EoizwCLGQFYyEFcBcbZSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Aug = _t, Sep = _t, Oct = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"),
    #"Uppercased Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", Text.Upper, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Uppercased Text", List.Distinct(#"Uppercased Text"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

[v-yanjiang-msft]

Hi @basicquestions ,

After you change the column names in the source, you should also change it in the whole code which reference the column name.

For example this sample, after I change the column name in the source, it return a error in the next step.

Now simply change the Column1 in the code to the correct name.

Hope I understand your problem correctly and it helps!

Best Regards,
Community Support Team _ kalyj

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