Forum Discussion
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 brea...
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 Combinedv2
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
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