Forum Discussion
Helper IIidentifying continuous dates per person in new column
Solution SupplierYou'll have to test this one carefully.
In the linked-to workbook below, there's a source table at cell B2 (your data). See the Power Query result table at cell I2. There's cell H1 (a named range MinGap) where you can input a gap (in days) which should consider gaps smaller than that as continuous dates (it may be a day out). Change that figure then refresh the PQ table. As I left it, the results matched your required/expected dates.:
Link to workbook: https://app.box.com/s/cwq6j9kxwakgriwxx2uzomj6a4trdylv
Helper IIhi p45cal,
this works perfectly in excel - how can i apply the same method in Power BI query? i don't know how to get the MinGap in my model
- p45cal
re: "how can i apply the same method in Power BI query?"
I'm ashamed to say 'I don't know'. Power BI is something I've only just begun to explore and my attempts at this would probably be very amateurish. Maybe someone seeing this thread would know how to adapt my solution.
I did try Yolo's query on your bigger data, primarily to compare results, and 55 minutes later it's still:
although my machine is 9 years old.
Does your data reach Power BI via Excel or in some other way?
- p45cal
In the linked-to pbix file below is my offering.
Note that your columns are not the same as your original request (Client ID has become Person Id, and there is no Service column) so I've had to re-write the query a bit.
The pbix file links directly to your dropbox file, so don't change your dropbox file until you've changed the source in the pbix file.
Regarding MinGap, I've added that instead as a step in the query which you can edit easily:
Link: https://app.box.com/s/pzc1ewxh4xc5z8q7barwgtjon13cz2bm