Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I cant work out if I need to transpose / unpivot or something else for the following, maybe having a brain freeze moment here. All help appreciated as always:
I have a data table as such
Name | Year | Delivered | Authorised | % Delivered/Authorised |
A | 2018 | 3 | 1 | 300% |
A | 2019 | 6 | 2 | 300% |
A | 2020 | 3 | 1 | 300% |
B | 2018 | 90 | 10 | 900% |
B | 2019 | 33 | 33 | 100% |
B | 2020 | 9 | 10 | 90% |
But I want to unpivot the data so the year column runs along the top and it looks like below
2018 | 2019 | 2020 | |||||||
A | 3 | 1 | 300% | 6 | 2 | 300% | 3 | 1 | 300% |
B | 90 | 10 | 900% | 33 | 33 | 100% | 9 | 10 | 90% |
I can't work out why this is so complicated, feel like I'm maybe overthinking, would be grateful for any help and logic here, thanks!
Solved! Go to Solution.
Hey @Anonymous ,
I think to perform that you have to merge the 3 columns first, do a pivot for column Year and then split them again afterwards.
Check the following M-Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMLQAUsZAbAiiDQxUlWJ14HKWQMoMxMSUMzLAps8JYaYlSIGhAZiFKgsy1dgYRhiiyoLNtURoBcrFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Year = _t, Delivered = _t, Authorised = _t, #"% Delivered/Authorised" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Year", Int64.Type}, {"Delivered", Int64.Type}, {"Authorised", Int64.Type}, {"% Delivered/Authorised", Percentage.Type}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Delivered", type text}, {"Authorised", type text}, {"% Delivered/Authorised", type text}}, "de-CH"),{"Delivered", "Authorised", "% Delivered/Authorised"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Merged Columns", {{"Year", type text}}, "de-CH"), List.Distinct(Table.TransformColumnTypes(#"Merged Columns", {{"Year", type text}}, "de-CH")[Year]), "Year", "Merged"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "2018", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"2018.1", "2018.2", "2018.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"2018.1", Int64.Type}, {"2018.2", Int64.Type}, {"2018.3", Int64.Type}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "2019", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"2019.1", "2019.2", "2019.3"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"2019.1", Int64.Type}, {"2019.2", Int64.Type}, {"2019.3", Int64.Type}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "2020", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"2020.1", "2020.2", "2020.3"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"2020.1", Int64.Type}, {"2020.2", Int64.Type}, {"2020.3", type number}})
in
#"Changed Type3"
Hey @Anonymous ,
I think to perform that you have to merge the 3 columns first, do a pivot for column Year and then split them again afterwards.
Check the following M-Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMLQAUsZAbAiiDQxUlWJ14HKWQMoMxMSUMzLAps8JYaYlSIGhAZiFKgsy1dgYRhiiyoLNtURoBcrFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Year = _t, Delivered = _t, Authorised = _t, #"% Delivered/Authorised" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Year", Int64.Type}, {"Delivered", Int64.Type}, {"Authorised", Int64.Type}, {"% Delivered/Authorised", Percentage.Type}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Delivered", type text}, {"Authorised", type text}, {"% Delivered/Authorised", type text}}, "de-CH"),{"Delivered", "Authorised", "% Delivered/Authorised"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Merged Columns", {{"Year", type text}}, "de-CH"), List.Distinct(Table.TransformColumnTypes(#"Merged Columns", {{"Year", type text}}, "de-CH")[Year]), "Year", "Merged"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "2018", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"2018.1", "2018.2", "2018.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"2018.1", Int64.Type}, {"2018.2", Int64.Type}, {"2018.3", Int64.Type}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "2019", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"2019.1", "2019.2", "2019.3"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"2019.1", Int64.Type}, {"2019.2", Int64.Type}, {"2019.3", Int64.Type}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "2020", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"2020.1", "2020.2", "2020.3"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"2020.1", Int64.Type}, {"2020.2", Int64.Type}, {"2020.3", type number}})
in
#"Changed Type3"
This solved it,
Many thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |