Grouping based on values in a column

[Kritiga]

Hi Everyone,

Please see the problem I am trying to solve below, with the 'Result' being the expected result.

ID	Date	X	Result
1	21-12-2020	0	1
1	21-12-2020	0	1
1	21-12-2020	2	2
1	21-12-2020	0	3
1	21-12-2020	0	3
1	21-12-2020	0	3
2	21-12-2020	3	1
2	21-12-2020	0	2
2	21-12-2020	0	2

For every ID the counter has to be reset & continuous 0's segmented as one group and non 0's as a group too.

Any help will be highly appreciated.

Thanks,

Kritiga

[v-frfei-msft · Accepted solution]

Hi @Kritiga ,

I have created a sample for your reference, please check the following steps as below.

1. Insert an index column by ID in power query as below.

M code for your reference, also you can check the article.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIy1DU00jUyMDIAckDYUClWhzQpIzDGqcuYAikjdCljuDMwpAzgzsAlFQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, X = _t, Result = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type text}, {"X", Int64.Type}, {"Result", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Rows", each _, type table [ID=number, Date=text, X=number, Result=number]}}),
    Indexed = Table.TransformColumns(#"Grouped Rows", {{"Rows", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
    #"Expanded Rows" = Table.ExpandTableColumn(Indexed, "Rows", {"Date", "X", "Result", "GroupIndex"}, {"Rows.Date", "Rows.X", "Rows.Result", "Rows.GroupIndex"})
in
    #"Expanded Rows"

 2. After that, create a calculated column like that to get it.

REsult_ = 
VAR k =
    ADDCOLUMNS (
        'Table',
        "a",
        VAR ind = 'Table'[Rows.GroupIndex]
        VAR im1 = ind - 1
        VAR rm1 =
            CALCULATE (
                MAX ( 'Table'[Rows.X] ),
                FILTER (
                    'Table',
                    'Table'[Rows.GroupIndex] = im1
                        && 'Table'[ID] = EARLIER ( 'Table'[ID] )
                )
            )
        VAR r = 'Table'[Rows.X]
        RETURN
            IF ( ISBLANK ( rm1 ) || rm1 <> r, 1, 0 )
    )
RETURN
    SUMX (
        FILTER (
            k,
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && 'Table'[Rows.GroupIndex] <= EARLIER ( 'Table'[Rows.GroupIndex] )
        ),
        [a]
    )

For more details, please check the pbix as attached.

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks.
Proud to be a Datanaut My Recent Blog -Time Intelligence - Direct Query

[Kritiga]

Columns ID, Date & X are the columns I have. Column Result is what I wish to get.

There are several datetime for one ID with corresponding X say quantity. Now for continuous occurrence of 0's & Non zero's need to be grouped, meaning for ID 1 if there are 3 rows continuously with 0, label it as 1. The moment we encounter a non zero it is next label, but until another 0 is encountered. So if I have 23,23,23 as values of next 3 rows the 'Result' should be 2 for all the 3 rows.

This is applied for each ID. So for ID=2, the label should start with 1 again.