Convert rows to columns

Question

I have an item table (question) and a sales price table (question) in Power query

In the item table all items (rows are unique)
In the price table each item code can have up to 4 different sales prices (buy more save more)

So the price table looks like this

Item code #units Price
1234 0 40
1234 5 35
1234 10 30

• Not all items have 4 prices, som items have 1 price and some have 3 or maximum 4.
• The number of units can differ for each price. So price2 can have different #units.

I want to import these sales prices to the item table and I want price1 to be in one column and the #untis for Price1 in the next column, then Price2 and the #units for price 2 so it looks like this.

Item code Price1 units1 Price2 units2 Price3 units3
1234 40 0 35 5 30 10

How do I do this? Anyone who knows?

Best regards Fredrik

Answer 1

Hi @Fredrik Söderholm Pettersson

(When you have a minute could you provide feedback/mark as answer your previous request please - Thanks)

Assuming TableItem and TablePrice loaded to PQ:

The following code for TableItem should do what you want

let  
    Source = Excel.CurrentWorkbook(){[Name="TableItem"]}[Content],  
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}}),  
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Item"}, TablePrice, {"Item"}, "TablePrice", JoinKind.LeftOuter),  
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Item"}),  
    #"Tranformed TablePrice" = Table.TransformColumns(#"Removed Columns",  
        {"TablePrice", each  
            let  
                Idx = Table.AddIndexColumn(_, "Index", 1, 1),  
                Unpivot = Table.UnpivotOtherColumns(Idx, {"Index", "Item"}, "Attribute", "Value"),  
                MergeColumns = Table.CombineColumns(  
                    Table.TransformColumnTypes(Unpivot, {{"Index", type text}}),  
                    {"Attribute", "Index"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"ColName"  
                )  
            in  
                Table.Pivot(MergeColumns, List.Distinct(MergeColumns[ColName]), "ColName", "Value"),  
            type table  
        }  
    ),  
    CombinedNestedTables = Table.Combine(#"Tranformed TablePrice"[TablePrice])  
in  
    CombinedNestedTables  

Corresponding sample available here. Any question let me know

Answer 2

And now I see that the formatting didn't show so it can be a bit tricky to see what I mean. Hopefully someone will understand anyway.
/Fredrik

Answer 3

This was the solution I needed. Thank you!!!

Sorry for my late reply.