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.

				Location	11006 Bin Sougat	11006 Bin Sougat	11014 Dubai Airport Concourse D	11014 Dubai Airport Concourse D
Partno	Description	Business Cat	Prod Grp	Brand	Qty	Value	Qty	Value
B2BOB22104	CAFLON UK ASSORTED STERELISED STUDS FOR BABIES	665762;HEALTHCARE	117830;EYE/EAR CARE	922017;CAFLON UK	49	717	53	635.862
B2BOB22111	(**)CAFLON UK ASSORTED STERELISED STUDS	665762;HEALTHCARE	117830;EYE/EAR CARE	922017;CAFLON UK	85	3340	70	2450
B2BOB22520315	MALIBU TROP SKIN SELF-TAN BRONZ SPRAY 175ML	1018811;PERSONAL CARE	442514;SUN PREPARATION	557833;MALIBU
B2BOB22520345	MALIBU MLIGHT SHIMER BRONZ SELF-TAN LTN 150ML	1018811;PERSONAL CARE	442514;SUN PREPARATION	557833;MALIBU
B2BOB22520530	MALIBU ALOE AFTER SUN GEL SPRAY 175ML	1018811;PERSONAL CARE	442514;SUN PREPARATION	557833;MALIBU

<colgroup><col><col><col><col><col><col span="2"><col span="2"></colgroup><tbody>
</tbody>

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.