Custom Column Help

[beerock]

I need to create a custom column that queries the rest of the data for a specific value

The data looks something like this

User Call Direction ANI(Automatic Number Identification) DNIS(Dialed Number Identification Service) VoiceMail VoicMailReturned John Smith Inbound 123 555 Yes   John Smith Inbound 345 555 No   John Smith Inbound 678 555 No   John Smith Outbound 555 123 No

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

[Anonymous · Accepted solution]

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 0

And 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.

[YesP]

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,

[dufoq3]

Hi @YesP, 
1.) you should create new topic

2.) 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

[dufoq3 · Accepted solution]

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

[Anonymous · Accepted solution]

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 0

And 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.

[PwerQueryKees]

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.

[PwerQueryKees]

Would 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....

[beerock]

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.