Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
You can do this by
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"
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.
You can do this by
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"
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.