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...
  • lbendlin's avatar
    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"