Need help on M Query

[anvikuttu]

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 are found then I want the lookup value to be updated in the Result field of Table A.

My code didn't work, as the output is either producing a list. I am unable to get the iterative value.

Table.AddColumn(#"Changed Type1", "Custom", each if Table.Contains(#"Rejection_Codes",[LookupValue= [Test]]) then Rejection_Codes[LookupValue]{0} else [Test])

Thanks in advance

[AlexisOlson · Accepted solution]

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.

[AlexisOlson · Accepted solution]

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.

[anvikuttu]

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

""

[AlexisOlson]

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.

[anvikuttu]

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 

[AlexisOlson · Accepted solution]

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]

[anvikuttu]

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.

[AlexisOlson]

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-expressi...

[anvikuttu]

Wow!!...simple and elegant....Thank you for your help!

[ronrsnfld]

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"

[anvikuttu]

Thank you very much for your help.