Power Query - unpivot quantity and values

[chriscorpion786]

Hi All,

I have the below data set, and the problem is that quantity and value is dumped by columns, and there are around 50 columns of quantity and value by each store. How can I arrange this in a tabular format and summarize by store / category with quantity and value. I have to select each store separately 50 times for the quantity and then unpivot it and same for the value. Is there a faster solution ? and how can i make the summary by quantity and value together.

Is there a way I can attach a file if needed.
[TABLE="width: 1725"]
<colgroup><col><col><col><col><col><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Location[/TD]
[TD]11006 Bin Sougat[/TD]
[TD]11006 Bin Sougat[/TD]
[TD]11014 Dubai Airport Concourse D[/TD]
[TD]11014 Dubai Airport Concourse D[/TD]
[/TR]
[TR]
[TD]Partno[/TD]
[TD]Description[/TD]
[TD]Business Cat[/TD]
[TD]Prod Grp[/TD]
[TD]Brand[/TD]
[TD]Qty[/TD]
[TD]Value[/TD]
[TD]Qty[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]B2BOB22104[/TD]
[TD]CAFLON UK ASSORTED STERELISED STUDS FOR BABIES[/TD]
[TD]665762;HEALTHCARE[/TD]
[TD]117830;EYE/EAR CARE[/TD]
[TD]922017;CAFLON UK[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]717[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]635.862[/TD]
[/TR]
[TR]
[TD]B2BOB22111[/TD]
[TD](**)CAFLON UK ASSORTED STERELISED STUDS[/TD]
[TD]665762;HEALTHCARE[/TD]
[TD]117830;EYE/EAR CARE[/TD]
[TD]922017;CAFLON UK[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]3340[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]2450[/TD]
[/TR]
[TR]
[TD]B2BOB22520315[/TD]
[TD]MALIBU TROP SKIN SELF-TAN BRONZ SPRAY 175ML[/TD]
[TD]1018811;PERSONAL CARE[/TD]
[TD]442514;SUN PREPARATION[/TD]
[TD]557833;MALIBU[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B2BOB22520345[/TD]
[TD]MALIBU MLIGHT SHIMER BRONZ SELF-TAN LTN 150ML[/TD]
[TD]1018811;PERSONAL CARE[/TD]
[TD]442514;SUN PREPARATION[/TD]
[TD]557833;MALIBU[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B2BOB22520530[/TD]
[TD]MALIBU ALOE AFTER SUN GEL SPRAY 175ML[/TD]
[TD]1018811;PERSONAL CARE[/TD]
[TD]442514;SUN PREPARATION[/TD]
[TD]557833;MALIBU[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks, Mustafa

 

[Ron Coderre]

With your sample data in an Excel Table named Table2....
These are the Power Query steps I used to transform your data so it summarizes by quantity and value by location:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Location", type text}, {"11006 Bin Sougat", type any}, {"11006 Bin Sougat5", type any}, {"11014 Dubai Airport Concourse D", type any}, {"11014 Dubai Airport Concourse D6", type any}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Column1|Partno", "Column2|Description", "Column3|Business Cat", "Column4|Prod Grp", "Location|Brand"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "ColSeq", each Number.Mod([Index],2)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Attribute.1"}),
    #"Added Conditional Column" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each if [ColSeq] = 1 then Text.Start([Attribute.1],Text.Length([Attribute.1])-1) else [Attribute.1] ),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Attribute.1"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"ColSeq", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"ColSeq", type text}}, "en-US")[ColSeq]), "ColSeq", "Value", List.Sum)
in
    #"Pivoted Column"

Is that something you can work with?

 

[chriscorpion786]

Hi Ron,
Thanks for that, but I dont work with M code as i dont understand it.
I wonder if someone could list out the steps, would be great to try it and solve.

Rgds,
Mustafa

 

[Ron Coderre]

Most of the steps were created by the interface....But, because of your requirements and data structure, some tweaking is necessary. (Sometimes there's no avoiding some of the technical aspects of a tool)

Just create a blank query....Open the Advanced Editor window...Paste in the code I posted. Then click "Done". You'll see each of the steps.

 

[chriscorpion786]

Alright, thank you, I will give it a try.