Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Transform data which has multiple columns with the same name but different values.

Below is the data, which I have multiple cash and credit columns. 

DateSalesman NameBranchItem E1Item E2Item E3Item E4CASHCREDITItem S1Item S2Item S3CASHCREDIT
01-10-2024aX3026203052344330
01-10-2024bS12144162478243344321
01-10-2024cA51509191290652014

 

I want to transform the data so that I can  have only one cash and credit column obtain this:-

 

DateSalesman NameBranchItemsQuantityCashCredit
01-10-2024aXItem E132030
01-10-2024aXItem E202030
01-10-2024aXItem E322030
01-10-2024aXItem E462030
01-10-2024aXItem S154330
01-10-2024aXItem S224330
01-10-2024aXItem S3344330
01-10-2024bSItem E1121624
01-10-2024bSItem E211624
01-10-2024bSItem E341624
01-10-2024bSItem E441624
01-10-2024bSItem S1784321
01-10-2024bSItem S22434321
01-10-2024bSItem S3344321
01-10-2024cAItem E151912
01-10-2024cAItem E2151912
01-10-2024cAItem E301912
01-10-2024cAItem E491912
01-10-2024cAItem S192014
01-10-2024cAItem S202014
01-10-2024cAItem S3652014

 

 

Need Help
Tips and Tricks
  • slorin's avatar
    slorin
    1 year ago

    Hi Anonymous 

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZU8xDoMwDPwKyoxEnARaRlqQytKh6VAJMaQoEkNhaPm/6qMhUsVw9tk5n52uE7VbvEiFdS//mdycXN2E+vR28zAyaRc/JQ1FpiLTkRlm58pekG5N3d63FxvnbJyzeqfu005IykhmSiqYOcaDAalkYLhARqER8tDVkBv9a+98nvgZgyDFLSaAVjeww3Fl+s9L0d5rYFRhM+XhsBJFuW0oQ7fIt1vJiL7/Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Colonne 1" = _t, #"Colonne 2" = _t, #"Colonne 3" = _t, #"Colonne 4" = _t, #"Colonne 5" = _t, #"Colonne 6" = _t, #"Colonne 7" = _t, #"Colonne 8" = _t, #"Colonne 9" = _t, #"Colonne 10" = _t, #"Colonne 11" = _t, #"Colonne 12" = _t, #"Colonne 13" = _t, #"Colonne 14" = _t]),
    #"CASH position" = List.PositionOf(Record.ToList(Source{0}),"CASH",Occurrence.All),
    #"Branch and CREDIT position" = List.PositionOfAny(Record.ToList(Source{0}), {"Branch","CREDIT"}, Occurrence.All),
    #"Item position" = List.Transform(
       List.Zip({
         List.Transform(List.RemoveLastN(#"Branch and CREDIT position",1), each _+1),
         List.Transform(#"CASH position", each _-1)}),
       each {_{0}.._{1}}),
    ToRows = Table.ToRows(Source),
    #"First Row" = ToRows{0},
    Data = List.Skip(ToRows),
    TransformMany = List.TransformMany(
       Data,
       each #"Item position",
       (x,y) => List.FirstN(x,3) &
               {Table.FromRows(List.Transform(y, each {#"First Row"{_}, x{_}}), {"Items", "Quantity"})} &
               {x{List.Max(y)+1}} & {x{List.Max(y)+2}} ),
    FromRows = Table.FromRows(TransformMany, List.FirstN(#"First Row", 3) & {"Data"} & List.LastN(#"First Row", 2)),
    Expand = Table.ExpandTableColumn(FromRows, "Data", {"Items", "Quantity"}, {"Items", "Quantity"})
    in
    Expand

    Stephane

5 Replies

  • slorin's avatar
    slorin
    Super User
    1 year ago

    Hi Anonymous 

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZU8xDoMwDPwKyoxEnARaRlqQytKh6VAJMaQoEkNhaPm/6qMhUsVw9tk5n52uE7VbvEiFdS//mdycXN2E+vR28zAyaRc/JQ1FpiLTkRlm58pekG5N3d63FxvnbJyzeqfu005IykhmSiqYOcaDAalkYLhARqER8tDVkBv9a+98nvgZgyDFLSaAVjeww3Fl+s9L0d5rYFRhM+XhsBJFuW0oQ7fIt1vJiL7/Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Colonne 1" = _t, #"Colonne 2" = _t, #"Colonne 3" = _t, #"Colonne 4" = _t, #"Colonne 5" = _t, #"Colonne 6" = _t, #"Colonne 7" = _t, #"Colonne 8" = _t, #"Colonne 9" = _t, #"Colonne 10" = _t, #"Colonne 11" = _t, #"Colonne 12" = _t, #"Colonne 13" = _t, #"Colonne 14" = _t]),
    #"CASH position" = List.PositionOf(Record.ToList(Source{0}),"CASH",Occurrence.All),
    #"Branch and CREDIT position" = List.PositionOfAny(Record.ToList(Source{0}), {"Branch","CREDIT"}, Occurrence.All),
    #"Item position" = List.Transform(
       List.Zip({
         List.Transform(List.RemoveLastN(#"Branch and CREDIT position",1), each _+1),
         List.Transform(#"CASH position", each _-1)}),
       each {_{0}.._{1}}),
    ToRows = Table.ToRows(Source),
    #"First Row" = ToRows{0},
    Data = List.Skip(ToRows),
    TransformMany = List.TransformMany(
       Data,
       each #"Item position",
       (x,y) => List.FirstN(x,3) &
               {Table.FromRows(List.Transform(y, each {#"First Row"{_}, x{_}}), {"Items", "Quantity"})} &
               {x{List.Max(y)+1}} & {x{List.Max(y)+2}} ),
    FromRows = Table.FromRows(TransformMany, List.FirstN(#"First Row", 3) & {"Data"} & List.LastN(#"First Row", 2)),
    Expand = Table.ExpandTableColumn(FromRows, "Data", {"Items", "Quantity"}, {"Items", "Quantity"})
    in
    Expand

    Stephane

  • audreygerred's avatar
    audreygerred
    Super User
    1 year ago

    Hi! In Power Query I would make two separate tables. One, a table that has these columns:

    Date Salesman Name Branch Item E1 Item E2 Item E3 Item E4 CASH

    CREDIT

    Then, unpivot it so that you wind up with 

    Date Salesman Name Branch Item Cash Credit

     

    Next, create a table that Date Salesman Branch, your S items, cash and credit and unpivot that one as well. Make sure the field names are identical in both tables.

     

    Append table 2 to table 1. Disable load on table 2 since you do not need it showing as a separate table in Power BI.

  • slorin's avatar
    slorin
    Super User
    1 year ago

    Hi Anonymous 

    Branch --> Area 

     

    #"Branch and CREDIT position" = List.PositionOfAny(Record.ToList(Source{0}), {"Area","CREDIT"}, Occurrence.All),

     Stéphane

  • slorin's avatar
    slorin
    Super User
    1 year ago

    Anonymous 

     

    let
    Source = YourSource,
    ToRows = Table.ToRows(Source),
    First_Row = ToRows{0},
    Data = List.Skip(ToRows),
    CASH_position = List.PositionOf(First_Row,"CASH",Occurrence.All),
    Supervisor_position = List.PositionOf(First_Row,"Supervisor Name",Occurrence.First),
    CREDIT_position = List.PositionOf(First_Row, "CREDIT", Occurrence.All),
    #"Item position" = List.Transform(
      List.Zip({
        List.Transform(List.RemoveLastN({Supervisor_position} & CREDIT_position,1), each _+1),
        List.Transform(CASH_position, each _-1)}),
      each {_{0}.._{1}}),
    TransformMany = List.TransformMany(
       Data,
       each #"Item position",
       (x,y) => List.FirstN(x, Supervisor_position+1) &
          {Table.FromRows(List.Transform(y, each {First_Row{_}, x{_}}), {"Items", "Quantity"})} &
          {x{List.Max(y)+1}} & {x{List.Max(y)+2}} ),
    FromRows = Table.FromRows(TransformMany, List.FirstN(First_Row, Supervisor_position+1) & {"Data"} & List.LastN(First_Row, 2)),
    Expand = Table.ExpandTableColumn(FromRows, "Data", {"Items", "Quantity"}, {"Items", "Quantity"})
    in
    Expand

    Stéphane