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 | 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 |
The above data is my sample data with the number of certain items and cash and credit columns twice. The above is the format in which I received the data.
Is it possible to categorize the items and transform the data like below?
| Date | Salesman | Branch | Category | Cash | Credit |
| 01-10-2024 | a | X | E-Items | 20 | 30 |
| 01-10-2024 | b | S | E-Items | 16 | 24 |
| 01-10-2024 | c | A | E-Items | 19 | 12 |
| 01-10-2024 | a | X | S-Items | 43 | 30 |
| 01-10-2024 | b | S | S-Items | 43 | 21 |
| 01-10-2024 | c | A | S-Items | 20 | 14 |
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"
5 Replies
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"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 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
I copy pasted you data in excel and loaded it in PowerQuery:
Applied a number of steps (all with the UI)
Resulting in the M code
let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}}), #"Filled Up" = Table.FillUp(#"Changed Type",{"Column1", "Column2", "Column3"}), #"Transposed Table" = Table.Transpose(#"Filled Up"), #"Removed Columns" = Table.RemoveColumns(#"Transposed Table",{"Column3"}), #"Filled Down" = Table.FillDown(#"Removed Columns",{"Column1"}), #"Merged Columns" = Table.CombineColumns(#"Filled Down",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged"), #"Transposed Table1" = Table.Transpose(#"Merged Columns"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date.Date", type text}, {"Salesman Name.Salesman Name", type text}, {"Branch.Branch", type text}, {"E-Items.Item E1", Int64.Type}, {"E-Items.Item E2", Int64.Type}, {"E-Items.Item E3", Int64.Type}, {"E-Items.Item E4", Int64.Type}, {"E-Items.CASH", Int64.Type}, {"E-Items.CREDIT", Int64.Type}, {"S Items.Item S1", Int64.Type}, {"S Items.Item S2", Int64.Type}, {"S Items.Item S3", Int64.Type}, {"S Items.CASH", Int64.Type}, {"S Items.CREDIT", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Date.Date", "Date"}, {"Salesman Name.Salesman Name", "Salesman Name"}, {"Branch.Branch", "Branch"}}) in #"Renamed Columns"Producing:
