Forum Discussion
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 | 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!
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
3 Replies
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 CombinedAllHi dufoq3 ,
thank you so much for your super quick response.
Is it possible to only combine the description if attachedTolineNo = lineNo?Hi, I've just edited code in previous post. Check it and let me know.
