Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
L70F
Helper II
Helper II

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

 

 

 

 

 

 

 

 

 

 

 

2 ACCEPTED SOLUTIONS

@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 

SU18_powerbi_badge

View solution in original post

@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 

SU18_powerbi_badge

View solution in original post

8 REPLIES 8
AlB
Super User
Super User

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 

SU18_powerbi_badge

 

@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 

SU18_powerbi_badge

 

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? 

ItemNoQtyPrice
A-D13507-00215000
612433868118900
612433868138700
ASB16130A418900
ASB16130A438700
538971015209
545424032700
61239581531780
61239581532445
61239581533340
61239581535265
61239581538225
P-E6306A-15520
P-E6306A-110410

 

Thanks in advance

BR//Larissa

@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 

SU18_powerbi_badge

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

 

@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 

SU18_powerbi_badge

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

BR//Larissa

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

BR//Larissa

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors