Forum Discussion

Sedos101's avatar
Sedos101
Helper I
3 years ago
Solved

Grouping

Hi Everyone   I am trying to group a set of rows based on reocurring row values.   Essentially I have a Haul Truck that I want to define each cycle it does from its haul cycle state. The start...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

Sedos101 So given the sample data, what is the desired result and what is the logic behind said desired result?

Sedos101's avatar
Sedos101
Helper I
3 years ago

Hi Greg Greg_Deckler 

 

Basically below:

 

 

The number of rows for each state may be different, however from from loaded event to the next while going through the other cycle states will be unique cycles as seen above

 

any help would be greatly appreciated

 

thanks heaps

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Sedos101 OK, better sample data posted as text would be hugely beneficial. I am assuming that each of those rows has a truck identifier and a date, correct? And, if my understanding is correct, you want a Cycle Number column that, for each truck, counts the number of times that a truck goes from Loading all the way through to "Stopped Empty". So, once through that cycle is 1, the next "Loading" after "Stopped Empty" is 2, etc.

     

    It sounds very Cthulhu'esque but obviously that's not Power Query. Basically a repeating counter with conditions.

    Cthulhu - Microsoft Power BI Community

    • Sedos101's avatar
      Sedos101
      Helper I
      3 years ago

      Hi Greg_Deckler 

       

      Yes that is what I am trying to do 🙂

       

      Below is text as requested.

       

      Thanks for your help

       

      Cycle StateCycle Number
        
      Fully Loaded1
      Fully Loaded1
      Fully Loaded1
      Stopped Loaded1
      Stopped Loaded1
      Dumping1
      Dumping1
      Dumping1
      Traveling Empty1
      Traveling Empty1
      Stopped Empty1
      Stopped Empty1
      Stopped Empty1

      Loading

      		2
      Fully Loaded2
      Traveling Loaded2
      Stopped Loaded2
      Dumping2
      Traveling Empty2
      Stopped Empty2
      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        Sedos101 Well, here is a DAX solution, not Power Query. Do you really need it in Power Query? PBIX is attached below signature. It's based on Cthulhu. Cthulhu - Microsoft Power BI Community

        Column = 
    VAR __index = [Index]
    VAR __truck = [Truck ID]
    VAR __cyclestate = [Cycle State]
    VAR __Table1 = FILTER('Table',[Truck ID] = __truck && [Index] < __index && ( [Cycle State] = "Stopped Empty" || [Cycle State] = "Loading"))
    VAR __Table2 = 
        ADDCOLUMNS(
            __Table1,
            "__diff",
            IF(
                [Cycle State] = "Stopped Empty",
                0,
                [Index] - MAXX(FILTER('Table',[Index]<EARLIER('Table'[Index]) && [Truck ID] = EARLIER([Truck ID]) && [Cycle State]="Stopped Empty"),[Index])
            )
        )
    VAR __CycleNumber = COUNTROWS(FILTER(__Table2,[__diff] = 1)) + 1
RETURN
    __CycleNumber