Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
niallhannon
Helper II
Helper II

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

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

 

Capture.PNG

1 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"

 

 


Regards
Zubair

Please try my custom visuals

View solution in original post

9 REPLIES 9
mussaenda
Super User
Super User

Hi @niallhannon,

 

From the data you provided, this is what I did

Table Query.png

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

Table Visual.png

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

 

Zubair_Muhammad
Community Champion
Community Champion

@niallhannon 

 

Could you copy paste current data in the post itself?


Regards
Zubair

Please try my custom visuals

Thanks, here it is:

 

Current Data           
customerrevenue type201920202021202220232019_type2020_type2021_type2022_type2023_type
Acme IncLicence100    Closing DealOrderbookOrderbookOrderbookOrderbook
Acme Incsubscription100100100100100Closing DealOrderbookOrderbookOrderbookOrderbook
Trottle Corpsubscription  200200200  Closing DealOrderbookOrderbook
Krane Ltdsubscription 300300300300 Closing DealClosing DealClosing DealClosing Deal
            
            
            
            
Desired Result           
customerrevenue typeYearTypeRevenue       
Acme IncLicence2019Closing Deal100       
Acme IncLicence2020Orderbook0       
Acme IncLicence2021Orderbook0       
Acme IncLicence2022Orderbook0       
Acme IncLicence2023Orderbook0       
Acme Incsubscription2019Closing Deal100       
Acme Incsubscription2020Orderbook100       
Acme Incsubscription2021Orderbook100       
Acme Incsubscription2022Orderbook100       
Acme Incsubscription2023Orderbook100       
Trottle Corpsubscription2019 0       
Trottle Corpsubscription2020 0       
Trottle Corpsubscription2021Closing Deal200       
Trottle Corpsubscription2022Orderbook200       
Trottle Corpsubscription2023Orderbook200       
Krane Ltdsubscription2019 0       
Krane Ltdsubscription2020Closing Deal300       
Krane Ltdsubscription2021Orderbook300       
Krane Ltdsubscription2022Orderbook300       
Krane Ltdsubscription2023Orderbook300       

@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"

 

 


Regards
Zubair

Please try my custom visuals

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!

@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)

 

 


Regards
Zubair

Please try my custom visuals

@mussaenda I will get back to you.


Regards
Zubair

Please try my custom visuals

Current Data           
customerrevenue type201920202021202220232019_type2020_type2021_type2022_type2023_type
Acme IncLicence100    Closing DealOrderbookOrderbookOrderbookOrderbook
Acme Incsubscription100100100100100Closing DealOrderbookOrderbookOrderbookOrderbook
Trottle Corpsubscription  200200200  Closing DealOrderbookOrderbook
Krane Ltdsubscription 300300300300 Closing DealClosing DealClosing DealClosing Deal
            
            
            
            
Desired Result           
customerrevenue typeYearTypeRevenue       
Acme IncLicence2019Closing Deal100       
Acme IncLicence2020Orderbook0       
Acme IncLicence2021Orderbook0       
Acme IncLicence2022Orderbook0       
Acme IncLicence2023Orderbook0       
Acme Incsubscription2019Closing Deal100       
Acme Incsubscription2020Orderbook100       
Acme Incsubscription2021Orderbook100       
Acme Incsubscription2022Orderbook100       
Acme Incsubscription2023Orderbook100       
Trottle Corpsubscription2019 0       
Trottle Corpsubscription2020 0       
Trottle Corpsubscription2021Closing Deal200       
Trottle Corpsubscription2022Orderbook200       
Trottle Corpsubscription2023Orderbook200       
Krane Ltdsubscription2019 0       
Krane Ltdsubscription2020Closing Deal300       
Krane Ltdsubscription2021Orderbook300       
Krane Ltdsubscription2022Orderbook300       
Krane Ltdsubscription2023Orderbook300       

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.