Forum Discussion
Custom Column Help
I need to create a custom column that queries the rest of the data for a specific value
The data looks something like this
| ||||||||||||||||||||||||||||||
What I am trying to do is determine if John Smith returned the voicemail received from ANI 123 in the 1st row (In the example John did call 123 in the last row)
Not sure if this a good use case for a custom column or what function to use. what I would like is VoicMailReturned column to indicate a 1 IF there was an outbound call later in the data to the number in the ANI column IF VoiceMail = Yes
Thanks for any help in advance
Hi beerock ,
In addition to the PwerQueryKees‘s method, you can also try adding a custom column:
First add an Index column:Then use this M code to add a custom column:
let currentUser = [User], currentDnis = [#"DNIS(Dialed Number Identification Service)"], currentVoiceMailNo = [VoiceMail] = "No", currentIndex = [Index] in if currentVoiceMailNo then if Table.RowCount( Table.SelectRows(#"Added Index", each ([#"ANI(Automatic Number Identification)"] = currentDnis and [User] = currentUser and [Index] < currentIndex)) ) > 0 then 1 else 0 else 0And the final output is as below:
Here is the whole M code in the Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIzs0syVDSUfLMS8ovzUsBsgyNjIGkqakpkIxMLVaK1cGp1NjEFK7ULx+fSjNzC7wq/UtLYEohiiCuACmNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, #"Call Direction" = _t, #"ANI(Automatic Number Identification)" = _t, #"DNIS(Dialed Number Identification Service)" = _t, VoiceMail = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ANI(Automatic Number Identification)", Int64.Type}, {"DNIS(Dialed Number Identification Service)", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), AddedCustom = Table.AddColumn(#"Added Index", "Custom Column", each let currentUser = [User], currentDnis = [#"DNIS(Dialed Number Identification Service)"], currentVoiceMailNo = [VoiceMail] = "No", currentIndex = [Index] in if currentVoiceMailNo then if Table.RowCount( Table.SelectRows(#"Added Index", each ([#"ANI(Automatic Number Identification)"] = currentDnis and [User] = currentUser and [Index] < currentIndex)) ) > 0 then 1 else 0 else 0) in AddedCustom
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi beerock, another solution:
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIzs0syVDSUfLMS8ovzUsBsgyNjIGkqakpkIxMLVaK1cGp1NjEFK7ULx+fSjNzC7wq/UtLYEohiiCuACmNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, #"Call Direction" = _t, #"ANI(Automatic Number Identification)" = _t, #"DNIS(Dialed Number Identification Service)" = _t, VoiceMail = _t]), AddedIndexHelper = Table.AddIndexColumn(Source, "IndexHelper", 0, 1, Int64.Type), Ad_VoiceMailReturned = Table.AddColumn(AddedIndexHelper, "VoiceMailReturned", each [ a = Table.SelectRows(Table.Buffer(AddedIndexHelper), (x)=> x[Call Direction] = "Outbound" and x[#"DNIS(Dialed Number Identification Service)"] = [#"ANI(Automatic Number Identification)"] and x[IndexHelper] > [IndexHelper] and x[User] = [User]), b = if [VoiceMail] = "Yes" and not Table.IsEmpty(a) then "Yes" else "No" ][b], type text ), RemovedColumns = Table.RemoveColumns(Ad_VoiceMailReturned,{"IndexHelper"}) in RemovedColumns
7 Replies
Hi beerock, another solution:
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIzs0syVDSUfLMS8ovzUsBsgyNjIGkqakpkIxMLVaK1cGp1NjEFK7ULx+fSjNzC7wq/UtLYEohiiCuACmNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, #"Call Direction" = _t, #"ANI(Automatic Number Identification)" = _t, #"DNIS(Dialed Number Identification Service)" = _t, VoiceMail = _t]), AddedIndexHelper = Table.AddIndexColumn(Source, "IndexHelper", 0, 1, Int64.Type), Ad_VoiceMailReturned = Table.AddColumn(AddedIndexHelper, "VoiceMailReturned", each [ a = Table.SelectRows(Table.Buffer(AddedIndexHelper), (x)=> x[Call Direction] = "Outbound" and x[#"DNIS(Dialed Number Identification Service)"] = [#"ANI(Automatic Number Identification)"] and x[IndexHelper] > [IndexHelper] and x[User] = [User]), b = if [VoiceMail] = "Yes" and not Table.IsEmpty(a) then "Yes" else "No" ][b], type text ), RemovedColumns = Table.RemoveColumns(Ad_VoiceMailReturned,{"IndexHelper"}) in RemovedColumnsWould merging the table with itself matching the outbound number with the inbound number work? You can the use any table function on the merge result column to add a custom column. Table.IsEmpty could work for you....
Thanks! This worked pretty well.... the only issue Table.IsEmpty() gets me close but because the merged table column is returning multiple results due to the fact that other agents can and do call the same number.
If it has to be the same agent, merge on both agent and number. Hold ctrl when selecting the columns. A little number appears on the match columns where same numbers in both table have to match.
Hi beerock ,
In addition to the PwerQueryKees‘s method, you can also try adding a custom column:
First add an Index column:Then use this M code to add a custom column:
let currentUser = [User], currentDnis = [#"DNIS(Dialed Number Identification Service)"], currentVoiceMailNo = [VoiceMail] = "No", currentIndex = [Index] in if currentVoiceMailNo then if Table.RowCount( Table.SelectRows(#"Added Index", each ([#"ANI(Automatic Number Identification)"] = currentDnis and [User] = currentUser and [Index] < currentIndex)) ) > 0 then 1 else 0 else 0And the final output is as below:
Here is the whole M code in the Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIzs0syVDSUfLMS8ovzUsBsgyNjIGkqakpkIxMLVaK1cGp1NjEFK7ULx+fSjNzC7wq/UtLYEohiiCuACmNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, #"Call Direction" = _t, #"ANI(Automatic Number Identification)" = _t, #"DNIS(Dialed Number Identification Service)" = _t, VoiceMail = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ANI(Automatic Number Identification)", Int64.Type}, {"DNIS(Dialed Number Identification Service)", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), AddedCustom = Table.AddColumn(#"Added Index", "Custom Column", each let currentUser = [User], currentDnis = [#"DNIS(Dialed Number Identification Service)"], currentVoiceMailNo = [VoiceMail] = "No", currentIndex = [Index] in if currentVoiceMailNo then if Table.RowCount( Table.SelectRows(#"Added Index", each ([#"ANI(Automatic Number Identification)"] = currentDnis and [User] = currentUser and [Index] < currentIndex)) ) > 0 then 1 else 0 else 0) in AddedCustom
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Community,
I will appretiate your help with this issue. I have the following table with two columns Order Number and Lenght. I want to create a column called "Is it a Correct Order? " If a Order number starts with "T" , "C", "V" follow by 7 digits, and has a total lengh of 14,17,20 the answer is Yes, otherwise is No.
Thank you,
Hi YesP,
1.) you should create new topic2.) for future requests, provide sample data in usable format (not as a screenshot)
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjEyNjE1M7fQNTAwMDRSitWJVnLOz0tLLUrNS05VSEtNLQaLhRlC1IGUGRlDlEE0WoKETEzBQiFALlDQEKzKQM/AEKLQ1MzC0tzCBGyFKVAULBELAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Number" = _t]), Ad_Length = Table.AddColumn(Source, "Length", each Text.Length([Order Number]), type text), Ad_IsItACorrectOrder = Table.AddColumn(Ad_Length, "Is it a Correct Order?", each [ a = List.Contains({"T", "C", "V"}, [Order Number], (x,y)=> Text.Start(y, 1) = x), //check first letter b = try Number.From(Text.Range([Order Number], 1, 7)) is number otherwise false, //check if followed 7 characters are digits c = List.Contains({14, 17, 20}, Text.Length([Order Number])), //check length d = if List.AllTrue({a, b, c}) then "Yes" else "No" ][d], type text) in Ad_IsItACorrectOrder
