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...
  • ronrsnfld's avatar
    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"