Forum Discussion

beerock's avatar
beerock
New Member
2 years ago
Solved

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 User Call Direction ANI(Automatic Number Identification) ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

  • dufoq3's avatar
    dufoq3
    2 years ago

    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
YesP's avatar
YesP
Icon for Helper I rankHelper I
1 year ago

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's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
1 year ago

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