Forum Discussion

Powerwoman's avatar
Powerwoman
Helper II
2 years ago
Solved

Lookup values from another column within a group

Hi community,   is it possible to lookup a value like attachedToLineNo and display all the corresponding description in one field?       documentNo lineNo attachedTolineNo descr...
  • dufoq3's avatar
    2 years ago

    Hi Powerwoman,

     

    Result:

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnI1MtE1MDQwMzFU0lEyNAACIA3Cnnll+ZnJqQqGSrE66OqMoOpg6h1TUjJLMvPzEnMUQlIrShTS8osU4PoVNAw1sZhhTJoZRuhmGOFwrxEWdaS41wiLe42Q3AvCCkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [documentNo = _t, lineNo = _t, attachedTolineNo = _t, description = _t]),
        // You can delete this step if you had blank values as null already.
        ReplaceBlanToNull = Table.TransformColumns(Source, {}, each if Text.Trim(_) = "" then null else _),
        GroupedRows = Table.Group(ReplaceBlanToNull, {"documentNo"}, {{"All", each 
            [ a = Table.NestedJoin(_, {"lineNo"}, _, {"attachedTolineNo"}, "Join"),
              b = Table.AddColumn(a, "Description Text", (x)=> if Table.RowCount(x[Join]) = 0 then null else Text.Combine(x[Join][description], ", "), type text),
              c = Table.RemoveColumns(b, {"Join"})
            ][c], type table}}),
        CombinedAll = Table.Combine(GroupedRows[All])
    in
        CombinedAll