cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HugoCExtA
Regular Visitor

Group by specific condition

Hello,

 

I have a table that I'd like to group by with a specific condition, the condition is : each time there is a 1 in a colum I'd like to group until there is another 1 in the same column. (Not sure it is very clear so I made a picture of what I'd like).

 

Capture.JPG

 

I'm not even sure it is possible with Power BI, but still any advice would be nice.

 

Thanks in advance.

1 ACCEPTED SOLUTION
ImkeF
Super User II
Super User II

In the query editor just add an index column first. Then multiply that with Type. Replace 0 by null and fill down values.

That will allocate the same number to each row in the group but a different number per group.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
ImkeF
Super User II
Super User II

In the query editor just add an index column first. Then multiply that with Type. Replace 0 by null and fill down values.

That will allocate the same number to each row in the group but a different number per group.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Thanks, it worked ! Did not know the fill function 🙂

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors