Edit

Use Power Apps to query data in Azure Data Explorer

  • Article

Azure Data Explorer is a fast, fully managed data analytics service for real-time analysis of large volumes of data streaming from applications, websites, IoT devices, and more.

Power Apps is a suite of apps, services, connectors, and data platform that provides a rapid application development environment to build custom apps that connect to your business data. The Power Apps connector is useful if you have a large and growing collection of streaming data in Azure Data Explorer and want to build a low code, highly functional app to make use of this data. In this article, you create a Power Apps application to query Azure Data Explorer data.

Prerequisites

Connect to Azure Data Explorer Connector

  1. Go to https://make.powerapps.com/ and sign in.

  2. On the left menu, select more > Connections.

  3. Select + New connection.

    Screenshot of the connections page, highlighting the create a new connection button.

  4. Search for Azure Data Explorer, and then select Azure Data Explorer.

    Screenshot of the new connection page, showing the search and select Azure Data Explorer connection.

  5. Select Create on the Azure Data Explorer window that appears.

    Screenshot of the Azure Data Explorer connection dialog box, highlighting the create button.

  6. Provide your credentials in the authentication window.

For more information on the Azure Data Explorer connector in Power Apps, see Azure Data Explorer connector

Create app

  1. On the left menu, select Apps.

  2. Select + New app > Start with a page design.

    Screenshot of the apps page, showing the create a new canvas app button.

  3. Select Blank canvas. By default, Tablet format is selected.

Add connector

  1. On the left menu, select Data.

    Screenshot of the navigation menu in the new app page. The menu option titled Data is highlighted.

  2. Select Add data.

  3. Expand Connectors, select Azure Data Explorer, and then select your Azure Data Explorer user.

    Screenshot of the app page showing a list of data connectors. The connector titled Azure Data Explorer is highlighted.

Azure Data Explorer is now added as a data source.

Configure data row limit

Optionally, you can set how many records are retrieved from server-based connections where delegation isn't supported.

  1. On the menu bar, select Settings.

  2. In General settings, scroll to Data row limit, and then set your returned records limit. The default limit is 500.

    Screenshot of the settings page, showing the return results limit setting.

    Note

    The limit value for returned records is between 1 and 2,000.

Add Dropdown

  1. On the menu bar, select +Insert.

  2. Select Input, and then select Drop down. The Drop Down properties pane appears on the rightmost of the canvas.

  3. In the properties pane, select the Advanced tab.

  4. Under Data, replace the placeholder text for Items with:

    ["NEW YORK","TEXAS"]

    A dropdown menu appears on the canvas. Once you have data, you can select New York or Texas by expanding the dropdown menu.

    Screenshot of the app page, showing the populated items in dropdown menu.

  5. With the dropdown still selected, replace the placeholder text for OnChange with the following formula.

    ClearCollect(
KustoQueryResults,
AzureDataExplorer.listKustoResultsPost(
"https://help.kusto.windows.net",
"Samples",
"StormEvents | where State == '" & Dropdown1.SelectedText.Value & "' | take 15"
).value
)

Add Data Table

  1. Select +Insert in the menu bar.

  2. Select Layout > Data table. Reposition the data table as needed.

  3. In the properties pane, select the Advanced tab.

  4. Under Data, replace the placeholder text for Items with the following formula. The formula specifies the column types to be mapped out according to the formula in Add Dropdown.

    ForAll( 
KustoQueryResults, 
{ 
EventId: Value(Value.EventId), 
EventType: Text(Value.EventType),
DamageProperty: Value(Value.DamageProperty) 
} 
)

  5. In the properties pane, select the Properties tab.

    The Data source is autopopulated with the source specified in the Items section of the data table. In this example, the source is KustoQueryResults.

  6. Select Edit fields, and then select + Add field.

    Screenshot of the app canvas showing the selected columns to be added to the data table.

  7. Select desired fields and then select Add. A preview of the selected data table appears.

    Screenshot of the app page, showing a preview of the table populated with data.

  8. Select Save, and then enter a name for your app.

Validate app

  1. Select the Play button on the rightmost of the menu bar, or select F5 to preview the app.
  2. Try the dropdown, scroll through the data table, and confirm successful data retrieval and presentation.

Limitations

  • Power Apps has a limit of up to 2,000 results records returned to the client. The overall memory for those records can't exceed 64 MB and a time of seven minutes to run.
  • The connector doesn't support the fork and facet operators.
  • Timeout exceptions: The connector has a timeout limitation of 7 minutes. To avoid potential timeout issue, make your query more efficient so that it runs faster, or separate it into chunks. Each chunk can run on a different part of the query. For more information, see Query best practices.

For more information on known issues and limitations for querying data using the Azure Data Explorer connector, see Known issues and limitations

Related content