Forum Discussion

BB987's avatar
BB987
Frequent Visitor
4 years ago
Solved

Special group by (group until column value change)

Hi Power BI Community,

 

I would like to group my data in a specific way. 

I have the following data:

If I do a normal "Group by", I get following result:

 

But what I want is, that it is just grouped until the value in Column "Type" change. It is possible that a specific Type value comes again and again. And I need the grouped values just until there is a change but if the Type value will come later, I need this row again. So the result should look like this (DateTime column is still needed):

 

Thanks in advance.


Best regards,

Marc

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    I would buffer your table once you have your sort order as you like it. Then, you can just add the optional parameter at the end of your group function--GroupKind.Local. This scenario is exactly the purpose for which it's used.

     

    --Nate

  • serpiva64's avatar
    serpiva64
    4 years ago

    Hi, 

    the solution provided by watkinnc is perfect and without it i wouldn't be able to help you.

    Only in order to help if it is not clear i show the steps i have applied to achive the result you wanted to obtain:

    Starting from your data you have to Group By

    You obtain this line

    = Table.Group(#"Changed Type", {"Type"}, {{"Date1", each List.Min([Date]), type nullable datetime}, {"TotAmount", each List.Sum([Amount]), type nullable number}})

     

    Now you to modify it by adding GroupKind.Local

    = Table.Group(#"Grouped Rows1", {"Type"}, {{"DateMin", each List.Min([Date]), type nullable datetime}, {"TotAmount", each List.Sum([Amount]), type nullable number}},GroupKind.Local)

     

    Nothing else.

     

    If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I would buffer your table once you have your sort order as you like it. Then, you can just add the optional parameter at the end of your group function--GroupKind.Local. This scenario is exactly the purpose for which it's used.

     

    --Nate

  • serpiva64's avatar
    serpiva64
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi, 

    the solution provided by watkinnc is perfect and without it i wouldn't be able to help you.

    Only in order to help if it is not clear i show the steps i have applied to achive the result you wanted to obtain:

    Starting from your data you have to Group By

    You obtain this line

    = Table.Group(#"Changed Type", {"Type"}, {{"Date1", each List.Min([Date]), type nullable datetime}, {"TotAmount", each List.Sum([Amount]), type nullable number}})

     

    Now you to modify it by adding GroupKind.Local

    = Table.Group(#"Grouped Rows1", {"Type"}, {{"DateMin", each List.Min([Date]), type nullable datetime}, {"TotAmount", each List.Sum([Amount]), type nullable number}},GroupKind.Local)

     

    Nothing else.

     

    If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

     

  • BB987's avatar
    BB987
    Frequent Visitor
    4 years ago

    Thanks a lot to Nate and serpiva64.

    GroupKind.Local worked 🙂