Count consecutive, non zero values in a column

[fanofgolfdsm]

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

[MarcelBeug · Accepted solution]

Well, you stated "anything"...

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"

[fanofgolfdsm]

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?

[MarcelBeug]

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.

[fanofgolfdsm]

MarcelBeug, I was wondering if I could ask you another question in regard to this solution. I need to add some additional grouping but am having difficulty understanding how to accomplish it.

The table I am working with contains a Region, Plant, Data Point Name, Date Time (by hour) and the values that you helped with your original code. What I need to be able to do is to be able to group these counts with the aforementioned structure. Note that Region is the top level and then it goes down in order from there (Region -> Stack -> Data Point Name, etc.). In other words I'd like to be able to see the following:

			Consecutive Hour Count
Region	Plant	Data Point Name	0-6 hrs	7-12 hrs	>12 hrs
West	Plant 1	Data Point 1	1	2	0
		Data Point 2	0	0	1
	Plant 2	Data Point 1	0	0	0
		Data Point 2	0	1	0

Any direction you can provide is appreciated. I've tried getting the data to the smallest amout possible and grouping from there, but have been unsuccessful in all my attempts.

Thanks

[MarcelBeug]

@fanofgolfdsm

It looks to me the solution would be with some visualization. I'm not too familiair with visualizations, so my suggestion would be to raise a new topic. Others will be better able to help you.

Otherwise it would be very helpful if you add some source data as well (e.g. via OneDrive or dropbox) along with the expected results.

[fanofgolfdsm]

Ok, that is what I was thinking about because the current table is rather large. I also wanted to let you know that I did verify your code worked with some of my current data. I copied a column of data I already had and used a modified form of your code to duplicate my existing results. Now I just need to adapt it to fit my needs. Thanks for all the help.

[Greg_Deckler]

I think you would need to look at EARLIER and EARLIEST for this.

[fanofgolfdsm]

Thanks smoupre. I'm looking at this and MarcelBeug's solution as well.