Create new column based on criteria and previous value

[antuneslucas]

I'm trying to create a column that displays the case id of each event based on Time column.

Each time column Time has a value, it incriment one in the case id.

Original table:

Description	Time
B
C
D
E
F
G	7
H
I
J	6
K
L
M	3
N
O
P	2
Q
R
S	5
T

Desired table:

Description	Time	CaseID
B		1
C		1
D		1
E		1
F		1
G	7	1
H		2
I		2
J	6	2
K		3
L		3
M	3	3
N		4
O		4
P	2	4
Q		5
R		5
S	5	5
T		6

I tried to reach this creating an index column and evaluating if Time column is not null, based on that increase by one the previous case id or keep the previous value, but I was not sucessfull. Mainly because Power Query do not manage tables as Excel do.

if [Time] <> null then [CaseID]{[Index] - 1} + 1 else [CaseID]{[Index] - 1} 

[Syndicate_Admin]

Add an index column to disambiguate.

[wdx223_Daniel · Accepted solution]

in DAX

=CALCULATE(COUNT(Table3[Time]),'Table3'[Time]<>0,ALLSELECTED(Table3[Description]))-CALCULATE(COUNT(Table3[Time]),'Table3'[Time]<>0,ALLSELECTED(Table3[Description]),'Table3'[Description]>=MAX('Table3'[Description]))+1

in M

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Custom1 = let a=List.Buffer(Source[ Time]) in List.Generate(()=>{1,0},each _{1}<Table.RowCount(Source),each {_{0}+Byte.From(a{_{1}}<>null),_{1}+1},each _{0}),
    Custom2 = Table.FromColumns(Table.ToColumns(Source)&{Custom1},Table.ColumnNames(Source)&{"CaseID"})
in
    Custom2

[Syndicate_Admin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pc23DcAwDAXRVQzWrmxJAzjn3Anafw0TFHDd4/2CMUojpRSSyigt6lCPBjSqfHDmiTqjRRVMK21Du6o2HbQTXarKdNMe9ObPwXk7vzykHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t, Time = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each [Time]),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom"," ",null,Replacer.ReplaceValue,{"Custom"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Custom"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Custom"}, {{"Rows", each _, type table [Description=nullable text, Time=nullable number]}},GroupKind.Local),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "CaseID", 1, 1, Int64.Type),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Added Index", "Rows", {"Description", "Time"}, {"Description", "Time"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Rows",{"Description", "Time", "CaseID"})
in
    #"Removed Other Columns"

[AlienSx]

@antuneslucas 

1. Table.FillUp by [Time] column (providing that you have nulls in there)

2. Group by [Time] column with Operation = "All Rows" in Group By dialog window.

3. Add index column

4. Expand table column.

[antuneslucas]

The second step, group by, would require more than one grouping criteria and I don't think it's possible in my case, because my data can contain different cases with the same time spent. Despite that, thank you very much for your answer.