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]
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.
I'd recommend this blog series and lots of practice solving real problems.
https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let