IF ELSE and Count

[Anthony_A]

Hello, 

I am trying to create a column that will count every time a specific ID has a value larger than 0, ELSE if the row below or above is the same ID, do not count. below is an example of the data i am working with. PLEASE HELP! 

[Anthony_A]

Its not allowing because its not supported in direct query. 

[spinfuzer · Accepted solution]

If you are relying on direct query then you may have to do this transformation in SQL instead.  Otherwise, you can switch over to import mode for this query.

There is also an alternative way of doing this that may be supported by direct query.  Let me know if you would like to see an alternative way.  It involves created an index starting from 0, then another starting from 1, a self join, and then comparing the values from the previous row after the join.

[Anthony_A]

I was able to accomplish in SQL. much easier! thank you for your help. 

[Anthony_A]

How does this work with thousands of IDs? 

[spinfuzer]

I think it should run effectively as long as you buffer the step right before generating the list.

[Anthony_A]

yes!! wow GENIUS! LET ME TRY THIS. MAY HAVE ADDITIONAL QUESTIONS. 

[spinfuzer]

something like this?  Gotta make sure you sorted by the id and the value first.

let
    Source = Table.FromColumns(
        {
            {null,null,1,2,2,3,3,3,4,4,4,5,5,5,5},
            {null,null,0,1,1,0,3,3,2,2,1,1,1,1,0}
        },
        {"id col","val column"}
    ),
    buffer = Table.Buffer(Source), // optional buffer to potentially increase speed of query
    CountList =
        List.Generate( () =>
            [
                n = 0, 
                prevVal = {null,null}, 
                currVal = {buffer[id col]{0} , buffer[val column]{0}}
            ],
        each [n] < Table.RowCount(buffer),
        each 
            [
                n = [n] + 1,
                prevVal =  
                    { 
                        buffer[id col]{[n]} , 
                        buffer[val column]{[n]}
                    },
                currVal =  
                    { 
                        buffer[id col]{[n]+1}, 
                        buffer[val column]{[n]+1} 
                    }
            ],
        each if [currVal] <> [prevVal] and [currVal]{1} > 0 then 1 else 0


        ),
    AddCountColumn = Table.FromColumns(Table.ToColumns(buffer)&{CountList}, Table.ColumnNames(buffer)&{"ct"})

in
    AddCountColumn