Create a group number that advances when value is found

[amulder]

I have the following data:

Event ID	Vehicle	Status Code
P16039954	1102	DP
P16039954	1102	AM
P16039954	1102	DP
P16039954	1102	AR
P16039954	1102	AM
P16039954	1301	DP
P16039954	1301	AM
P16039955	1299	DP
P16039956	1299	DP
P16039956	1301	DP

<tbody>
</tbody>

I would like to create a new value called Group Number that advances by 1 every time the Status Code of DP is encountered. The result should appear like the following:

Event ID	Vehicle	Status Code	Group Number
P16039954	1102	DP	1
P16039954	1102	AM	1
P16039954	1102	DP	2
P16039954	1102	AR	2
P16039954	1102	AM	2
P16039954	1301	DP	3
P16039954	1301	AM	3
P16039955	1299	DP	4
P16039956	1299	DP	5
P16039956	1301	DP	6

<tbody>
</tbody>

I currently have the data within PowerPivot but could also do the calculation in Excel.

Thanks in advance for your help.

 

[Matt Allington]

Power Pivot doesn't have a concept of "row order". All the data is loaded and processed in any order. The issue therefore in creating this column is i don't see any way how to determine the order before the new column is calculated. I would therefore do this either in the source, in Excel or maybe Power Query

 

[MarcelBeug]

In Excel:

=COUNTIF(C$2:C2,"DP")

The following Power Query code was created this way

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event ID", type text}, {"Vehicle", Int64.Type}, {"Status Code", type text}, {"Group Number Excel", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Calculated Count" = List.NonNullCount(#"Added Index"[Index]),
    Group = List.Buffer(
                        List.Generate(() => [Counter = 0, Group = if #"Added Index"[#"Status Code"]{0} = "DP" then 1 else 0],
                                each [Counter] < #"Calculated Count", 
                                each [Counter = [Counter] + 1, Group = if #"Added Index"[#"Status Code"]{[Counter]} = "DP" then [Group] + 1 else [Group]], 
                                each [Group])
                        ),    
    #"Added Custom" = Table.AddColumn(#"Added Index", "Group Number PQ", each Group{[Index]}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

 

[MarcelBeug]

Small correction in the Power Query Code (+1 added, see bold red):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event ID", type text}, {"Vehicle", Int64.Type}, {"Status Code", type text}, {"Group Number Excel", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Calculated Count" = List.NonNullCount(#"Added Index"[Index]),
    Group = List.Buffer(
                        List.Generate(() => [Counter = 0, Group = if #"Added Index"[#"Status Code"]{0} = "DP" then 1 else 0],
                                each [Counter] < #"Calculated Count", 
                                each [Counter = [Counter] + 1, Group = if #"Added Index"[#"Status Code"]{[Counter] [COLOR=#FF0000][B]+ 1[/B][/COLOR]}[COLOR=#FF0000][/COLOR] = "DP" then [Group] + 1 else [Group]], 
                                each [Group])
                        ),    
    #"Added Custom" = Table.AddColumn(#"Added Index", "Group Number PQ", each Group{[Index]}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

 

[MarcelBeug]

In Excel:

=COUNTIF(C$2:C2,"DP")

This code may cause an issue when adding rows to the table, because the range reference (C$2:Cn) will be automatically adjusted for the previous last row.

In order to avoid that issue, you can use the following in D2 and copy down

=SUM([@[Status Code]]="DP",OFFSET(D2,-1,0))

 

[billszysz]

Hi MarcelBeug )
Think it is a simpler way

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event ID", type text}, {"Vehicle", Int64.Type}, {"Status Code", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Status Code"}, {{"tbl", each Table.AddIndexColumn(_, "Group Number", 1, 1), type table}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Status Code"}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Removed Columns", "tbl", {"Event ID", "Group Number", "Index", "Status Code", "Vehicle"}, {"Event ID", "Group Number", "Index", "Status Code", "Vehicle"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded {0}",{"Event ID", "Vehicle", "Status Code", "Group Number", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns1"

Regards

 

[MarcelBeug]

Hi Bill,

Well appreciated. As a matter of fact I'm studying Microsoft's PQFL (M) Reference thoroughly from top to bottom and coincidentally today I was about to cover " Table.Group" which is an important part of your solution. So I will examine your solution more thoroughly as well. I already studied "List.Generate" which I regard as a very powerful function and quite suitable for this topic.

Otherwise please note that your solution always starts with group number 1, even if the first row doesn't have Status Code DP. In such a case, my solution would start with group number 0.
I guess it's up to the topic starter to judge on which one is correct.

Regards,
Marcel

 

[billszysz]

Hi Marcel,
Usually, there is more than one way to skin a cat in PQ
So, for this case i can also imagine something like these below

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event ID", type text}, {"Vehicle", Int64.Type}, {"Status Code", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Removed Duplicates" = Table.Distinct(#"Added Index", {"Status Code"})[[Status Code]],
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", (x) => Table.AddIndexColumn(Table.SelectRows(#"Added Index", each [Status Code] = x[Status Code]), "Group Number", 1, 1)),
    CombineTables = Table.Combine( #"Added Custom"[Custom]),
    #"Sorted Rows" = Table.Sort(CombineTables,{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

or

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event ID", type text}, {"Vehicle", Int64.Type}, {"Status Code", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Indeks", 1, 1),
    HandWritten = Table.AddColumn(#"Added Index", "Group Number",(x) => List.Count(List.Select(List.Range(#"Added Index"[Status Code], 0, x[Indeks]), each  _ = x[Status Code]))),
    #"Removed Columns" = Table.RemoveColumns(HandWritten,{"Indeks"})
in
    #"Removed Columns"

Regards