Visualize data using the Azure Data Explorer connector for Power BI

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. Azure Data Explorer provides three options for connecting to data in Power BI: use the built-in connector, import a query from Azure Data Explorer, or use a SQL query. This article shows you how to use the built-in connector to get data and visualize it in a Power BI report. Using the Azure Data Explorer native connector for creating Power BI dashboards is straightforward. The Power BI connector supports Import and Direct Query connectivity modes. You can build dashboards using Import or DirectQuery mode depending on the scenario, scale, and performance requirements.


You need the following to complete this article:

Get data from Azure Data Explorer

First, you connect to the Azure Data Explorer help cluster, then you bring in a subset of the data from the StormEvents table. The StormEvents sample data set contains weather-related data from the National Centers for Environmental Information.

  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 Azure Data Explorer (Kusto) screen, fill out the form with the following information.

    Cluster, database, table options.

    Setting Value Field description
    Cluster The URL for the help cluster. For other clusters, the URL is in the form https://<ClusterName>.<Region>
    Database Leave blank A database that is hosted on the cluster you're connecting to. We'll select this in a later step.
    Table name Leave blank One of the tables in the database, or a query like StormEvents | take 1000. We'll select this in a later step.
    Advanced options Leave blank Options for your queries, such as result set size.
    Data connectivity mode DirectQuery Determines whether Power BI imports the data or connects directly to the data source. You can use either option with this connector.


    In Import mode, data is moved to Power BI. In DirectQuery mode, data is queried directly from your Azure Data Explorer cluster.

    Use Import mode when:

    • Your data set is small.
    • You don't need near real-time data.
    • Your data is already aggregated or you perform aggregation in Kusto

    Use DirectQuery mode when:

    • Your data set is very large.
    • You need near real-time data.

    Advanced options

    Setting Sample value Field description
    Limit query result record number 300000 The maximum number of records to return in the result
    Limit query result data size 4194304 The maximum data size in bytes to return in the result
    Disable result set truncation true Enable/disable result truncation by using the notruncation request option
    Additional set statements set query_datascope=hotcache Sets query options for the duration of the query. Query options control how a query executes and returns results.
  4. If you don't already have a connection to the help cluster, sign in. Sign in with an organizational account, then select Connect.

    Sign in.

  5. On the Navigator screen, expand the Samples database, select StormEvents then Transform Data.

    Select table.

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

  6. In the Power Query Editor, select the arrow next to the DamageCrops column then Sort descending.

    Sort DamageCrops descending.

  7. On the Home tab, select Keep Rows then Keep Top Rows. Enter a value of 1000 to bring in the top 1000 rows of the sorted table.

    Keep top rows.

  8. On the Home tab, select Close & Apply.

    Close and apply.

Visualize data in a report

Now that you have data in Power BI Desktop, you can create reports based on that data. You'll create a simple report with a column chart that shows crop damage by state.

  1. On the left side of the main Power BI 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 DamageCrops and State.

    Select fields.

    You now have a chart that shows the damage to crops for the top 1000 rows in the table.

    Crop damage by state.

  4. Save the report.

Clean up resources

If you no longer need the report you created for this article, delete the Power BI Desktop (.pbix) file.

Next steps

Tips for using the Azure Data Explorer connector for Power BI to query data