Forum Discussion
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 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
23 Replies
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.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
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:
- Loading
- Fully Loaded
- Stopped Loaded
- Traveling Loaded
- Dumping
- Traveling Empty
- 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
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 So given the sample data, what is the desired result and what is the logic behind said desired result?
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
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.
Greg_Deckler sorry forgot @ you in previous comment!
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
Hi Greg_Deckler
Thanks again for your help, would you happen to know how I could get around the Memory Issue error?
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
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"
