Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |