Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
basicquestions
Frequent Visitor

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

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

7 REPLIES 7
IanTaylor
New Member

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

 

 

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

 

 

 

 

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
Community Support
Community Support

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
Super User
Super User

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
Super User
Super User

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
Community Support
Community Support

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.

vkalyjmsft_0-1663233978023.png

vkalyjmsft_1-1663234090264.png

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors