Lookup values from another column within a group

[Powerwoman]

Hi community,

is it possible to lookup a value like attachedToLineNo and display all the corresponding description in one field?

documentNo	lineNo	attachedTolineNo	description	desired outcome
RE24-010641	10000	0	Invoice 1	Additional Text for Invoice 1 (1) , Additional Text for Invoice 1 (2)
RE24-010641	20000	10000	Additional Text for Invoice 1 (1)
RE24-010641	30000	10000	Additional Text for Invoice 1 (2)
RE24-010642	10000	0	Invoice 2	Additional Text for Invoice 2 (1)
RE24-010642	20000	10000	Additional Text for Invoice 2 (1)
RE24-010642	30000	0

Thank you!

[dufoq3 · Accepted solution]

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

[Powerwoman]

Hi @dufoq3 ,

thank you so much for your super quick response.
Is it possible to only combine the description if attachedTolineNo = lineNo?

[dufoq3]

Hi, I've just edited code in previous post. Check it and let me know.