Forum Discussion
Powerwoman
2 years agoHelper II
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...
- 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
dufoq3
2 years agoCommunity Champion
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
- Powerwoman2 years agoHelper II
Hi dufoq3 ,
thank you so much for your super quick response.
Is it possible to only combine the description if attachedTolineNo = lineNo?- dufoq32 years agoCommunity Champion
Hi, I've just edited code in previous post. Check it and let me know.