Forum Discussion
Display Value from list that matches another column
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 nullSee 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"
Most Valuable ProfessionalUse 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"
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.