Forum Discussion
DAX Help
I'll post the code (which you can place in the Advanced Editor in Power Query). This'll either confound you or inspire you. Let's hope it's the latter.
Basically you'll be unpivotting the column pairs. To do this you need consistency in the naming of pairs e.g. Jan21 for 2 columns or Jan for 2 columns.
Here's the code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY0xDoAgDEXvQhwZLIhx1UEP4EiYCAOLGpT729KoDA5NX/Jf/rdWjEKKOactXjkFZMBrdNviU4QdopPsLWn3IcVwIg8UDqSBJgT4vPWIvkiGEkNSx7VFmf4mVdEY+8+rJ0E9Q8ilGqrGd5QtyjUXC+du", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SuperMarket = _t, #"Goods Purchased" = _t, #"Jan21 Quantity" = _t, #"Jan Expenditure" = _t, #"Feb21 Quantity" = _t, #"Feb Expenditure" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"SuperMarket", type text}, {"Goods Purchased", type text}, {"Jan21 Quantity", Int64.Type}, {"Jan Expenditure", type text}, {"Feb21 Quantity", Int64.Type}, {"Feb Expenditure", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"SuperMarket", "Goods Purchased"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","21","",Replacer.ReplaceText,{"Attribute.1"}), #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute.2]), "Attribute.2", "Value"), #"Replaced Value1" = Table.ReplaceValue(#"Pivoted Column","$","",Replacer.ReplaceText,{"Expenditure"}), #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value1",{{"Expenditure", Int64.Type}, {"Quantity", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Attribute.1", "Period"}}) in #"Renamed Columns"Looks complex but it was all done with the mouse + interface.
See if you can apply it your data. I won't be around for a few hours but I encourage you to work it out and post back later if you're struggling.
There's lots of forum examples of unpivotting pairs. Let me know how it goes
I'll post the code (which you can place in the Advanced Editor in Power Query). This'll either confound you or inspire you. Let's hope it's the latter.
Basically you'll be unpivotting the column pairs. To do this you need consistency in the naming of pairs e.g. Jan21 for 2 columns or Jan for 2 columns.
Here's the code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY0xDoAgDEXvQhwZLIhx1UEP4EiYCAOLGpT729KoDA5NX/Jf/rdWjEKKOactXjkFZMBrdNviU4QdopPsLWn3IcVwIg8UDqSBJgT4vPWIvkiGEkNSx7VFmf4mVdEY+8+rJ0E9Q8ilGqrGd5QtyjUXC+du", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SuperMarket = _t, #"Goods Purchased" = _t, #"Jan21 Quantity" = _t, #"Jan Expenditure" = _t, #"Feb21 Quantity" = _t, #"Feb Expenditure" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SuperMarket", type text}, {"Goods Purchased", type text}, {"Jan21 Quantity", Int64.Type}, {"Jan Expenditure", type text}, {"Feb21 Quantity", Int64.Type}, {"Feb Expenditure", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"SuperMarket", "Goods Purchased"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","21","",Replacer.ReplaceText,{"Attribute.1"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute.2]), "Attribute.2", "Value"),
#"Replaced Value1" = Table.ReplaceValue(#"Pivoted Column","$","",Replacer.ReplaceText,{"Expenditure"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value1",{{"Expenditure", Int64.Type}, {"Quantity", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Attribute.1", "Period"}})
in
#"Renamed Columns"Looks complex but it was all done with the mouse + interface.
See if you can apply it your data. I won't be around for a few hours but I encourage you to work it out and post back later if you're struggling.
There's lots of forum examples of unpivotting pairs. Let me know how it goes
I tried to unpivot my whole table but I do not have a fixed column that I can use as a pivot (Many examples online use time but Im pivoting the time(monthly) column as well)
My apologies but I am still very new and the whole process isn't intuitive for my dumb brain.
Another problem I faced was with the insane amount of categorizations (this is a mock-up data, the actual one has like 4-5 category columns and each has different categories like geolocation, supermarket names, various goods, etc). If you include each of the month's data, it went all the way to 100 over columns since I have >5 years of data.
For analysis purposes, I believe what I want to achieve is similar to this:
https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/
where I can make comparisons between different categories relative to time.
