How to unpivot and relate different columns from an excel file?

[niallhannon]

I am looking for some help on how to transfrom the "current data" rows into the "desired result" as per the screenshot.

The difficulty is how to relate column C and H, D and I etc. I can easily unpivot but can't see how to relate those columns so I end up with the "desired result" layout? Any pointers?

Thanks

[mussaenda]

Hi @niallhannon,

From the data you provided, this is what I did

let
    Source = Excel.Workbook(File.Contents("C:\Users\mussaenda.mejico\Desktop\Cartel1.xlsx"), null, true),
    Foglio1_Sheet = Source{[Item="Foglio1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Foglio1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"customer", type text}, {"revenue type", type text}, {"2019", Int64.Type}, {"2020", Int64.Type}, {"2021", Int64.Type}, {"2022", Int64.Type}, {"2023", Int64.Type}, {"2019_type", type text}, {"2020_type", type text}, {"2021_type", type text}, {"2022_type", type text}, {"2023_type", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"customer", "revenue type"}, "Attribute", "Value"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type2", "Custom", each if Text.Contains([Attribute], "_type") then [Attribute] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if not Text.EndsWith([Attribute], "_type") then [Attribute] else null),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Custom.2", each if [Custom] <> null then [Value] else null),
    #"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Custom.3", each if [Custom.1] <> null then [Value] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column3",{"Value", "Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.2", "Type"}, {"Custom.3", "Revenue"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Custom", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Custom.1.1", "Custom.2"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter",{"Custom.2"}),
    #"Added Conditional Column4" = Table.AddColumn(#"Removed Columns1", "Custom", each if [Custom.1] = null then [Custom.1.1] else [Custom.1]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Conditional Column4",{"Custom.1.1", "Custom.1"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns2",{{"Custom", Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type3",{{"Custom", "Year"}}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Revenue", Int64.Type}, {"Type", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type4", {"Year", "customer", "revenue type"}, {{"Type", each _, type table}, {"Revenue", each List.Sum([Revenue]), type number}}),
    #"Expanded Type" = Table.ExpandTableColumn(#"Grouped Rows", "Type", {"Type"}, {"Type.Type"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Expanded Type",{{"Type.Type", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type5", each ([Type.Type] <> null)),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",null,0,Replacer.ReplaceValue,{"Revenue"})
in
    #"Replaced Value"

This is the Visual

This is not 100% same as your desired output but hope this still helps you.

[Zubair_Muhammad]

@niallhannon 

Could you copy paste current data in the post itself?

[niallhannon]

Thanks, here it is:

Current Data
customer	revenue type	2019	2020	2021	2022	2023	2019_type	2020_type	2021_type	2022_type	2023_type
Acme Inc	Licence	100					Closing Deal	Orderbook	Orderbook	Orderbook	Orderbook
Acme Inc	subscription	100	100	100	100	100	Closing Deal	Orderbook	Orderbook	Orderbook	Orderbook
Trottle Corp	subscription			200	200	200			Closing Deal	Orderbook	Orderbook
Krane Ltd	subscription		300	300	300	300		Closing Deal	Closing Deal	Closing Deal	Closing Deal
Desired Result
customer	revenue type	Year	Type	Revenue
Acme Inc	Licence	2019	Closing Deal	100
Acme Inc	Licence	2020	Orderbook	0
Acme Inc	Licence	2021	Orderbook	0
Acme Inc	Licence	2022	Orderbook	0
Acme Inc	Licence	2023	Orderbook	0
Acme Inc	subscription	2019	Closing Deal	100
Acme Inc	subscription	2020	Orderbook	100
Acme Inc	subscription	2021	Orderbook	100
Acme Inc	subscription	2022	Orderbook	100
Acme Inc	subscription	2023	Orderbook	100
Trottle Corp	subscription	2019		0
Trottle Corp	subscription	2020		0
Trottle Corp	subscription	2021	Closing Deal	200
Trottle Corp	subscription	2022	Orderbook	200
Trottle Corp	subscription	2023	Orderbook	200
Krane Ltd	subscription	2019		0
Krane Ltd	subscription	2020	Closing Deal	300
Krane Ltd	subscription	2021	Orderbook	300
Krane Ltd	subscription	2022	Orderbook	300
Krane Ltd	subscription	2023	Orderbook	300

[Zubair_Muhammad · Accepted solution]

@niallhannon 

This is another way of doing it.

Please see attached file for steps

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckzOTVXwzEtW0lHyyUxOzUtOBbIMDQyAJDJyzskvzsxLV3BJTcwBcv2LUlKLkvLzs4lix+qg2FNcmlScXJRZUJKZnwe3DBdJscUhRfklJTmpCs75RQWYlkOQEdgqZJIEX4Ms8S5KzEtV8ClJwWqDMdhITBLTBhK4sbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [customer = _t, #"revenue type" = _t, #"2019" = _t, #"2020" = _t, #"2021" = _t, #"2022" = _t, #"2023" = _t, #"2019_type" = _t, #"2020_type" = _t, #"2021_type" = _t, #"2022_type" = _t, #"2023_type" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"customer", type text}, {"revenue type", type text}, {"2019", Int64.Type}, {"2020", Int64.Type}, {"2021", Int64.Type}, {"2022", Int64.Type}, {"2023", Int64.Type}, {"2019_type", type text}, {"2020_type", type text}, {"2021_type", type text}, {"2022_type", type text}, {"2023_type", type text}}),
    #"Removed Columns" = Table.RemoveColumns(ChangedType,{"2019_type", "2020_type", "2021_type", "2022_type", "2023_type"}),
    UnpivotedColumns = Table.UnpivotOtherColumns(#"Removed Columns", {"customer", "revenue type"}, "Attribute", "Value"),
    Custom1 = ChangedType,
    #"Removed Columns1" = Table.RemoveColumns(Custom1,{"2019", "2020", "2021", "2022", "2023"}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Removed Columns1", {"customer", "revenue type"}, "Attribute", "Value"),
    Extracted = Table.TransformColumns(#"Unpivoted Columns1", {{"Attribute", each Text.BeforeDelimiter(_, "_"), type text}, {"Value", each Text.BeforeDelimiter(_, "_"), type text}}),
    #"Merged Queries" = Table.NestedJoin(Extracted,{"customer", "revenue type", "Attribute"},UnpivotedColumns,{"customer", "revenue type", "Attribute"},"Extracted",JoinKind.LeftOuter),
    #"Expanded Extracted" = Table.ExpandTableColumn(#"Merged Queries", "Extracted", {"Value"}, {"Value.1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Extracted",{{"customer", Order.Ascending}, {"revenue type", Order.Ascending}, {"Attribute", Order.Ascending}})
in
    #"Sorted Rows"

[mussaenda]

Hi @Zubair_Muhammad,

Can you provide a quick video of what you did? I also want to learn how to create those steps. I reviewed your applied steps but I am lost somewhere.

Thank you!

[Zubair_Muhammad]

@mussaenda 

So Sorry I couldn't get back earlier

Basically in the Query itself

1) I created 2 tables(2 steps) one with years and one with years_type

2) I unpivoted these tables(steps)
3)Then merged these steps. (Just like you merge different tables you can also merge different steps)

[Zubair_Muhammad]

@mussaenda I will get back to you.

[niallhannon]

Thanks @Zubair_Muhammad

[niallhannon]

Current Data
customer	revenue type	2019	2020	2021	2022	2023	2019_type	2020_type	2021_type	2022_type	2023_type
Acme Inc	Licence	100					Closing Deal	Orderbook	Orderbook	Orderbook	Orderbook
Acme Inc	subscription	100	100	100	100	100	Closing Deal	Orderbook	Orderbook	Orderbook	Orderbook
Trottle Corp	subscription			200	200	200			Closing Deal	Orderbook	Orderbook
Krane Ltd	subscription		300	300	300	300		Closing Deal	Closing Deal	Closing Deal	Closing Deal
Desired Result
customer	revenue type	Year	Type	Revenue
Acme Inc	Licence	2019	Closing Deal	100
Acme Inc	Licence	2020	Orderbook	0
Acme Inc	Licence	2021	Orderbook	0
Acme Inc	Licence	2022	Orderbook	0
Acme Inc	Licence	2023	Orderbook	0
Acme Inc	subscription	2019	Closing Deal	100
Acme Inc	subscription	2020	Orderbook	100
Acme Inc	subscription	2021	Orderbook	100
Acme Inc	subscription	2022	Orderbook	100
Acme Inc	subscription	2023	Orderbook	100
Trottle Corp	subscription	2019		0
Trottle Corp	subscription	2020		0
Trottle Corp	subscription	2021	Closing Deal	200
Trottle Corp	subscription	2022	Orderbook	200
Trottle Corp	subscription	2023	Orderbook	200
Krane Ltd	subscription	2019		0
Krane Ltd	subscription	2020	Closing Deal	300
Krane Ltd	subscription	2021	Orderbook	300
Krane Ltd	subscription	2022	Orderbook	300
Krane Ltd	subscription	2023	Orderbook	300