Forum Discussion
Need help on M Query
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.
Thank AlexisOlson, my apologies for not making my question clear. I would like the output to make the distinction between Exact and partial....like for e.g. "Exact match ???" or "Partial match C/O"
Thank you for taking the effort to help me out
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]You are genius ...thanks a lot...have been breaking my head for last few hours. 🙂
I have learnt a lot from you... could you please share some links where i could improve my knowledge on M query.