Forum Discussion

xjsuarexcx's avatar
xjsuarexcx
Regular Visitor
3 years ago
Solved

counting consecutive identical values - calculated column

Dear ones, I need your support, thank you very much! I want to list (in the "Consecutive Imbalance" column) consecutive records that have a value equal to 1 (from the "Imbalance" column)...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi xjsuarexcx ,

    If you need a calculated column, here's my solution.

    Create two calculated columns.

    Flag =
MAXX (
    FILTER (
        'Query1',
        'Query1'[Timestamp] <= EARLIER ( 'Query1'[Timestamp] )
            && 'Query1'[Desbalance] = 0
    ),
    'Query1'[Timestamp]
)

    Consecutive Imbalance =
IF (
    'Query1'[Desbalance] = 1,
    RANKX (
        FILTER (
            'Query1',
            'Query1'[Desbalance] = 1
                && 'Query1'[Flag] = EARLIER ( 'Query1'[Flag] )
        ),
        'Query1'[Timestamp],
        ,
        ASC
    )
)

    Get the correct result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

jbwtp's avatar
jbwtp
Memorable Member
3 years ago

Hi xjsuarexcx,

 

Try something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc/LDcAgDATRXnzmwBryoRZE/20ERdFObnNZPXvOUJSoscqMdDVXdx279Nbpuly3a7hUPRaKYISjzgxJUMISmAbXo+XvJ7RsnuWnrQc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Items = _t, Desbalance = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Items", Int64.Type}, {"Desbalance", Int64.Type}}),
    Process = List.Accumulate(Table.ToRecords(#"Changed Type"), {}, (a, n)=> a & {if n[Desbalance] = 0 then Record.AddField(n, "Consec", 0) else Record.AddField(n, "Consec", List.Last(a)[Consec]+1)}),
    Output = Table.FromRecords(Process, Value.Type(Table.AddColumn(#"Changed Type", "Consec", each null, type number)))
in
    Output

 

Kind regards,

John