Forum Discussion

Kritiga's avatar
Kritiga
Regular Visitor
6 years ago
Solved

Grouping based on values in a column

Hi Everyone,

 

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

 

IDDateXResult
121-12-202001
121-12-202001
121-12-202022
121-12-202003
121-12-202003
121-12-202003
221-12-202031
221-12-202002
221-12-202002

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

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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.

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Kritiga
      Regular Visitor
      6 years ago

      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.

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    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.