Transpose a table

[Jase68]

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...

[amitchandak · Accepted solution]

Refer, if pivot  can help

https://radacad.com/pivot-and-unpivot-with-power-bi

[Greg_Deckler · Accepted solution]

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"

[Anonymous · Accepted solution]

@Jase68 

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.

[Anonymous · Accepted solution]

Hi @Jase68  select the first two columns and pivot the value column in Power Query

[Jase68]

Works a treat - thanks!

[Anonymous · Accepted solution]

@Jase68 

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.

[Jase68]

This works - thanks!

[Greg_Deckler · Accepted solution]

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"

[Jase68]

Brilliant - thank you.

[Greg_Deckler]

I wrote a DAX Transpose once: https://community.powerbi.com/t5/Quick-Measures-Gallery/TRANSPOSE/m-p/630229#M314

[amitchandak · Accepted solution]

Refer, if pivot  can help

https://radacad.com/pivot-and-unpivot-with-power-bi

[Jase68]

Yes, this worked. Thanks.