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]
let
//read in lookup table and create a list
SourceLookup = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
lookup = Table.TransformColumnTypes(SourceLookup, {"LookupValue", type text})[LookupValue],
//read in Table A
Source = Excel.CurrentWorkbook(){[Name="Table15"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", type text}}),
//see if there is a match
#"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each
let
matchPos =List.PositionOf(
List.Transform(lookup, (ss)=>
Text.Contains([Test],ss)),true,Occurrence.Last)
in
if matchPos = -1 then "Not Found" else lookup{matchPos})
in
#"Added Custom"
Thank you very much for your help.