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 fie...
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"

     

     

     

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