How to Show Hourly Data in Power BI

Trying to show hourly data in Power BI can feel surprisingly cumbersome when you first start. Power BI loves summarizing dates into neat hierarchies of years, quarters, months, and days, which is great for high-level trends. But when you need to pinpoint the busiest hours for website traffic or sales, you have to tell it specifically how to break down the timeline. This article will walk you through the essential steps and best practices for creating clear and effective hourly visualizations in your Power BI reports.

So, Why Bother with Hourly Data?

Analyzing data on an hourly basis is essential for understanding the rhythm of your business and a customer's behavior. Aggregating everything into daily totals can hide the crucial patterns that happen throughout the day. Here are a few common use cases where hourly analysis shines:

Website Analytics: Pinpoint the exact hours your site gets the most traffic or conversions. This is perfect for scheduling a new blog post, planning website maintenance, or timing a marketing push.
Sales & E-commerce: Identify peak purchase times to optimize flash sales, send out email campaigns when customers are most active, or ensure your support team is fully staffed.
Call Center & Support Teams: Track hourly ticket or call volume to forecast future demand and schedule agents effectively, preventing long wait times during unexpected rushes.
Social Media Engagement: Find out when your audience is most likely to like, comment, and share your content to maximize the reach of your posts.
Operational Monitoring: For businesses using IoT devices or monitoring production lines, hourly data is critical for spotting anomalies, monitoring performance, and predicting maintenance needs.

In short, hourly data turns a blurry, day-long picture into a sharp, actionable timeline.

First, Get Your Data Ready for Analysis

Before you can build any visuals, you must ensure Power BI understands your time-based data correctly. The most important prerequisite is having a single column that contains both the date and the time information.

Check for a DateTime Column

Your source data should have a column formatted as "Date/Time." It might be named something like Timestamp, EventDate, or CreatedAt, and the data will look something like this: 1/15/2024 2:35:15 PM.

If your date and time are in separate columns, you'll need to combine them first in the Power Query Editor. You can do this by selecting both columns, going to the "Add Column" tab, and choosing "Merge Columns."

Once you have a potential DateTime column, you need to verify its data type:

Click on "Transform data" from the Home ribbon to open the Power Query Editor.
Find and select your date and time column header.
On the "Home" tab, look at the "Data Type" dropdown menu. It should be set to "Date/Time".
If it's set to "Text" or "Date," click the icon next to the column name or use the dropdown to change it. Power BI is smart enough to convert most standard formats.

Getting this step right is fundamental. If Power BI doesn't recognize your column as a "Date/Time" field, none of the time-based functions will work correctly.

Method 1: Using Power BI's Built-in Hierarchy (The Quick and Easy Way)

For a quick glance at trends, you can let Power BI’s default behavior do the initial work. This method is best for simple explorations but has some limitations.

Select a visual, like a Clustered Column Chart or a Line Chart.
Find your DateTime field (e.g., Timestamp) in the Data pane and drag it onto the X-axis field well.
Drag your measure (e.g., Sales Amount or Total Sessions) onto the Y-axis.

You'll immediately see your data grouped by year. In the top right corner of your visual, you’ll see several icons for navigating the hierarchy. To get down to the hour level, simply click the "drill down" icon (a downward arrow with diverging lines below it) multiple times. You'll move from Year → Quarter → Month → Day → Hour.

The Limitation: This default behavior aggregates all data for a specific hour together. For example, it will pool all sales that occurred at 9:00 AM across every single day in your dataset into one data point. This is useful for answering "What is our busiest hour on average?" but not for seeing a continuous trend over time.

Method 2: Create Separate Date and Hour Columns (The Best Practice)

For more control, flexibility, and accurate timeline visualizations, the best approach is to split your DateTime column into its components. Creating a dedicated Hour column makes filtering, sorting, and charting far more intuitive. You can do this in either Power Query or with DAX.

Creating an Hour Column in Power Query

This method adds the Hour column directly to your data model during the import and transformation stage. It’s often preferred because the column is created once and is more performant.

