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 Powe...
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"
It's not helpful. After applying the Transpose option in Power Query the data is looks like below.
col1 col2 col3 col4
| Planned | Executed | Pass | Fail |
| 22 | 7 | 6 | 7 |
| 42 | 8 | 7 | 8 |
| 20 | 9 | 10 | 9 |
| 28 | 6 | 11 | 13 |
| 25 | 5 | 13 | 15 |
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"