Use Azure Data Explorer data in Power BI

In this article, you'll learn about the different ways in which you can connect Azure Data Explorer as a data source to Power BI. Once connected, you can proceed to build Power BI reports.

Prerequisites

Connectivity modes

Power BI supports Import and DirectQuery connectivity modes. When building Power BI reports or dashboards, choose your connectivity mode depending on your scenario, scale, and performance requirements. Using Import mode copies your data to Power BI. In contrast, using DirectQuery mode queries your data directly from your Azure Data Explorer cluster.

Use Import mode when:

Use DirectQuery mode when:

  • Your dataset is large or you need near real-time data.

For more information on connectivity modes, see Import and Direct Query connectivity modes.

Use data in Power BI

You can connect Azure Data Explorer as a data source to Power BI in the following ways:

  • Starting in Azure Data Explorer web UI and then pasting the data in Power BI Desktop.
  • Starting directly in Power BI Desktop and then adding the Azure Data Explorer connector.

In the following steps, we'll be using the StormEvents table from our help cluster to demonstrate how to use Azure Data explorer data in Power BI.

  1. In the Azure Data Explorer web UI, on the left menu, select Query, and then select the database with your data.

  2. Create a query and select it. For example, the following query from the Samples > StormEvents table, returns storm events that caused the most crop damage:

    StormEvents
    | sort by DamageCrops desc
    | take 1000
    
  3. From the Export menu, select Query to Power BI.

    Screenshot of Azure Data Explorer web UI, showing the open Share menu with the Query to Power BI option highlighted.

  4. Launch Power BI Desktop.

  5. On the Home tab, select Transform data.

    Screenshot of the Home tab in Power BI Desktop, with the Home tab option titled Transform data highlighted.

  6. Paste the query in the Navigator pane.

    Screenshot of the Power BI Desktop Navigator pane, showing the drop-down menu of the right mouse button with the Paste option highlighted.

  7. Optionally, you can change the connectivity mode from DirectQuery to Import. In the Query Settings window, under Applied steps, select the settings cog. For more information, see Connectivity modes.

    Screenshot of the Query Settings window, showing applied steps with the settings cog highlighted.

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

    Screenshot of the Home tab with the Close & Apply option highlighted.

Next step