Forum Discussion
Transpose data from list to predefined matrix with PQ
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
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
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
Thanks a lot. It works well. I am happy.
BR//Larissa
