Forum Discussion
JorgeLuizMS
4 years agoRegular Visitor
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...
- 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"
Anonymous
4 years agoNot applicable