Forum Discussion

scottie2h's avatar
scottie2h
New Member
4 years ago
Solved

Display Value from list that matches another column

Hi,

I am new to Power BI and trying to query the data and effectively display the information I am after.

After the Data is extracted, I need to confirm which specific values exist in a List Column and display them so they can be counted and reported on effectively.

 

I have two tables of data

table 1 has three columns, the Ticket Number, a delimited list of tags and a List column of tags

 

Table1:

TicketNumberDelimitedDataListData
35467Alpha; Dune; Random; Dog; Blue

List

Alpha
Dune
Random
Dog
Blue
45897Lazy; Match; Gamma; Garbage

List:

Lazy
Match
Gamma
Garbage
23678Lost; Beta; Help List:
Lost
Beta
Help

 

 

Table 2 has two columns an index and a list of Released codenames

 

Table2:

IndexVersionName
1Alpha
2Beta
3Gamma
4Horizon

 

What I need to do is add a column called Version to Table1 that displays the VersionName that is in the ListData Coloumn.

 

e.g.

 

Table1:

TicketNumberDelimitedDataListDataVersion
35467Alpha; Dune; Random; Dog; Blue

List

Alpha
Dune
Random
Dog
Blue

Alpha

45897Lazy; Match; Gamma; Garbage

List:

Lazy
Match
Gamma
Garbage

Gamma

23678Lost; Beta; Help List:
Lost
Beta
Help
Beta

 

Thanks in advance for your help.

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Use the below formula in a custom column

     

    = List.Intersect({[ListData],Table2[VersionName]}){0}

    In case, second table doesn't have the required element, then you can display null by following formula

    = try List.Intersect({[ListData],Table2[VersionName]}){0} otherwise null

     

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjY1MTNX0lFyzCnISLRWcCnNS7VWCErMS8nPBfLy060VnHJKU5VidaKVTEwtLEFKfRKrKq0VfBNLkjOsFdwTc3MTQVRRUmI6RJ2RsZm5BUhdfnEJUHtqCVDeIzWnQCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TicketNumber = _t, DelimitedData = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TicketNumber", Int64.Type}, {"DelimitedData", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ListData", each Text.Split([DelimitedData],"; ")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Version", each List.Intersect({[ListData],Table2[VersionName]}){0})
in
    #"Added Custom1"

     

     

5 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    Use the below formula in a custom column

     

    = List.Intersect({[ListData],Table2[VersionName]}){0}

    In case, second table doesn't have the required element, then you can display null by following formula

    = try List.Intersect({[ListData],Table2[VersionName]}){0} otherwise null

     

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjY1MTNX0lFyzCnISLRWcCnNS7VWCErMS8nPBfLy060VnHJKU5VidaKVTEwtLEFKfRKrKq0VfBNLkjOsFdwTc3MTQVRRUmI6RJ2RsZm5BUhdfnEJUHtqCVDeIzWnQCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TicketNumber = _t, DelimitedData = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TicketNumber", Int64.Type}, {"DelimitedData", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ListData", each Text.Split([DelimitedData],"; ")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Version", each List.Intersect({[ListData],Table2[VersionName]}){0})
in
    #"Added Custom1"

     

     

    • scottie2h's avatar
      scottie2h
      New Member
      4 years ago

      Hi Vijay,

       

      Thanks for the response.

      This works great if the first entry in the list matches, but unfortunately in my data the first entry in the list isn't always the version it needs to match against

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        It will give Gamma and Beta which are not first entries. 

        May be if you can post your result which you are getting and point out the issue, that would be great. 

  • scottie2h's avatar
    scottie2h
    New Member
    4 years ago

    Hi Vijay,

     

    I got it working.  I hadnt included the leading space as part of the delimiter when i did the text.split.
    Adding the trailing white space so the delimiter was "; " instead of ";" fixed it all up.

    Thanks so much for your help

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Most Valuable Professional
      4 years ago

      Great!!! Can you mark the thread with required answer so that future visitors can benefit from this.