Unpivot 2 types of columns in Direct Query

[karo]

Hi,

I am wonder if there is any clever way to unpivot columns in Power BI  with two types of columns (sales ammount by month and Quantity per month) in  Direct Query.

From:

Client	City	Product ID	June 2020 (Sales Amount)	June 2020 (Quantity)	July 2020 (Sales Amount)	July 2020 (Quantity)	August 2020 (Sales Amount)	August 2020 (Quantity)
A	X	1234	100	1	1000	10	10000	100
B	Y	1345	50	1	2500	50	5000	100
C	Z	1456	10	1	50	5	0	0

To:

	City	Product ID	Date	Sales Amount	Quantity
A	X	1234	June 2020	100	1
A	X	1234	July 2020	1000	10
A	X	1234	August 2020	10000	100
B	XY	1345	June 2020	50	1
B	XY	1345	July 2020	2500	50
B	XY	1345	August 2020	5000	100
C	XYZ	1456	June 2020	10	1
C	XYZ	1456	July 2020	50	5
C	XYZ	1456	August 2020	0	0

Thank you in advance for any hints!,

Karo

[AllisonKennedy]

Paste this code into the Advanced Editor of Power Query Editor to see how to unpivot then repivot:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dYwxD8IgEIX/CmHSpANScMd20UnjohKGRolpgjSxMPTfe0BFGTrcvfd49yElbkyvrcMVbno3gRzfw8PfHdq3EA7eakQJJWh17owekXgN3rp1WZ18Zx3Q6dlMi0Su/gnhn350C0xR/ihVSSygvsBsaM2CEBJ2ctGS2c8aoR34a8g14yD8y1Aez3haBdKAv4XM+DZ/m29hSBylPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Client", type text}, {"City", type text}, {"Product ID", Int64.Type}, {"June 2020 (Sales Amount)", Int64.Type}, {"June 2020 (Quantity)", Int64.Type}, {"July 2020 (Sales Amount)", Int64.Type}, {"July 2020 (Quantity)", Int64.Type}, {"August 2020 (Sales Amount)", Int64.Type}, {"August 2020 (Quantity)", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Client", "City", "Product ID"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Split Column by Delimiter", {{"Attribute.2", each Text.BeforeDelimiter(_, ")"), type text}}),
#"Pivoted Column" = Table.Pivot(#"Extracted Text Before Delimiter", List.Distinct(#"Extracted Text Before Delimiter"[Attribute.2]), "Attribute.2", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Attribute.1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Attribute.1", "Date"}})
in
#"Renamed Columns"

[karo]

hi @AllisonKennedy ,

thank you for your reply, however few steps are not supported in Direct Query (example: Split Column by Delimiter).

regards,

karo

[v-kelly-msft]

Hi @karo ,

Check the similar thread below:

https://community.powerbi.com/t5/Desktop/Using-dax-to-unpivot-a-table/td-p/421116

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!