Forum Discussion
Count consecutive, non zero values in a column
I am looking for a way to count the consecutive number of 1's in a given column of a table. I then want a count the number of times they fall into a specific group: 1-5 consecutive 1's, 6-12 consecutive 1's, and greater than 12 consecutive 1's. I have done this in Excel using the following formula but have been unable to figure out how to do it in Power BI Desktop.
=INDEX(FREQUENCY(FREQUENCY(IF(Downtime_hrV=1,ROW(Downtime_hrV)),IF(Downtime_hrV<>1,ROW(Downtime_hrV))),Bins!A2:C2),2)
The Bins!A2:C2 cell values are:
| A2 | B2 | C2 |
| 0 | 6 | 12 |
Note that Downtime_hrV is a named range consisting of data in a single column.
I'd like to do this in a measure but am open to anything that works. Any help is appreciated.
Thanks
Well, you stated "anything"... :smileywink:
Below some rather complicated Power Query M-code that generates base data and creates the frequency distribution.
I wasn't sure about the 12-border; I put 12 in the upper group, based on your bin-values.
The trick with consecutive values is to add argument GroupKind.Local to Table.Group as indicated in the comments.
let // First some lines to generate data Source = List.Random(1000), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Downtime_hrV"}}), #"Added to Column" = Table.TransformColumns(#"Renamed Columns", {{"Downtime_hrV", each _ + 0.45, type number}}), #"Rounded Off" = Table.TransformColumns(#"Added to Column",{{"Downtime_hrV", each Number.Round(_, 0), type number}}), // Now we have base data // Group By via UI, adjusted by adding GroupKind.Local to get consecutive results #"Grouped Rows" = Table.Group(#"Rounded Off", {"Downtime_hrV"}, {{"Count", each Table.RowCount(_), type number}},GroupKind.Local), // Standard filtering on value 1 #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Downtime_hrV] = 1)), // Group By on Count via UI, code extended with null and the (x,y) function to adjust values to 1 (<6), 2 (<12) or 3 (>=12): #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"Count"}, {{"Frequency", each Table.RowCount(_), type number}}, null, (x,y) => Value.Compare(List.Count(List.FirstN({1,6,12}, each _ <= x[Count])),List.Count(List.FirstN({1,6,12}, each _ <= y[Count])))), // Add group labels #"Added Conditional Column" = Table.AddColumn(#"Grouped Rows1", "Group", each if [Count] < 6 then "1-5" else if [Count] < 12 then "6-11" else ">=12" ), // Select Group and Frequency #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Group", "Frequency"}) in #"Removed Other Columns"
8 Replies
I think you would need to look at EARLIER and EARLIEST for this.
Thanks smoupre. I'm looking at this and MarcelBeug's solution as well.
Well, you stated "anything"... :smileywink:
Below some rather complicated Power Query M-code that generates base data and creates the frequency distribution.
I wasn't sure about the 12-border; I put 12 in the upper group, based on your bin-values.
The trick with consecutive values is to add argument GroupKind.Local to Table.Group as indicated in the comments.
let // First some lines to generate data Source = List.Random(1000), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Downtime_hrV"}}), #"Added to Column" = Table.TransformColumns(#"Renamed Columns", {{"Downtime_hrV", each _ + 0.45, type number}}), #"Rounded Off" = Table.TransformColumns(#"Added to Column",{{"Downtime_hrV", each Number.Round(_, 0), type number}}), // Now we have base data // Group By via UI, adjusted by adding GroupKind.Local to get consecutive results #"Grouped Rows" = Table.Group(#"Rounded Off", {"Downtime_hrV"}, {{"Count", each Table.RowCount(_), type number}},GroupKind.Local), // Standard filtering on value 1 #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Downtime_hrV] = 1)), // Group By on Count via UI, code extended with null and the (x,y) function to adjust values to 1 (<6), 2 (<12) or 3 (>=12): #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"Count"}, {{"Frequency", each Table.RowCount(_), type number}}, null, (x,y) => Value.Compare(List.Count(List.FirstN({1,6,12}, each _ <= x[Count])),List.Count(List.FirstN({1,6,12}, each _ <= y[Count])))), // Add group labels #"Added Conditional Column" = Table.AddColumn(#"Grouped Rows1", "Group", each if [Count] < 6 then "1-5" else if [Count] < 12 then "6-11" else ">=12" ), // Select Group and Frequency #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Group", "Frequency"}) in #"Removed Other Columns"Thanks MarcelBeug. I've used your code below to see what it does and now I need to see how I can fit it into my tables. I'm still relatively new at Power BI and all this aggregation stuff is a little different. In my limted use of grouping, it seems to remove a lot of columns from the table depending on how it is done. This may present additional challenges as I have a lot of other data I need to keep. In cases like this, is it better to create a specific table for this type of need?
If you have a lot of other data, I can't paint the picture how the result should look like.
Maybe a good apporach would be to create a separate table and use only part of my code (up to and including #Filtered Rows".
Then use the result in some visual that will create the frequency distribution for you.
