Forum Discussion
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...
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"HI ImkeF
Unfortunately it didnt work, I kept getting and ERROR when I expanded for the Column I defined by your code.
