Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have one table such as:
Person | Location | Start | End |
Person A | LocA | 29/04/2024 13:00 | 29/04/2024 15:57 |
Person A | LocR | 25/04/2024 18:00 | 29/04/2024 17:14 |
Person G | LocA | 28/04/2024 21:00 | 28/04/2024 21:02 |
I want to split rows into minute granularity rows, take last row above as example the result would be:
Person | Location | Start | End | Minute |
Person G | LocA | 28/04/2024 21:00 | 28/04/2024 21:02 | 28/04/2024 21:00 |
Person G | LocA | 28/04/2024 21:00 | 28/04/2024 21:02 | 28/04/2024 21:01 |
Person G | LocA | 28/04/2024 21:00 | 28/04/2024 21:02 | 28/04/2024 21:02 |
Its like a join,except instead of it being a exact match join to one of the timestamps its a between join with a date/time table that holds a row for each minute of the day. At least thats how I'm interpreting it.
I can do the splitting into minute granularity rows in SQL to allow the usual exact match of a join in PBI to work (join on between) however the more people, locations and dates covered the larger the extract so its limited in scale.
Wondering if powerbi would be able to handle that part so SQL would only need to get a row for each whole period as shown in first table.
Solved! Go to Solution.
Hi @madman12 ,
Below is my table:
This situation can be realised using the m-language:
You can create a custom column:
List.Transform(
List.DateTimes(
[Start],
Duration.TotalMinutes([End] - [Start]) + 1,
#duration(0, 0, 1, 0)
),
each DateTime.ToText(_, "dd/MM/yyyy HH:mm")
))
The final output is shown in the following figure:
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @madman12 ,
Below is my table:
This situation can be realised using the m-language:
You can create a custom column:
List.Transform(
List.DateTimes(
[Start],
Duration.TotalMinutes([End] - [Start]) + 1,
#duration(0, 0, 1, 0)
),
each DateTime.ToText(_, "dd/MM/yyyy HH:mm")
))
The final output is shown in the following figure:
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
88 | |
85 | |
68 | |
68 | |
64 |
User | Count |
---|---|
209 | |
118 | |
116 | |
81 | |
74 |