Transpose data from list to predefined matrix with PQ

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 

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