How can I create an Index column based on 2 columns (time and the process level)-CodePudding

I am trying to obtain the Index column (highlighted in yellow) that can count the number of times the product ID has iterated through 1 - 6 denoted by the Status Key and the tible is sorted in chronological order.

For detail: The Product ID follows a chronological order denoted by the timestamp and the Status Key can fluctuate back and forth (this a type of approval or rejecting process and the keys indicate the approval stage). I want the index column to count 1 every time the Status key starts to loop 1-6. The starting number will not always start at 1.

CodePudding user response：

You do not state a clear rule for how to detect when the Status Key starts to increment. But this approach using List.Generate should be adaptable in case the method I intuit from your data is not correct.

I increment the count by one starting after Status Key = 6

Source

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Products"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Product ID", Int64.Type}, {"Date", type datetime}, {"Status Key", Int64.Type}}),

//Generate List for Index column
// incrementing after SK = 6
    sk = #"Changed Type"[Status Key],
    IndexList = List.Generate(
        ()=>[Index = 1, idx = 0],
        each [idx] < List.Count(sk),
        each [Index = if sk{[idx]} = 6 then [Index] 1 else [Index],
                idx=[idx] 1],
        each [Index]
                            ),
//Add Index column to table
    integer = Int64.Type,
    newTble = Table.FromColumns(
                Table.ToColumns(#"Changed Type") & {IndexList},
                type table[Product ID = integer, Date = datetime, Status Key = integer, Index = integer]
    )
in
    newTble

Results

CodePudding user response：

For powerquery, Here's my version which assumes you want to increment the count by one starting after Status Key = 6. It might be a bit easier to understand

Add index

Add custom column with formula

= if [Status Key]=6 then [Index] else null

Right click that custom column and fill up

Click select both the Product ID and the new custom column, and Group By. Use any name you want and operation All Rows

Add another index

Expand the data column created by the group to extract Data and Status Key fields

Remove extra columns

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Status Key]=6 then [Index] else null),
#"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"}),
#"Grouped Rows" = Table.Group(#"Filled Up", {"Product ID", "Custom"}, {{"data", each _, type table}}),
#"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
#"Expanded data" = Table.ExpandTableColumn(#"Added Index1", "data", {"Date", "Status Key"}, {"Date", "Status Key"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded data",{"Custom"})
in  #"Removed Columns"