Transpose data from list to predefined matrix with PQ

[L70F]

Hi all! I hope someone can help me out with best practice ways to deal with my problem.

We receive price lists from suppliers that may contain different levels of prices. Number of price levels and starting quantities may vary for different items.

To be able to script this into our ERP we must transform the table from a list (Input table) to a matrix where quantity must be from the left of the price and there must be no spaces between values from the left side (Output table).

Today we solve this by creating a PIVOT of the input table, then copying the PIVOT result to an excel file, to further create an output table using VBA macro. All other formatting is done using Excel Power Query.

If it is possible to get the transpose from input table to output table using PQ, we can streamline the process and avoid several manual steps in between.

I need help with the codes if it is possible to solve my problem by PQ.

I would much appreciate it if someone could assist on this issue. 

Input table:

ItemNo	Qty	Price
A	1	5000
B	1	8900
B	3	8700
C	1	8900
C	3	8700
D	5	209
E	2	700
F	1	780
F	2	445
F	3	340
F	5	265
F	8	225
G	5	520
G	10	410

Output table:

Item No	Qty1	Price 1	Qty2	Price 2	Qty3	Price 3	Qty4	Price 4	Qty5	Price 5	Qty6	Price 6	Qty7	Price 7
A	1	5000
B	1	8900	3	8700
C	1	8900	3	8700	5	7690
D	5	209
E	2	700
F	1	780	2	445	3	340	5	265	8	225
G	5	520	10	410

[AlB · Accepted solution]

@L70F 

Delete the last two lines in your code:

    in

           #"Trimmed Text"

and add the following, right after #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"ItemNo", Text.Trim, type any}})

    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"ItemNo"}, {{"Count", each List.Zip({[Qty], [Price]}), type table [ItemNo=nullable text, Qty=nullable number, Price=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Accumulate([Count],{}, (s,c)=> s & c)),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Qty1", "Price1", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Qty1", Int64.Type}, {"Price1", Int64.Type}, {"Custom.3", Int64.Type}, {"Custom.4", Int64.Type}, {"Custom.5", Int64.Type}, {"Custom.6", Int64.Type}, {"Custom.7", Int64.Type}, {"Custom.8", Int64.Type}, {"Custom.9", Int64.Type}, {"Custom.10", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Count"})
in
    #"Removed Columns"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @L70F 

Is the desired output always up to Qty7, Price7? Is that dependent on the input data? In the example Qty6, Price6 and Qty7, Price7 are empty

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@L70F 

Place the following M code in a blank query to see the steps. I've only changed the names of the columns for the first two. You can change the rest. All this can be made into a function. This version creates as many Qty-Price column pairs as the maximum number of pairs in the data (5 in this case). This can also be changed and be made a fix number (7 perhaps like in your example) or a number dependent on the input data. Probably the best option  is to make all this into a function

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYw7EoAgDETvkpoi/ARKBfUQDPe/hglGcCiW4e1mXq2wgwJN8YgITVU4pIjpV1gughR5vcjrRWEhxWDqfPKf8u2XGEKczLtzfjALrZt7921zj8zm5Vt2b3CwRhbS29oD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ItemNo = _t, Qty = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ItemNo", type text}, {"Qty", Int64.Type}, {"Price", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ItemNo"}, {{"Count", each List.Zip({[Qty], [Price]}), type table [ItemNo=nullable text, Qty=nullable number, Price=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Accumulate([Count],{}, (s,c)=> s & c)),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Qty1", "Price1", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Qty1", Int64.Type}, {"Price1", Int64.Type}, {"Custom.3", Int64.Type}, {"Custom.4", Int64.Type}, {"Custom.5", Int64.Type}, {"Custom.6", Int64.Type}, {"Custom.7", Int64.Type}, {"Custom.8", Int64.Type}, {"Custom.9", Int64.Type}, {"Custom.10", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Count"})
in
    #"Removed Columns"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[L70F]

Hi, thank you for your reply. 

Yes, the number of qty/price intervals depends of the input data and might vary, be less or more than 7. 

I wonder about the meaning of souce in binary? 

When I insert the real item numbers that we use I haven't got the codes to work. 

When I insert the table with real ItenNo (se ex below), the PQ starts as following:

let
Source = Excel.CurrentWorkbook(){[Name="TransposeLFex"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ItemNo", type text}, {"Qty", Int64.Type}, {"Price", Int64.Type}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"ItemNo", Text.Trim, type any}})
in
#"Trimmed Text"

How can I adjust your codes to the beginning of my PQ? 

ItemNo	Qty	Price
A-D13507-002	1	5000
6124338681	1	8900
6124338681	3	8700
ASB16130A4	1	8900
ASB16130A4	3	8700
53897101	5	209
54542403	2	700
6123958153	1	780
6123958153	2	445
6123958153	3	340
6123958153	5	265
6123958153	8	225
P-E6306A-1	5	520
P-E6306A-1	10	410

Thanks in advance

BR//Larissa

[AlB · Accepted solution]

@L70F 

Delete the last two lines in your code:

    in

           #"Trimmed Text"

and add the following, right after #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"ItemNo", Text.Trim, type any}})

    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"ItemNo"}, {{"Count", each List.Zip({[Qty], [Price]}), type table [ItemNo=nullable text, Qty=nullable number, Price=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Accumulate([Count],{}, (s,c)=> s & c)),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Qty1", "Price1", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Qty1", Int64.Type}, {"Price1", Int64.Type}, {"Custom.3", Int64.Type}, {"Custom.4", Int64.Type}, {"Custom.5", Int64.Type}, {"Custom.6", Int64.Type}, {"Custom.7", Int64.Type}, {"Custom.8", Int64.Type}, {"Custom.9", Int64.Type}, {"Custom.10", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Count"})
in
    #"Removed Columns"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[L70F]

Hi, I encountered a problem. When my prices have decimals, delimiter splits these as separate numbers. This went wrong, because cents of the price became the quantities of the next price level. Please, help me to solve it. 

Thanks in advance. 

BR//Larissa

[AlB · Accepted solution]

@L70F 

Place the following M code in a blank query to see the steps. I've changed the delimiter from "," to "|" so that it doesn't intefere with the comma for the decimals. The example has some prices with decimals to show how the code deals with them.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY7LEcAgCER74ewBP0Q95meKcOy/jYBhJAcc9y27Q++wgwPPQ4joAsFwHQ5lpTLLxqKwzIxwsvO3t8BaSl/wknKegHXqW/48WTNNS3IxLX7SfNPOmMyffZv5RbQe/6hPAZf2KIX8jvEC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ItemNo = _t, Qty = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ItemNo", type text}, {"Qty", Int64.Type}, {"Price", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ItemNo"}, {{"Count", each List.Zip({[Qty], [Price]}), type table [ItemNo=nullable text, Qty=nullable number, Price=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Accumulate([Count],{}, (s,c)=> s & c)),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Qty1", "Price1", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Qty1", Int64.Type}, {"Price1", type number}, {"Custom.3", type number}, {"Custom.4", type number}, {"Custom.5", type number}, {"Custom.6", type number}, {"Custom.7", type number}, {"Custom.8", Int64.Type}, {"Custom.9", type number}, {"Custom.10", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Count"})
in
    #"Removed Columns"

the steps that change are two (in red):

#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),

#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Qty1", "Price1", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10"})

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[L70F]

Hi, thanks a lot for a quick reply. I have got it works now. 😊

BR//Larissa

[L70F]

Thanks a lot. It works well. I am happy. 

BR//Larissa