Open the Power Query Editor ("Transform data").
Select your main DateTime column.
Go to the "Add Column" tab in the ribbon.
Click the "Time" dropdown, then select "Hour".

That's it! A new column named "Hour" will appear, containing a number from 0 (12:00 AM) to 23 (11:00 PM). It's a good practice to ensure this new column's data type is set to "Whole Number" for proper sorting.

Creating an Hour Column with DAX

Alternatively, you can add a calculated column using DAX in the Report or Data view. This is useful if you don't want to edit the base query or need more dynamic calculations.

Navigate to the Data View (the table icon on the left).
Select the table containing your DateTime data.
From the "Column tools" ribbon, click "New column".
Enter the following DAX formula in the formula bar, replacing YourTableName and YourDateTimeColumn with your actual table and column names:

Hour = HOUR('YourTableName'[YourDateTimeColumn])

Press Enter, and Power BI will calculate and add the Hour column to your table.

Visualizing Your New Hourly Data Correctly

With a dedicated Hour column, you now have the power to build precise visuals.

Analysis 1: What is the Busiest Hour of the Day?

Here, we want to see the performance of each hour, aggregated across all days.

Create a Clustered Column Chart.
Drag your new Hour column to the X-axis.
Drag your measure (like Number of Pageviews) to the Y-axis.

You’ll likely run into one frustrating issue: the chart isn't sorted chronologically. By default, Power BI sorts the chart by the value on your Y-axis (e.g., from most pageviews to least). To fix this, click the ellipses (...) in the top right corner of your visual, select "Sort axis", and choose your Hour column. Then, select "Sort ascending" to ensure it reads 0, 1, 2, 3... and so on.

Analysis 2: Show me an Hourly Trend over Multiple Days

For this analysis, you want to see a continuous timeline, not aggregated data.

Create a Line Chart.
Drag your original DateTime column to the X-axis.
Click the little down-arrow next to the DateTime field in the X-axis well. Instead of choosing "Date Hierarchy", select the first option, which is just the name of the column itself (e.g., Timestamp). This tells Power BI to use every individual value, not group them.
Drag your measure to the Y-axis.

This will produce a detailed line chart showing the fluctuations of your metric, hour by hour, across your selected date range.

Pro Tip: Format Your Hour for Better Readability

Showing hours as "14" or "15" is technically correct but not always user-friendly in a presentation. To fix this, you can create a formatted text column and then teach Power BI how to sort it correctly.

Step 1: Create a Formatted Hour Column with DAX

Create a new calculated column with a formula like this one to produce "12 AM", "1 AM", "2 PM", etc.:

Formatted Hour = FORMAT('YourTableName'[YourDateTimeColumn], "h AM/PM")

Step 2: Tell Power BI How to Sort It

If you use this Formatted Hour column in a chart, it will sort alphabetically ("1 AM", "10 AM", "11 AM", "12 AM", "2 PM"...), which is wrong. You need to assign a numerical sorting order to it.

Go to the Data View.
Click to select your new Formatted Hour column.
Go to the "Column tools" tab in the ribbon.
Click the "Sort by column" button.
In the dropdown, select the numeric Hour column you created earlier.

Now, when you use the Formatted Hour field on your chart axis, it will be automatically and correctly sorted in chronological order. This small step makes your reports significantly more professional and easier for your audience to understand at a glance.

Final Thoughts

Dealing with hourly data in Power BI boils down to a few key principles: verifying your DateTime data type, creating a dedicated Hour column for flexibility, and mastering the "Sort by column" feature to ensure your visuals are intuitive. While it involves a few steps, controlling the granularity of your analysis will give you much deeper insight into your business operations.

If you're looking for a faster way to get these insights, tools are now available that handle this data preparation work for you. That's why we built Graphed. Instead of navigating Power Query, writing DAX, and configuring sorting, we allow you to simply ask questions in plain English, like "Show me website sessions by hour on a bar chart for last week". Graphed connects to data sources like Google Analytics in real-time and builds the fully interactive, correctly sorted chart for you in seconds, saving you from the setup time so you can focus on the insights.