Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to 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.
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.
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.
yes!! wow GENIUS! LET ME TRY THIS. MAY HAVE ADDITIONAL QUESTIONS.
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
User | Count |
---|---|
20 | |
10 | |
10 | |
9 | |
7 |