Display Value from list that matches another column

[scottie2h]

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:

TicketNumber	DelimitedData	ListData
35467	Alpha; Dune; Random; Dog; Blue	List Alpha Dune Random Dog Blue
45897	Lazy; Match; Gamma; Garbage	List: Lazy Match Gamma Garbage
23678	Lost; Beta; Help	List: Lost Beta Help

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

Table2:

Index	VersionName
1	Alpha
2	Beta
3	Gamma
4	Horizon

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:

TicketNumber	DelimitedData	ListData	Version
35467	Alpha; Dune; Random; Dog; Blue	List Alpha Dune Random Dog Blue	Alpha
45897	Lazy; Match; Gamma; Garbage	List: Lazy Match Gamma Garbage	Gamma
23678	Lost; Beta; Help	List: Lost Beta Help	Beta

Thanks in advance for your help.

[scottie2h]

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]

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

[Vijay_A_Verma · Accepted solution]

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]

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]

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.