Visualize hourly query counts and highlight peak/off-peak hours

[Rohitb]

Hello,

I have a log table which showcases the number of queries executed like below.

Here, the column "Event Time" has a data in date time field (with seconds included as well).

I have a requirement to create visuals to display the number of queries executed per hour in a database for a specific day. Identify the hour with the highest number of queries and the hour with the lowest number of queries executed

What can be the best way to achieve this ?

[mickey64 · Accepted solution]

Step 0: I use your data.

Step 1: I add a 'Date' column on Power Query Editor.

Step 2: I add a 'Hour' column on Power Query Editor.

Step 3: I make a matrix on Power BI Desktop.

[bhanu_gautam]

@Rohitb , You can extract hour in Power Query

• Go to the "Transform Data" option to open Power Query Editor.
  Ensure that the "Event Time" column is in DateTime format.
  Create a New Column for Hour:
• In Power Query Editor, add a new column to extract the hour from the "Event Time" column.
  You can do this by using the "Add Column" tab and selecting "Time" -> "Hour".
  This will create a new column with the hour part of the "Event Time".
  Create a New Column for Date:
• Similarly, add another column to extract the date from the "Event Time" column.
  Use the "Add Column" tab and select "Date" -> "Date Only".
  Filter Data for a Specific Day:
• If you want to analyze data for a specific day, you can apply a filter on the new Date column to select the desired date.
  Create a Visual for Queries per Hour:
• Go back to the main Power BI window.
  Create a new bar chart or line chart.
  Drag the new Hour column to the X-axis.
  Drag the "Database Name" column to the Legend (if you want to differentiate between databases).
  Drag the "Event Time" column to the Values and set it to count.