Change in Table field

[aqeel_shaikh]

I have a case study, where i need to move the price and q'ty from vertical field to horizontal field.

First table is my actual data. PNR is the common field but i have multiple price and qty break up which i need in one field.below expected result provided.

PNR	Price	Qty Break
0046098-100	631.00	60.00
0046098-100	945.00	36.00
0046098-100	3405.00	0.00
0046098-100	1277.00	10.00

This is how i need the output.

PNR	QTY Price (1)	QTY Break (1)	QTY Break (2)	QTY Price (2)	QTY Break (3)	QTY Price (3)	QTY Break (4)	QTY Price (4)	QTY Break (5)	QTY Price (5)
0046098-100	631.00	60.00	36	945	0	3405	10	1277	15	1111

[dufoq3 · Accepted solution]

Hi @aqeel_shaikh, two more solutions:

Output

v1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMTOwtNA1NDBQ0lEyMzbUgzAMQHSsDroCSxNTiAJjM+wKjE0MoCpwmGBoZG4OUWAIURELAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PNR = _t, Price = _t, #"Qty Break" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Price", Currency.Type}, {"Qty Break", type number}}, "en-US"),
    Grouped = Table.Group(ChangedType, {"PNR"}, {{"All", each 
        [ AddedIndex = Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type),
          UnpivotedOtherColumns = Table.UnpivotOtherColumns(AddedIndex, {"PNR", "Index"}, "Attribute", "Value"),
          MergedColumns = Table.CombineColumns(Table.TransformColumnTypes(UnpivotedOtherColumns, {{"Index", type text}}, "sk-SK"),{"Attribute", "Index"}, Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "Attribute"),
          PivotedColumn = Table.Pivot(MergedColumns, List.Distinct(MergedColumns[Attribute]), "Attribute", "Value")
        ][PivotedColumn], type table}}),
    Combined = Table.Combine(Grouped[All])
in
    Combined

v2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMTOwtNA1NDBQ0lEyMzbUgzAMQHSsDroCSxNTiAJjM+wKjE0MoCpwmGBoZG4OUWAIURELAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PNR = _t, Price = _t, #"Qty Break" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Price", Currency.Type}, {"Qty Break", type number}}, "en-US"),
    GroupedRows = Table.Group(ChangedType, {"PNR"}, {{"All", each 
        List.Accumulate(
            {0..Table.RowCount(_)-1},
            Table.FirstN(Table.RemoveColumns(_, {"Price", "Qty Break"}), 1),
            (s,c)=> Table.AddColumn(Table.AddColumn(s, "Price " & Text.From(c+1), (x)=> [Price]{c}, Currency.Type), "Qty Break " & Text.From(c+1), (y)=> [Qty Break]{c}, type number)), type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined

[aqeel_shaikh]

let
    Source = Excel.Workbook(File.Contents("C:\Users\s441801\Desktop\Pricelist 2023 CHF_cal-day_updated.xlsx"), null, true),
    Prices_Sheet = Source{[Item="Prices",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Prices_Sheet, [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Column4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Price", Currency.Type}, {"Qty Break", Int64.Type}}),
    #"Grouped Rows" = Table.Group(ChangedType, {"PNR"}, {{"All", each [ AddedIndex = Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type),
    #"UnpivotedOtherColumns" = Table.UnpivotOtherColumns(AddedIndex, {"PNR", "Index"}, "Attribute", "Value"),
    #"MergedColumns" = Table.CombineColumns(Table.TransformColumnTypes(UnpivotedOtherColumns, {{"Index", type text}}, "sk-SK"),{"Attribute", "Index"}, Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "Attribute"),
    #"PivotedColumn" = Table.Pivot(MergedColumns, List.Distinct(MergedColumns[Attribute]), "Attribute", "Value")][PivotedColumn], type table}}),
    #"Combined" = Table.Combine(Groupe Rows[All])
in
    #"Combined"

@dufoq3 , i tried V1 can you please check as i getting below error

[dufoq3]

You're missing comma somewhere. Which step caused an error?

Try this, but I've just combined your beginning of the query with mine.

let
    Source = Excel.Workbook(File.Contents("C:\Users\s441801\Desktop\Pricelist 2023 CHF_cal-day_updated.xlsx"), null, true),
    Prices_Sheet = Source{[Item="Prices",Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(Prices_Sheet, [PromoteAllScalars=true]),
    RemovedColumns = Table.RemoveColumns(Promoted Headers,{"Column4"}),
    ChangedType = Table.TransformColumnTypes(RemovedColumns,{{"Price", Currency.Type}, {"Qty Break", type number}}, "en-US"),
    Grouped = Table.Group(ChangedType, {"PNR"}, {{"All", each 
        [ AddedIndex = Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type),
          UnpivotedOtherColumns = Table.UnpivotOtherColumns(AddedIndex, {"PNR", "Index"}, "Attribute", "Value"),
          MergedColumns = Table.CombineColumns(Table.TransformColumnTypes(UnpivotedOtherColumns, {{"Index", type text}}, "sk-SK"),{"Attribute", "Index"}, Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "Attribute"),
          PivotedColumn = Table.Pivot(MergedColumns, List.Distinct(MergedColumns[Attribute]), "Attribute", "Value")
        ][PivotedColumn], type table}}),
    Combined = Table.Combine(Grouped[All])
in
    Combined

[aqeel_shaikh]

@dufoq3- Hi, Thanks for the revert, showing error for below 

#"Promoted Headers" = Table.PromoteHeaders(Prices_Sheet, [PromoteAllScalars=true]),

[aqeel_shaikh]

@dufoq3 - V1 worked...Thanks

[dufoq3]

You're welcome. Enjoy 😉

[Omid_Motamedise]

This is step by step solution

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMTOwtNA1NDBQ0lFSMDM21IOyDECMWB0MJZYmplAlxmY4lBibGMDU4DLF0MjcHKrEEKImFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PNR = _t, Price = _t, #"Qty Break" = _t]),
Custom1 = Table.Group(Source, "PNR", {"S1", each _}),
#"Added Custom" = Table.AddColumn(Custom1, "Custom", each Table.RemoveColumns([S1],"PNR")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.ToRows([Custom])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each List.Combine([Custom.1])),
Custom2 = Table.SplitColumn(#"Added Custom2", "Custom.2", each _)
in
Custom2

[AlienSx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    group = Table.Group(Source, "PNR", {"x", (x) => List.Combine(List.Zip({x[Price], x[Qty Break]}))}),
    split = Table.SplitColumn(group, "x", (x) => x)
in
    split

[aqeel_shaikh]

@AlienSx what does "x" stands in the table here ?

[AlienSx]

column name