Edit

Share via

Tutorial: Visualize data from Azure Data Explorer in Power BI

  • Article

Azure Data Explorer is a fast and highly scalable data exploration service for log and telemetry data. Power BI is a business analytics solution that lets you visualize your data and share the results across your organization. In this tutorial, you first learn how to render visuals in Azure Data Explorer. You then connect to Azure Data Explorer with Power BI, build a report based on sample data, and publish the report to the Power BI service.

If you're not signed up for Power BI Pro, sign up for a free trial before you begin.

In this tutorial, you learn how to:

  • Render visuals in Azure Data Explorer
  • Connect to Azure Data Explorer in Power BI Desktop
  • Work with data in Power BI Desktop
  • Create a report with visuals
  • Publish and share the report

Prerequisites

In addition to a Power BI subscription, you need the following to complete this tutorial:

Render visuals in Azure Data Explorer

Before jumping into Power BI, let's look at how to render visuals in Azure Data Explorer. This is great for some quick analysis.

  1. Sign in to https://dataexplorer.azure.com.

  2. In the left pane, select the test database that contains the StormEvents sample data.

  3. Paste the following query into the right window, and select Run.

    StormEvents
| summarize event_count=count() by State
| where event_count > 1800
| project State, event_count
| sort by event_count
| render columnchart

    This query counts weather events by state. It then renders a column chart for all states that have more than 1800 weather events.

    Events column chart.

  4. Paste the following query into the right window, and select Run.

    StormEvents
| where State == "WASHINGTON" and StartTime >= datetime(2007-07-01) and StartTime <= datetime(2007-07-31)
| summarize StormCount = count() by EventType
| render piechart

    This query counts weather events by type for the month of July in the state of Washington. It then renders a pie chart showing the percentage of each event type.

    Events pie chart.

It's now time to look at Power BI, but there's a lot more you can do with visuals in Azure Data Explorer.

Connect to Azure Data Explorer

Now you connect to Azure Data Explorer in Power BI Desktop.

  1. In Power BI Desktop on the Home tab, select Get Data then More.

    Get data.

  2. Search for Azure Data Explorer, select Azure Data Explorer, then Connect.

    Search and get data.

  3. On the Preview connector screen, select Continue.

  4. On the next screen, enter the name of your test cluster and database. Cluster should be in the form https://<ClusterName>.<Region>.kusto.windows.net. Enter StormEvents for the name of the table. Leave all other options with default values, and select OK.

    Cluster, database, table options.

  5. On the data preview screen, select Edit.

    The table opens in Power Query Editor, where you can edit rows and columns before importing the data.

Work with data in Power BI Desktop

Now that you have a connection to Azure Data Explorer, you edit the data in Power Query Editor. You drop rows with null values in the BeginLat column and drop the StormSummary JSON column entirely. These are simple operations, but you can also perform complex transformations when importing data.

  1. Select the arrow for the BeginLat column, clear the null check box, then select OK.

    Filter column.

  2. Right-click the StormSummary column header, then select Remove.

    Remove column.

  3. In the QUERY SETTINGS pane, change the name from Query1 to StormEvents.

    Change query name.

  4. On the Home tab of the ribbon, select Close and apply.

    Close and apply.

    Power Query applies your changes, then imports the sample data into a data model. The next few steps show you how to enrich that model. Again, this is just a simple example to give an idea of what's possible.

  5. On the left side of the main window, select the data view.

    Data view.

  6. On the Modeling tab of the ribbon, select New column.

    New column.

  7. Enter the following Data Analysis Expressions (DAX) formula into the formula bar, then press Enter.

    DurationHours = DATEDIFF(StormEvents[StartTime], StormEvents[EndTime], hour)

    Formula bar.

    This formula creates the column DurationHours that calculates how many hours each weather event lasted. You use this column in a visual in the next section.

  8. Scroll to the right side of the table to see the column.

Create a report with visuals

Now that the data is imported and you've improved the data model, it's time to build a report with visuals. You add a column chart based on event duration and a map that shows crop damage.

  1. On the left side of the window, select the report view.

    Report view.

  2. In the VISUALIZATIONS pane, select the clustered column chart.

    Add column chart.

    A blank chart is added to the canvas.

    Blank chart.

  3. In the FIELDS list, select DurationHours and State.

    Select fields.

    You now have a chart that shows the total hours of weather events by state over the course of a year.

    Duration column chart.

  4. Click anywhere on the canvas outside the column chart.

  5. In the VISUALIZATIONS pane, select the map.

    Add map.

  6. In the FIELDS list, select CropDamage and State. Resize the map so you can see the US states clearly.

    Crop damage map.

    The size of the bubbles represents the dollar value of crop damage. Mouse over the bubbles to see details.

  7. Move and resize the visuals so you have a report that looks like the following image.

    Finished report.

  8. Save the report with the name storm-events.pbix.

Publish and share the report

Until this point, the work you've done in Power BI has all been local, using Power BI Desktop. Now you publish the report to the Power BI service where you can share it with others.

  1. In Power BI Desktop, on the Home tab of the ribbon, select Publish.

    Publish button.

  2. If you're not already signed in to Power BI, go through the sign-in process.

  3. Select My workspace, then Select.

    Select workspace.

  4. When publishing is finished, select Open storm-events.pbix in Power BI.

    Publishing succeeded.

    The report opens in the service, with the same visuals and layout you defined in Power BI Desktop.

  5. In the upper-right corner of the report, select Share.

    Share button.

  6. In the Share report screen, add a colleague in your organization, add a note, then select Share.

    Share report.

    If your colleague has appropriate permissions, they can access the report that you shared.

Clean up resources

If you don't want to keep the report you created, simply delete the storm-events.pbix file. If you want to remove the report you published, follow these steps.

  1. Under My workspace, scroll down to REPORTS and find storm-events.

  2. Select the ellipsis (. . .) next to storm-events, then select REMOVE.

    Remove report.

  3. Confirm the removal.

Related content