Grouping

[Sedos101]

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 of the first cycle (cycle number 1) is Loading and the start of the next cycle (cycle number 2) is the next Loading event and so on and so on.

There are a total of 7 events in each cycle and they are sequentially ordered as follows:

Loading=1

Fully Loaded =2

Traveling Loaded = 3

Stopped Loaded = 4

Dumping = 5

Traveling Empty = 6

Stopped Empty = 7

Can someone please help me with the M Query on how to do this?

Thanks in advance

[ImkeF · Accepted solution]

Hi @Sedos101 ,
please check the solution in the file attached.

[Sedos101]

I LOVE YOU!! THANK YOU SOOO MUCH!! @ImkeF 

[ImkeF]

Hi @Sedos101 ,

if my understanding is correct, this should work:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Truck ID", Int64.Type}, {"Cycle State", type text}, {"Cycle", type text}}),
    Custom1 = Table.Buffer( #"Changed Type" ),
    #"Added Index" = Table.AddIndexColumn(Custom1, "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Truck ID", "Cycle State"}, {{"All", each _}}, 0, (x, y)=>  Number.From(x[Cycle State]<> y[Cycle State]) ),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Truck ID", "Cycle State"}, {{"All", each _}},0,(x,y)=> Number.From(y[Cycle State] = "Loading")),
    #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows1", "Index", 1, 1, Int64.Type),
    #"Expanded All" = Table.ExpandTableColumn(#"Added Index1", "All", {"All"}, {"All.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded All",{"Cycle State"}),
    #"Expanded All.1" = Table.ExpandTableColumn(#"Removed Columns", "All.1", {"Cycle", "Cycle State", "Index"}, {"Cycle", "Cycle State", "Index.1"})
in
    #"Expanded All.1"

[Sedos101]

HI @ImkeF 

Unfortunately it didnt work, I kept getting and ERROR when I expanded for the Column I defined by your code.

[Sedos101]

@ImkeF  my apoligies, the 3rd column "Cycle" in the latest Excel sheet I attached is just a column I added so you could reference when the cycle needs to start and end, the first two columns are the only ones I have.

[ImkeF]

Hi @Sedos101 ,
you can try this Power Query solution:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45W8slPTMnMS1eK1YlWcivNyalUAImkphApEFySX1CQmkJAyKU0twBmCS52SFFiWWoOkKfgmltQUolTDGY8aSJ4vYmwhnhvEOG0WAA=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"Cycle State" = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Cycle State", type text}}), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type", 
    {"Cycle State"}, 
    {{"Cycle", each _, type table [Cycle State = nullable text]}}, 
    0, 
    (x, y) => Number.From(y[Cycle State] = "Loading") // this is where the magic happens
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows", {"Cycle State"}), 
  #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type), 
  #"Expanded Cycle" = Table.ExpandTableColumn(
    #"Added Index", 
    "Cycle", 
    {"Cycle State"}, 
    {"Cycle State.1"}
  )
in
  #"Expanded Cycle"

It is based on using the 5th parameter of the Table.Group-function like described here:
Table.Group: Exploring the 5th element in Power BI and Power Query – The BIccountant
It will create a new group everytime "Loading" occurs. Then you can add an index column for the sequence-numbers before expanding the original data.

[Sedos101]

Hi @Greg_Deckler @ImkeF 

Please see link to download CSV example

https://1drv.ms/u/s!AmQ0-fnxPpEChh-3s3DR9i_WcWqS?e=Z1jauJ

I Apologise as I realised my explanation has not been very good. Please see below hope it explains my dillemma better.

I have a table in Power BI which contains a column called “Cycle State”. There are basically 7 states of a cycle which are sequentially as follows:

1. Loading
2. Fully Loaded
3. Stopped Loaded
4. Traveling Loaded
5. Dumping
6. Traveling Empty
7. Stopped Empty

The data I am looking at is second by second data log files coming of the trucks. So for every second it records what state of the cycle it is in. Each complete cycle (From Loading to Stopped Empty) varies in the number of rows as it depends on how long it took to complete each cycle state.  For example a complete cycle could look like:

Loading

Fully Loaded

Stopped Loaded

Traveling Loaded

Dumping

Traveling Empty

Stopped Empty

Or

Loading

Loading

Loading

Fully Loaded

Stopped Loaded

Traveling Loaded

Dumping

Dumping

Dumping

Traveling Empty

Traveling Empty

Stopped Empty

Is there a way I can basically group each one of these cycles and name them with a number that increases sequentially, for example the first example would be cycle 1 and the second example could be cycle 2? (Basically the new cycle will start once Loading has recommenced after Stopped Empty has occurred)

Thanks for your help

[Sedos101]

Hi @Greg_Deckler @ImkeF 

Please use this data set instead, I have labelled 3 cycles from the start to the end.

https://1drv.ms/x/s!AmQ0-fnxPpEChiGMWl4UyDwsZHhJ?e=B0cMJw

Thanks again!

[Sedos101]

Hi @ImkeF 

Thank you very much for your help!

I Just have a follow on question

What do you mean by adding an index colum for the sequence-number before expanding?

I am not sure exactly how your code works. The amound of rows for each cycle status will differ for each cycle.

Will this code be able to distinguish that and be able to then group those cycles and call it cycle 1?

Thanks again

[Sedos101]

Hi @Greg_Deckler 

Thanks heaps, however I am running into "memory issues with the DAX option"

Is there a way to do it in Power Query?

Thanks

[Sedos101]

HI @Greg_Deckler 

Thanks very much for your help, I am getting a "There's not enough memory to complete this operation"

I tried increasing my memory settings in Power BI but to no luck.

Is there a way to do it in Power Query instead of a DAX measure?

Thanks again

[Greg_Deckler]

@Sedos101 I personally have no earthly clue regarding how to implement a Cthulhu-esque calculation in Power Query. But, hopefully @ImkeF or @edhans do.

[Sedos101]

Hi @Greg_Deckler 

Thanks again for your help, would you happen to know how I could get around the Memory Issue error?

[Sedos101]

Hi @Greg_Deckler 

I got it to work, by reducing my data

However it does not seem to work if there is more that one loading row in a cycle? 

It seems to just start back at number 1 for the next cycle...

Thanks

[Greg_Deckler]

@Sedos101 Yeah, tough problem to solve, the data is uh...uh...uh...ugly. Shoot me some sample data that replicates that specific issue and I can see what I can come up with. But, additional calculations are probably going to slow things down even more.

[Sedos101]

@Greg_Deckler 

How do I upload a CSV, wont allow me ?

[Greg_Deckler]

@Sedos101 You can share it on OneDrive or Box or Google Drive. Or use @ImkeF 's solution.

[Sedos101]

@Greg_Deckler sorry forgot @ you in previous comment!

[Greg_Deckler]

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

[Sedos101]

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