Forum Discussion
Anonymous
1 year agoNot applicable
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...
- 1 year ago
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"
Anonymous
1 year agoNot applicable
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 |
lbendlin
Super User
1 year agoThe above is the format in which I received the data.
Is it or is it not?