Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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...
Need Help
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    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"
PwerQueryKees's avatar
PwerQueryKees
Icon for Super User rankSuper User
1 year ago

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: