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.PNRPriceQty Break0046098-100 631.00 60.000046098-100 945.00 36.000046098-100 3405.00 0.000046098-100 1277.00 10.00 This is how i need the output.PNRQTY 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-100631.0060.003694503405101277151111

Helper III

1 year ago

Solved

Change in Table field

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

Hi aqeel_shaikh, two more solutions:

Output

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

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

10 Replies

dufoq3

Community Champion

1 year ago

Hi aqeel_shaikh, two more solutions:

Output

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

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

Helper III

1 year ago

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

Community Champion

1 year ago

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

AlienSx

Super User

1 year ago

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
Helper III
1 year ago
AlienSx what does "x" stands in the table here ?
- AlienSx
  Super User
  1 year ago
  column name

Omid_Motamedise
Super User
1 year ago
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

Forum Discussion

Change in Table field

10 Replies

Related content

Changing which field is displayed in a Matrix or Table

Change Colour in Slicer Search Field

Displaying Table Fields as Rows in DirectQuery

SUMMARIZE, VALUES, and other table generation/manipuation with field parameters

Custom Date table based on date field in FACT table with hierarchy applied to datetime field