Use Excel to summarize values from Kusto for a list of Ids
Task at hand
I connect from Excel to Kusto/ADX.
I can’t import all rows into Excel, so I want to bring back from Kusto summary values for a list of keys.
The user of the report should be able to enter several Ids and these Ids should be fed to the Kusto query.
Table for entering the IDs
I use the SalesTable in help/ContosoSales. I want the user to enter a list of Customer Keys which are numbers from 1 to 18000.
I create a table with one column - Customer IDs and call the table Customers.
In a cell somewhere on the sheet (or another hidden sheet) I create a formula that will combine all the values in the table into one list separated with commas.
In this case the values are numeric , so I enter the formula =TEXTJOIN(",",FALSE,Customers[Customer IDs]) and the result is 12,234,666,444,555
If the Ids are strings, I can use the formula ="'" & TEXTJOIN("','",FALSE,Customers[Customer IDs]) & "'" and the result is '12','234','666','444','555'
Power Query
Query from Table/range
I give the cell with the concatenated Ids a name – ListIds and I create a query from the cell. I delete the change type step and use drill down so the query looks like this
let
Source = Excel.CurrentWorkbook(){[Name="ListIds"]}[Content],
Column1 = Source{0}[Column1]
in
Column1
This query can now be referenced from the main query to the SalesTable in Kusto
Query from Kusto
This query uses regular PQ steps to access the table and aggregate it + a custom step to implement the filtering.
After the navigation step that points to the table, I add a step
I edit the step:
= Value.NativeQuery(SalesTable1," | where CustomerKey in(" & ListIds & ")")
The function inserts a snippet of native code (KQL) into the query. The KQL snippet uses the in operator and the list if ids to filter only the selected Ids from the Excel table.
After I use group rows to summarize the data, the final native query is:
["SalesTable"]
| where CustomerKey in(12,234,666,444)
| project ["SalesAmount"],["FirstName"],["LastName"],["CustomerKey"]
| extend ["Full Name"]=strcat((strcat(["FirstName"], (" "))), ["LastName"])
| project ["SalesAmount"],["CustomerKey"],["Full Name"]
| summarize ["Sales"]=sum(["SalesAmount"]) by ["Full Name"], ["CustomerKey"]
| extend ["t0_0"]=tolong(["Sales"])
| project ["Full Name"],["CustomerKey"],["t0_0"]
| project-rename ["Sales"] = ["t0_0"]
I could include the summarize step as part of the NativeQuery function, especially if the aggregation is not supported by PQ like percentiles.
Refreshing
After I enter several IDs, I can click refresh or create a button that runs a macro to refresh and bring the aggregated result into a table.
Privacy
Every time you mix two sources in one query, you’ll need to address privacy in Power Query
In this example we use a query to Kusto with a query from a cell in Excel.
When prompted , I recommend to ignore privacy settings in this case.