none
Item Name and cell reference should be in same order dynamically. RRS feed

  • Question

  • Table 1
    Cell Ref Item Qty Year
    B6 a 15 2018
    B7 b 16 2018
    B8 d 12 2018
    B9 e 17 2018
    B10 f 19 2018
    B11 g 1 2018
     

    Table 2
    Cell Ref Item Qty Year
    B6 a 139 2019
    B7 c 60 2019
    B8 d 164 2019
    B9 e 161 2019
    B10 f 60 2019
    B11 g 115 2019
    B12 h 2 2019
    B13 i 3 2019


    i want to separate sheets with identical item name with same cell reference in same order.  If item is missing then item name should be in same cell reference with blank qty in any of two tables mentioned above. 

    For Example.

    Desired Table 1
    Cell Ref Item Qty Year
    B6 a 15 2018
    B7 b 16 2018
    B8 c
    2018 

    B9       d         12  2018

    Desired Table 2 

    Cell Ref Item Qty Year
    B6 a 139 2019
    B7 b 2019

    B8        c          60 2019




    Hayder Alee

    Wednesday, December 11, 2019 6:48 PM

Answers

  • Hi Hayder,

    For Table1 (for Table2 you should just swap table names in code):

    let
        fn = (tbl, optional st)=> Table.AddIndexColumn(tbl, "i", st, 1),
        ref = let a = Table1{0}[Cell Ref] in [col = Text.Start(a, 1), row = Number.From(Text.End(a, 1))],
        Source = Table.NestedJoin(fn(Table1), "Item", fn(Table2), "Item", "t", JoinKind.RightAnti),
        transform = Table.TransformColumns(Source{0}[t], {"Qty", each null}),
        append = Table.Combine({transform, fn(Table1)}),
        sort = Table.Sort(append, List.Transform({"i","Year"}, each {_, 0})),
        del = Table.RemoveColumns(sort,{"Cell Ref", "i"}),
        transform1 = Table.TransformColumns(fn(del, ref[row]), {"i", each ref[col] & Text.From(_)})
    in
        transform1


    Note, items b and c may be in another order. For english letters it may be solved, but for real-world data additional information about proper order of items is needed.

    Friday, December 13, 2019 11:49 AM

All replies

  • Hi Hayder,

    For Table1 (for Table2 you should just swap table names in code):

    let
        fn = (tbl, optional st)=> Table.AddIndexColumn(tbl, "i", st, 1),
        ref = let a = Table1{0}[Cell Ref] in [col = Text.Start(a, 1), row = Number.From(Text.End(a, 1))],
        Source = Table.NestedJoin(fn(Table1), "Item", fn(Table2), "Item", "t", JoinKind.RightAnti),
        transform = Table.TransformColumns(Source{0}[t], {"Qty", each null}),
        append = Table.Combine({transform, fn(Table1)}),
        sort = Table.Sort(append, List.Transform({"i","Year"}, each {_, 0})),
        del = Table.RemoveColumns(sort,{"Cell Ref", "i"}),
        transform1 = Table.TransformColumns(fn(del, ref[row]), {"i", each ref[col] & Text.From(_)})
    in
        transform1


    Note, items b and c may be in another order. For english letters it may be solved, but for real-world data additional information about proper order of items is needed.

    Friday, December 13, 2019 11:49 AM
  • Hi Aleksei,

    Many thanks for providing solution. 


    Hayder Alee

    Sunday, December 15, 2019 5:55 PM