Forum Discussion

anvikuttu's avatar
anvikuttu
Advocate I
4 years ago
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 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

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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's avatar
    AlexisOlson
    4 years ago

    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]

     

10 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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's avatar
      anvikuttu
      Advocate I
      4 years ago

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

    • anvikuttu's avatar
      anvikuttu
      Advocate I
      4 years ago

      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's avatar
        AlexisOlson
        Super User
        4 years ago

        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.

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    4 years ago
    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"