Forum Discussion
IF ELSE and Count
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!
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.
7 Replies
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 AddCountColumnyes!! wow GENIUS! LET ME TRY THIS. MAY HAVE ADDITIONAL QUESTIONS.
How does this work with thousands of IDs?
I think it should run effectively as long as you buffer the step right before generating the list.
Its not allowing because its not supported in direct query.
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.
I was able to accomplish in SQL. much easier! thank you for your help.
