Forum Discussion
Table Hours In and Hours Out
Hi, i have 4 types of vehicles and i have 2 gates for entering and getting out.
I have this record:
| TRUCK | GATE IN | HOUR IN | GATE OUT | HOUR OUT |
| Truck 1 | Gate 1 | 09:00 | Gate 3 | 09:35 |
| Truck 1 | Gate 1 | 10:00 | Gate 3 | 10:35 |
| Truck 2 | Gate 1 | 10:10 | Gate 3 | 11:40 |
| Truck 3 | Gate 3 | 09:10 | Gate 1 | 10:45 |
| Truck 4 | Gate 3 | 10:20 | Gate 1 | 11:50 |
| Truck 2 | Gate 1 | 11:30 | Gate 3 | 13:50 |
I was thinking in creating some type os scatter chart to show each truck at each time where they enter and where they get out.
But i'm getting nowehere. I can't event add the hours in Y-axis.
Can someone help me on this?
Thank you?
Goncalo
Hi, JockerBoy
Based on your table, you can create a new calculated column to convert hours to minutes, and then put it into the Y-axis. I've taken some of your data and done a demonstration.
Just like this:
HourMinutes = HOUR([HOUR]) * 60 + MINUTE([HOUR])How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Go to Power Query Editor.
Duplicate your table.
For the first table, keep columns Truck, Gate In, and Hour In, and rename them as Gate and Hour.
For the second table, keep columns Truck, Gate Out, and Hour Out, and rename them as Gate and Hour.
Append these two tables together to create a single table with the columns: Truck, Gate, and Hour.LIKE:
Truck Gate Hour
Truck 1 Gate 1 09:00
Truck 1 Gate 3 09:35
Truck 1 Gate 1 10:00
Truck 1 Gate 3 10:35
Truck 2 Gate 1 10:10
Truck 2 Gate 3 11:40Add a Scatter Chart visual to your Power BI canvas.
Set the fields:
X-axis: Gate (Categorical)
Y-axis: Hour (Time)
Legend: Truck (to distinguish trucks)
Details: Add Truck to ensure proper differentiation.Format the Y-axis to display hours correctly by ensuring the column is recognized as a Time data type.
Use different colors for each truck for better visualization.💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedInHi,
I can't add the hours in the Y Axis.
I formated my Hours as "Time".
I tried another thing. i added new clolumn for Hour and minutes using "Hours" column.
And i get this:
But the Y Axis only accept "sum of Hours" or another type of calculation.
Maybe i can't use the scatter chart?
Gonçalo
Hi, JockerBoy
Based on your table, you can create a new calculated column to convert hours to minutes, and then put it into the Y-axis. I've taken some of your data and done a demonstration.
Just like this:
HourMinutes = HOUR([HOUR]) * 60 + MINUTE([HOUR])How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
