Forum Discussion
Group by with gaps in data
Hi
I cant think of a way to do this and hoping you can help.
I have data that looks like this
| PersonID | Effective Date | End Date | Job Title |
| 2145 | 20/02/2017 | 30/04/2017 | Job 1 |
| 2145 | 01/05/2017 | 14/09/2017 | Job 1 |
| 2145 | 15/09/2017 | 01/10/2017 | Job 1 |
| 2145 | 02/10/2017 | 11/01/2018 | Job 1 |
| 2145 | 12/01/2018 | 31/07/2018 | Job 1 |
| 2145 | 01/08/2018 | 14/09/2018 | Job 1 |
| 2145 | 15/09/2018 | 14/09/2019 | Job 1 |
| 2145 | 15/09/2019 | 04/10/2020 | Job 1 |
| 2145 | 05/10/2020 | 18/04/2021 | Job 2 |
| 2145 | 19/04/2021 | 20/05/2021 | Job 1 |
| 2145 | 21/05/2021 | 31/12/2222 | Job 1 |
I want to get it looking like this
| PersonID | Effective Date | End Date | Job Title |
| 2145 | 20/02/2017 | 04/10/2020 | Job 1 |
| 2145 | 05/10/2020 | 18/04/2021 | Job 2 |
| 2145 | 19/04/2021 | 31/12/2222 | Job 1 |
But every time i group by i'm only getting 2 rows because the job titles for job 1 are the same.
Any ideas? can this even be done?
Thanks
Hi emmabrice ,
Here are the steps you can follow：
1. Create calculated column.
Index = RANKX('Table',[Effective Date],,ASC,Dense)Column = var _lastrow=CALCULATE(MAX('Table'[Job Title]),FILTER(ALL('Table'),[Index]=EARLIER('Table'[Index])-1)) return IF(_lastrow=[Job Title],0,1)Group = SUMX(FILTER(ALL('Table'),[Index]<=EARLIER('Table'[Index])),[Column])2. Create calculated table.
Table 2 = var _summarize= SUMMARIZE('Table',[PersonID],[Job Title],[Group],"1",MIN('Table'[Effective Date]),"2",MAX('Table'[End Date])) return SELECTCOLUMNS(_summarize,"PersonID",[PersonID],"Effective Date",[1],"End Date",[2],"Job Title",[Job Title])3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi emmabrice ,
Here are the steps you can follow：
1. Create calculated column.
Index = RANKX('Table',[Effective Date],,ASC,Dense)Column = var _lastrow=CALCULATE(MAX('Table'[Job Title]),FILTER(ALL('Table'),[Index]=EARLIER('Table'[Index])-1)) return IF(_lastrow=[Job Title],0,1)Group = SUMX(FILTER(ALL('Table'),[Index]<=EARLIER('Table'[Index])),[Column])2. Create calculated table.
Table 2 = var _summarize= SUMMARIZE('Table',[PersonID],[Job Title],[Group],"1",MIN('Table'[Effective Date]),"2",MAX('Table'[End Date])) return SELECTCOLUMNS(_summarize,"PersonID",[PersonID],"Effective Date",[1],"End Date",[2],"Job Title",[Job Title])3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZExCsMwDEWvUjwHpC9HxL5CrxCyFDIUCh3q+1O5rY0MST1ZvOcvIa9rEMwapiBMLCSMxYpoxdyK6/N2QdimrjKItVHMxPlUhTpq78DnqeIorAVqkQ5TxdFo6nKq1llTo33W41R1tKv5r1qpbeozuPDhAOoo0nevgp8qQ2p2tP6HenVIFThqG7CNiJ1BLfur1FRCa29Su5Z7eeyjGCm2RKUe3sXtDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PersonID = _t, #"Effective Date" = _t, #"End Date" = _t, #"Job Title" = _t]), #"Grouped Rows" = Table.Group(Source, {"PersonID", "Job Title"}, {{"ar", each _}}, 0, (x,y) => Number.From(x[PersonID]<>y[PersonID] or x[Job Title]<>y[Job Title])), Custom1 = Table.TransformColumns(#"Grouped Rows", {"ar", each let start=[Effective Date], end=[End Date] in [Start=start{0}, End=List.Last(end)]}), #"Expanded ar" = Table.ExpandRecordColumn(Custom1, "ar", {"Start", "End"}, {"Start", "End"}) in #"Expanded ar"
