Forum Discussion
Solved
Need help on M Query
Hi Team, I need your help in solving in Power query. I have 2 tables Table A and Table B. I am trying to update Table A (Result field) by searching values from Table B in Table A. If the values a...
You aren't too far off but I think I'd write it like this:
= Table.AddColumn( #"Changed Type1", "Custom", (t) => List.Last( List.Select( TableB[LookupValue], each Text.Contains(t[Test], _) ) ) ?? "Not Found", type text )The "??" part indicates what to return if the preceding expression is null.
Compute them separately and use if/then logic to return the text you want.
(t) => [ Partial = List.Last(List.Select(TableB[LookupValue], each Text.Contains(t[Test], _))), Exact = List.Last(List.Select(TableB[LookupValue], each t[Test] = _)), Text = if Exact <> null then "Exact match " & Exact else if Partial <> null then "Partial match " & Partial else "No match" ][Text]
Hi AlexisOlson,
I thought I could tweak your code to do multiple conditions but didn't work. Could you please look into this, i would like a solution similar to the one you had provided but checking the below conditions?
First Check for
1. Exact Match
else
2. Partial Match (which you have provided)
else
""
How about this?
(t) => List.Last(
List.Select(
TableB[LookupValue],
each Text.Contains(t[Test], _)
) &
List.Select(
TableB[LookupValue],
each t[Test] = _
)
) ?? ""This checks both partial and exact matches and List.Last means it will return the last exact match if one exists since I appended it after the list of partial matches.