Forum Discussion

JorgeLuizMS's avatar
JorgeLuizMS
Regular Visitor
4 years ago
Solved

Text.Contains with lookup

Hello guys!

I have a problem, I would like to know if someone could help me.

I have two queries in Power Query: one with calls from a telephone exchange and another with a customer record.

The field that stores the phone (in the customer table) can store one or more numbers.

I would like to make via M language a way to bring the customer who was contacted by that call, comparing strings. If the dialed number string is in the other table, it will bring the customer's name.


An example follows.

 

 

Someone has an idea how make this?

 

thank you in advance

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    4 years ago

    You can do this by 

    • Expand the delimited phone numbers into new rows
    • JOIN the two tables using a Nested LeftOuter join
    • Extract the NAME(s) from the nested table
    let

//read in the call table
    Source = Excel.CurrentWorkbook(){[Name="callTbl"]}[Content],
    calls = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Call number", Int64.Type}}),

//read in the client table
    Source2 = Excel.CurrentWorkbook(){[Name="clientTbl"]}[Content],
    clients = Table.TransformColumnTypes(Source2,{{"NAME", type text}, {"PHONE", type text}}),

//split the phones by the delimiter into ROWS
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(clients, 
        {{"PHONE", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), 
            let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "PHONE"),
    phones = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"PHONE", Int64.Type}}),

//Now join the two tables
    joined = Table.NestedJoin(calls, "Call number", phones,"PHONE","Joined",JoinKind.LeftOuter),

//Extract the matches.
//I'm using Text.Combine since you have multiple clients with the same number in your data
    #"Added Custom" = Table.AddColumn(joined, "CLIENT", each Text.Combine([Joined][NAME],",")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Joined"})


in
    #"Removed Columns"

     

     

     

4 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    4 years ago

    You can do this by 

    • Expand the delimited phone numbers into new rows
    • JOIN the two tables using a Nested LeftOuter join
    • Extract the NAME(s) from the nested table
    let

//read in the call table
    Source = Excel.CurrentWorkbook(){[Name="callTbl"]}[Content],
    calls = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Call number", Int64.Type}}),

//read in the client table
    Source2 = Excel.CurrentWorkbook(){[Name="clientTbl"]}[Content],
    clients = Table.TransformColumnTypes(Source2,{{"NAME", type text}, {"PHONE", type text}}),

//split the phones by the delimiter into ROWS
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(clients, 
        {{"PHONE", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), 
            let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "PHONE"),
    phones = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"PHONE", Int64.Type}}),

//Now join the two tables
    joined = Table.NestedJoin(calls, "Call number", phones,"PHONE","Joined",JoinKind.LeftOuter),

//Extract the matches.
//I'm using Text.Combine since you have multiple clients with the same number in your data
    #"Added Custom" = Table.AddColumn(joined, "CLIENT", each Text.Combine([Joined][NAME],",")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Joined"})


in
    #"Removed Columns"

     

     

     

  • JorgeLuizMS's avatar
    JorgeLuizMS
    Regular Visitor
    4 years ago

    Hello guys!
    Sorry for the delay, there were unforeseen events, I will test the solutions and will post here.

    Thank you very much in advance and I will mark the solution I used.

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorViVYyNjEF02bmFmDa0BjIjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Call number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Call number", type text}}),
    NumbersList = List.Buffer(Clients[Phone]),
    ClientsList = List.Buffer(Clients[Name]),
    Custom3 = #"Changed Type",
    #"Added Custom" = Table.AddColumn(Custom3, "Custom", each let 
thisnumber = [Call number],
clientnumber = List.Select(NumbersList, each Text.Contains(_, thisnumber)){0},
clientnumberposition = List.PositionOf(NumbersList, clientnumber),
clientname = try ClientsList{clientnumberposition} otherwise "n/a"
in 
clientname)
in
    #"Added Custom"

     

    Pat