Solved: Transpose / Pivot to include multiple columns?

Anonymous · ‎04-18-2021

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!

selimovd · ‎04-18-2021

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"

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

View solution in original post

selimovd · ‎04-18-2021