To prepare for this recipe, do the following:
- Open Power BI Desktop.
- Create a table called R02_Table using an Enter Data query with the following data:
|
ID |
Date |
Start |
End |
|
1 |
1/1/2019 |
8:00:00 AM |
9:24:00 AM |
|
1 |
1/2/2019 |
5:24:00 AM |
9:14:00 PM |
|
2 |
1/1/2019 |
9:59:00 AM |
4:13:00 PM |
|
2 |
1/2/2019 |
3:31:00 AM |
12:01:00 PM |
|
3 |
1/1/2019 |
8:24:00 AM |
8:55:00 AM |
- Create a second table called R02_Hours using the following formula:
R02_Hours =
VAR __Hours =
SELECTCOLUMNS(
GENERATESERIES(1 , 12 , 1),
"Hour",
[Value] & ":00:00 "
)
VAR __Period =
SELECTCOLUMNS(
{ ("AM"),("PM") },
"AM/PM",
[Value]
)
VAR __HoursAMPM = GENERATEALL(__Hours, __Period)
VAR __Final =
SELECTCOLUMNS(
ADDCOLUMNS(
__HoursAMPM,
"Time",
TIMEVALUE([Hour] & [AM/PM])
),
"Time",
[Time]
)
RETURN
__final
- Ensure that these two tables are not related to one another in any way.