Forum Discussion
Create new column based on criteria and previous value
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}
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]))+1in 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
5 Replies
- AlienSx
Super User
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.
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.
Syndicate_AdminAdministrator
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"- wdx223_Daniel
Community Champion
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]))+1in 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_AdminAdd an index column to disambiguate.
