Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello -
This situation seems so simple, but I'm struggling to get it to work.
I have two seperate tables and need to search a column in Table 1 using a list of values from a column in Table 2.
Example:
In Table1 I have a column called "Tags" with the following values listed in each.
Table1[Tags]
Tags |
condition-4; condition-4.2; condition-3 |
condition-9; condition-5; condition-10 |
condition-8; condition-7; condition-6 |
I'm trying to create a calculated column that searches this field using a column in Table2 like this.
Table2[Condition_Name]
Condition_Name |
condition-10 |
condition-9.1 |
condition-9 |
condition-8 |
condition-7 |
condition-6 |
condition-5 |
condition-4.2 |
condition-4.1 |
condition-4 |
What I need the calculated column to do is search Table1 using the values in Table2 and output the first instance starting from Top to bottom.
Expected output for the new calculated column in Table1
Tags | New Calculated Column |
condition-4; condition-4.2; condition-3 | condition-4.2 |
condition-9; condition-5; condition-10 | condition-10 |
condition-8; condition-7; condition-6 | condition-8 |
I hope this makes sense and would be very greatful if someone can help!
Thanks,
Rich
Solved! Go to Solution.
Try this solution.
1. Create an Index column in Power Query for Table2 (Condition_Name).
2. Create calculated column in Table1 (Tags).
New Calculated Column =
VAR vTag = Tags[Tags]
// get the minimum index (multiple matches may occur)
VAR vMinIndex =
MINX (
FILTER (
Condition_Name,
CONTAINSSTRING ( vTag, Condition_Name[Condition_Name] )
),
Condition_Name[Index]
)
VAR vResult =
// get the condition name corresponding to the minimum index
MAXX (
FILTER ( Condition_Name, Condition_Name[Index] = vMinIndex ),
Condition_Name[Condition_Name]
)
RETURN
vResult
Proud to be a Super User!
Try this solution.
1. Create an Index column in Power Query for Table2 (Condition_Name).
2. Create calculated column in Table1 (Tags).
New Calculated Column =
VAR vTag = Tags[Tags]
// get the minimum index (multiple matches may occur)
VAR vMinIndex =
MINX (
FILTER (
Condition_Name,
CONTAINSSTRING ( vTag, Condition_Name[Condition_Name] )
),
Condition_Name[Index]
)
VAR vResult =
// get the condition name corresponding to the minimum index
MAXX (
FILTER ( Condition_Name, Condition_Name[Index] = vMinIndex ),
Condition_Name[Condition_Name]
)
RETURN
vResult
Proud to be a Super User!
Thanks for the quick reply! This worked perfectly!
and output the first instance starting from Top to bottom.
Power BI has no concept of that. You need to indicate what you mean by "top" or "bottom". Usually by adding an index column
Your request doesn't seem to depend on user input, so instead of doing it as a calculated column you could also decide to do it in Power Query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7PS8ksyczP0zWxVkDi6Bkhc42VYnWQ1VoiS5oicwwN0JRaIMuaI3PMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tags = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom Column", each List.Intersect({Text.Split([Tags],"; "),#"Condition_Name"[Condition_Name]})),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", (k)=> List.First(Table.Sort(Table.SelectRows(#"Condition_Name",each List.Contains(k[Custom Column],[Condition_Name])),"Index")[Condition_Name])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom Column"})
in
#"Removed Columns"
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
12 | |
10 | |
10 | |
6 |