Forum Discussion
Count consecutive, non zero values in a column
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"
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.
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.
