Forum Discussion
Transform Data which has repeated column names but different values.
Item E1 Item E2 Item E3 Item E4 CASH CREDIT Item S1 Item S2 Item S3 CASH CREDIT Date Salesman Name Branch ######## a X 3 0 2 6 20...
How is Power Query supposed to know what are "E-Items" vs "S-Items" ? Purely by their column order?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hU+xDoIwFPwV0hmSvragjCgksjhYBxPCUAkJgzAo/x/fYWk0DA7X3ru+d+/aNCIScUA992NUUWAqMB2YYXYs7AnXpSrr6/piw5wNc1Zvutu4EaWbe1ase/Sv0U3R2Y2oD083dcNXnv+Am6SEZKKkQjTHuDGwWDIQJcONQuNIvarRbvRH3vjckY9BaMXPjActbmC7/cL0j5eirVfHKPxmSn2wHEW+bsi9mqVrVjKibd8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t]), Headers = List.Skip(Record.ToList(Source{0}),3), #"Converted to Table" = Table.FromList(Headers, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1, Int64.Type), #"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Column1],each if [Column1]="CASH" then Text.At(#"Added Index"{[Index]-1}[Column1],5) & "-Items CASH" else if [Column1]="CREDIT" then Text.At(#"Added Index"{[Index]-2}[Column1],5) & "-Items CREDIT" else [Column1],Replacer.ReplaceValue,{"Column1"}), #"Removed Top Rows" = Table.Skip(Source,1), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), NewHeaders = List.Zip({List.Skip(Table.ColumnNames(#"Promoted Headers"),3),#"Replaced Value"[Column1]}), Renamed = Table.RenameColumns(#"Promoted Headers",NewHeaders), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Renamed, {"Date", "Salesman Name", "Branch"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each Text.Contains([Attribute], "-")), #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Category", "Attribute.2"}), #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.2]), "Attribute.2", "Value"), #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"CASH", Currency.Type}, {"CREDIT", Currency.Type}}) in #"Changed Type"
lbendlin
Super User
Super UserHow is Power Query supposed to know what are "E-Items" vs "S-Items" ? Purely by their column order?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hU+xDoIwFPwV0hmSvragjCgksjhYBxPCUAkJgzAo/x/fYWk0DA7X3ru+d+/aNCIScUA992NUUWAqMB2YYXYs7AnXpSrr6/piw5wNc1Zvutu4EaWbe1ase/Sv0U3R2Y2oD083dcNXnv+Am6SEZKKkQjTHuDGwWDIQJcONQuNIvarRbvRH3vjckY9BaMXPjActbmC7/cL0j5eirVfHKPxmSn2wHEW+bsi9mqVrVjKibd8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t]),
Headers = List.Skip(Record.ToList(Source{0}),3),
#"Converted to Table" = Table.FromList(Headers, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1, Int64.Type),
#"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Column1],each if [Column1]="CASH" then Text.At(#"Added Index"{[Index]-1}[Column1],5) & "-Items CASH" else if [Column1]="CREDIT" then Text.At(#"Added Index"{[Index]-2}[Column1],5) & "-Items CREDIT" else [Column1],Replacer.ReplaceValue,{"Column1"}),
#"Removed Top Rows" = Table.Skip(Source,1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
NewHeaders = List.Zip({List.Skip(Table.ColumnNames(#"Promoted Headers"),3),#"Replaced Value"[Column1]}),
Renamed = Table.RenameColumns(#"Promoted Headers",NewHeaders),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Renamed, {"Date", "Salesman Name", "Branch"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each Text.Contains([Attribute], "-")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Category", "Attribute.2"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.2]), "Attribute.2", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"CASH", Currency.Type}, {"CREDIT", Currency.Type}})
in
#"Changed Type"Let's say that there is a merged row above
Column1 Column2 Column3 Column4 Column5 Column6 Column7 Column8 Column9 Column10 Column11 Column12 Column13 Column14 E-Items S Items Item E1 Item E2 Item E3 Item E4 CASH CREDIT Item S1 Item S2 Item S3 CASH CREDIT Date Salesman Name Branch ########## a X 3 0 2 6 20 30 5 2 34 43 30 ########## b S 12 1 4 4 16 24 78 243 34 43 21 ########## c A 5 15 0 9 19 12 9 0 65 20 14 - AlienSx
let fx = (lst, positions) => List.Transform(positions, (i) => lst{i}), Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], cash = List.Buffer(List.PositionOf(Record.FieldValues(Source{1}), "CASH", Occurrence.All)), credit = List.Buffer(List.Transform(cash, (x) => x + 1)), items = List.Select(Record.FieldValues(Source{0}), (x) => Text.Contains(x, "Items")), trx = List.TransformMany( Table.ToRows(Table.Skip(Source, 3)), (x) => List.Zip({items, fx(x, cash), fx(x, credit)}), (x, y) => List.FirstN(x, 3) & y ), result = Table.FromRows(trx, {"date", "salesman", "branch", "item", "cash", "credit"}) in result - lbendlin
The above is the format in which I received the data.Is it or is it not?
