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
ImkeF's avatar
ImkeF
Community Champion
3 years ago

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's avatar
    Sedos101
    Helper I
    3 years ago

    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's avatar
    Sedos101
    Helper I
    3 years ago

    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