Forum Discussion
Transpose data from list to predefined matrix with PQ
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 |
|
|
|
|
|
|
|
|
|
|
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
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
8 Replies
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
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
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
