Forum Discussion

u4us1923's avatar
u4us1923
Frequent Visitor
2 years ago
Solved

it's a simple question.

Hello.. 

 

I have a table such as below. I want to subtract the failed call from the total call and put the result in a new row (success_call).

Can anyone who has information about this subject help me?

 

Call TypeCount 
total_call10000 
Failed_call100 
Success_Call9900 new rows
  • Hi u4us1923, you can do this easily in Excel but here you go:

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKskvScyJT07MyVHSUTI0AAKlWJ1oJbfEzJzUFCRxpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Call Type" = _t, Count = _t]),
        ChangedType = Table.TransformColumnTypes(Source,{{"Count", type number}}),
        Ad_SuccessCall_Row = Table.InsertRows(ChangedType, 2, {
            [ Call Type = "Success_Call", 
              Count = [ t = Table.SelectRows(ChangedType, (x)=> Text.Contains(x[Call Type], "total", Comparer.OrdinalIgnoreCase))[Count]{0}?,
                        f = Table.SelectRows(ChangedType, (x)=> Text.Contains(x[Call Type], "failed", Comparer.OrdinalIgnoreCase))[Count]{0}?,
                        result = t-f
                      ][result]
            ] } )
    in
        Ad_SuccessCall_Row

2 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion

    Hi u4us1923, you can do this easily in Excel but here you go:

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKskvScyJT07MyVHSUTI0AAKlWJ1oJbfEzJzUFCRxpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Call Type" = _t, Count = _t]),
        ChangedType = Table.TransformColumnTypes(Source,{{"Count", type number}}),
        Ad_SuccessCall_Row = Table.InsertRows(ChangedType, 2, {
            [ Call Type = "Success_Call", 
              Count = [ t = Table.SelectRows(ChangedType, (x)=> Text.Contains(x[Call Type], "total", Comparer.OrdinalIgnoreCase))[Count]{0}?,
                        f = Table.SelectRows(ChangedType, (x)=> Text.Contains(x[Call Type], "failed", Comparer.OrdinalIgnoreCase))[Count]{0}?,
                        result = t-f
                      ][result]
            ] } )
    in
        Ad_SuccessCall_Row
  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi u4us1923 

    Did the solution dufoq3  provided help you solve the problem, if it helps, please consider to mark it as a solution or you can offer more information.

     

    Best Regards!

    Yolo Zhu