Forum Discussion
Transpose a table
Hi PBI Experts
I habe a simple table that I need to transpose so that the DATES (Year-Wk) move from the rows to the columns.
This is my current format;
Year-Week MeasureID Value
2020-01 A 5
2020-02 A 10
2020-01 B 15
2020-02 B 20
I want to transpose on load so I get the data into a table that looks like this;
MeasureID 2020-01 2020-02
A 5 10
B 15 20
I have tried many ways but I cannot get it to work. It feels like there should be a simple answer so hoping someone can help...
Refer, if pivot can help
Here is the Power Query version:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1MFTSUXIEYlOlWB2YmBFUzNAASRCk0AkkiK4SJGgEVBkLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Year-Week" = _t, MeasureID = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year-Week", type date}, {"MeasureID", type text}, {"Value", Int64.Type}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Year-Week", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Year-Week", type text}}, "en-US")[#"Year-Week"]), "Year-Week", "Value", List.Sum) in #"Pivoted Column"Query Editor => Pivot column
Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
Refer, if pivot can help
Yes, this worked. Thanks.
- I wrote a DAX Transpose once: https://community.powerbi.com/t5/Quick-Measures-Gallery/TRANSPOSE/m-p/630229#M314
Here is the Power Query version:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1MFTSUXIEYlOlWB2YmBFUzNAASRCk0AkkiK4SJGgEVBkLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Year-Week" = _t, MeasureID = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year-Week", type date}, {"MeasureID", type text}, {"Value", Int64.Type}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Year-Week", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Year-Week", type text}}, "en-US")[#"Year-Week"]), "Year-Week", "Value", List.Sum) in #"Pivoted Column"Brilliant - thank you.
Query Editor => Pivot column
Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.This works - thanks!
Works a treat - thanks!
