Forum Discussion
basicquestions
3 years agoFrequent 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 mont...
AlexisOlson
3 years agoSuper 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"