Forum Discussion
Identify start and end date in a snapshot date column
Hi Community,
I have a table which contains snapshot of users which are having a specific license on a specific date. A user can have license for a month and then never have it again, or the user can have the license for few months then it is removed and assigned again in future. Below is a table that represent simplified version of the original table. When a user has a license he/she will appear in the daily snapshot, if not then the user is not in the snapshot. In reality these are snapshots of members of AD security group in form of excel files loaded into a PBI dataflow and appended. Each daily file contains around 1500 users (records).
| User ID | Snapshot Date |
| 1 | 01/01/2023 |
| 2 | 01/01/2023 |
| 3 | 01/01/2023 |
| 1 | 02/01/2023 |
| 2 | 02/01/2023 |
| 3 | 02/01/2023 |
| 1 | 03/01/2023 |
| 2 | 03/01/2023 |
| 1 | 04/01/2023 |
| 2 | 04/01/2023 |
| 1 | 05/01/2023 |
| 2 | 05/01/2023 |
| 3 | 05/01/2023 |
| 4 | 05/01/2023 |
| 3 | 06/01/2023 |
| 4 | 06/01/2023 |
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY7BCcAwEMN28TvQxHfpMkf2XyOhrxZR8EtI4CoNNfVxnbk7tFrJREH0hGZohmYYDINW0kpak9bkiS/KH+um9UJrAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, #"Snapshot Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", Int64.Type}, {"Snapshot Date", type date}})
in
#"Changed Type"
Expected outcome
- Transform the long input table into a table with User ID, License Start On Date, License End On Date, Is Currently Active
- Column Is Currently Active can be easily derived by testing if the latest Snapshot Date is between the Start and End dates. So figuring out point 2 is easy once I have point 1 solved.
- I would like to know the historical assignments of the license like for User ID = 3
| User ID | License Start On Date | License End On Date | Is Currently Active |
| 1 | 01/01/2023 | 05/01/2023 | No |
| 2 | 01/01/2023 | 05/01/2023 | No |
| 3 | 01/01/2023 | 02/01/2023 | No |
| 3 | 05/01/2023 | 06/01/2023 | Yes |
| 4 | 05/01/2023 | 06/01/2023 | Yes |
Tried and failed approaches
Initially I started with creating a cross-join between all User IDs and Snapshotdates and then joining it with the original table. This would tell me the dates on which the user did not have a license. This approach was not successful and it will not be optimal considering the daily snapshot has around 1500 records resulting in approx 550k records per year.
Also, for inspiration I have looked into the SQLBI's Data Modelling course, section Using Snapshots.
Related posts which I tried and failed to apply to my scenario:
- Identify start and end date in a date column - Microsoft Fabric Community by Vijay_A_Verma - this seemed very similar to my problem but after adding my data as the source, the Groupping was not as expected. As input I used the cross-join table (mentioned above) to have the blank dates when user did not have the license (values FALSE). The downside of the cross-join table was that a user who has the license assigned first time on 05 Jan 2023 (ID 4), would still show as FALSE on 01 Jan 2023. I have tried both with GroupKind.Local and GroupKind.Global
- Solved: Transforming a Single Column into Two Dates - Star... - Microsoft Fabric Community and Solved: Transforming a Single Column into Two Dates Versio... - Microsoft Fabric Community by CNENFRNL - in these solution we can rely on a flag Opt-In and Opt-Out to determine the Start and End dates. I don't have such flags in my solution.
Thank you beforehand for your suggestions.
Hi
1. Group by ID, sort by Date and add Index
2. LocalGroup if DateMax-DateMin = IndexMax-IndexMin then same group, else new group
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY7BCcAwEMN28TvQxHfpMkf2XyOhrxZR8EtI4CoNNfVxnbk7tFrJREH0hGZohmYYDINW0kpak9bkiS/KH+um9UJrAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, #"Snapshot Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", Int64.Type}, {"Snapshot Date", type date}}),
#"Group1-Sort+Index" = Table.Group(
#"Changed Type",
{"User ID"},
{{"Data", each Table.AddIndexColumn(Table.Sort(_,{{"Snapshot Date", Order.Ascending}}), "Index"),
type table [User ID=nullable number, Snapshot Date=nullable date, Index=Int64.Type]}}),
#"Group2-LocalGroup" = Table.AddColumn(
#"Group1-Sort+Index",
"Data2",
each Table.Group(
[Data],
{"User ID", "Snapshot Date", "Index"},
{{"Start", each List.Min([Snapshot Date]), type nullable date},
{"End", each List.Max([Snapshot Date]), type nullable date}},
GroupKind.Local,
(x,y)=> if(y[Index]-x[Index])=Duration.Days(y[Snapshot Date]-x[Snapshot Date]) then 0 else 1
)[[User ID],[Start],[End]]),
Data2 = Table.Combine(#"Group2-LocalGroup"[Data2])
in
Data2Stéphane
4 Replies
slorinSuper User
Hi
1. Group by ID, sort by Date and add Index
2. LocalGroup if DateMax-DateMin = IndexMax-IndexMin then same group, else new group
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY7BCcAwEMN28TvQxHfpMkf2XyOhrxZR8EtI4CoNNfVxnbk7tFrJREH0hGZohmYYDINW0kpak9bkiS/KH+um9UJrAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, #"Snapshot Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", Int64.Type}, {"Snapshot Date", type date}}),
#"Group1-Sort+Index" = Table.Group(
#"Changed Type",
{"User ID"},
{{"Data", each Table.AddIndexColumn(Table.Sort(_,{{"Snapshot Date", Order.Ascending}}), "Index"),
type table [User ID=nullable number, Snapshot Date=nullable date, Index=Int64.Type]}}),
#"Group2-LocalGroup" = Table.AddColumn(
#"Group1-Sort+Index",
"Data2",
each Table.Group(
[Data],
{"User ID", "Snapshot Date", "Index"},
{{"Start", each List.Min([Snapshot Date]), type nullable date},
{"End", each List.Max([Snapshot Date]), type nullable date}},
GroupKind.Local,
(x,y)=> if(y[Index]-x[Index])=Duration.Days(y[Snapshot Date]-x[Snapshot Date]) then 0 else 1
)[[User ID],[Start],[End]]),
Data2 = Table.Combine(#"Group2-LocalGroup"[Data2])
in
Data2Stéphane
Hi Stéphane, I have pasted your m-code but I am not getting expected result. Each user has 1 record with duration of 1 day. The expected output is 5 records as shown in table in my initial post.
Could you share screenshot of the output on your side?
- slorin
Hi
I have 5 records.
I use french version, date = dd/MM/yyyy
Stéphane
I have not noticed that in the sample JSON file the month and day were switched around and that is why it did not work. After I have corrected the date and it worked as expected. Additionally I have connected your solution to my full dataset and it works.
Thank you very much for the solution.
Martin.
