Forum Discussion
Interchanging the rows and columns values
I am having the excel spreadsheet data in below format.
The expected data format shown below to perform required visulizations.
How to interchange column and row values in Power BI ?
Is it possible ??
This seems to work:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCshJzMtLTVHSUTI0AhHGIMIURJgBCSNTpVidaCXXitTk0hKwKnMgtgBiSyAGqYAoCEgsLoYKgBQYGoAIQ4h5IHm3xMwcqJwZVDPMpthYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"1/1/2019" = _t, #"1/2/2019" = _t, #"1/3/2019" = _t, #"1/4/2019" = _t, #"1/5/2019" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"1/1/2019", Int64.Type}, {"1/2/2019", Int64.Type}, {"1/3/2019", Int64.Type}, {"1/4/2019", Int64.Type}, {"1/5/2019", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Column1]), "Column1", "Value") in #"Pivoted Column"
3 Replies
Try the Transpose option in Power Query
This seems to work:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCshJzMtLTVHSUTI0AhHGIMIURJgBCSNTpVidaCXXitTk0hKwKnMgtgBiSyAGqYAoCEgsLoYKgBQYGoAIQ4h5IHm3xMwcqJwZVDPMpthYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"1/1/2019" = _t, #"1/2/2019" = _t, #"1/3/2019" = _t, #"1/4/2019" = _t, #"1/5/2019" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"1/1/2019", Int64.Type}, {"1/2/2019", Int64.Type}, {"1/3/2019", Int64.Type}, {"1/4/2019", Int64.Type}, {"1/5/2019", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Column1]), "Column1", "Value") in #"Pivoted Column"