Forum Discussion
Solved
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 ...
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.
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