Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Powerwoman
Helper II
Helper 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?

Powerwoman_0-1714667997966.png

 

 

 

documentNolineNoattachedTolineNodescriptiondesired outcome
RE24-010641100000Invoice 1Additional Text for Invoice 1 (1) , Additional Text for Invoice 1 (2)
RE24-0106412000010000Additional Text for Invoice 1 (1) 
RE24-0106413000010000Additional Text for Invoice 1 (2) 
RE24-010642100000Invoice 2Additional Text for Invoice 2 (1)
RE24-0106422000010000Additional Text for Invoice 2 (1) 
RE24-010642300000  

 

Thank you!

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi @Powerwoman,

 

Result:

dufoq3_0-1714678350816.png

 

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

 

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

3 REPLIES 3
dufoq3
Super User
Super User

Hi @Powerwoman,

 

Result:

dufoq3_0-1714678350816.png

 

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

 

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Hi @dufoq3 ,

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

Powerwoman_0-1714676830811.png

 

 

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors