Filtering (Kusto) Direct Query results by using (Excel) Imported data

[sslezic]

Hi guys,

I have many devices uploading reams of data into ADX. I need to pull out and present a subset of this data for a specific business need. The subset is really defined by a different department and I want to give them control over definition of this subset.

I figured I could solve this by having an Excel sheet on Sharepoint that they have access to. This sheet defines the specific device IDs that I need to pull the data for and some additional metadata used for additional report filtering. The changes to this configuration data are few and far inbetween and daily refreshes/imports into the data set would do just fine.

ADX data I chose to pull through Direct Query as I need to be able to show this data in as near real time as possible. On top of having data points over time, I also need to compute various statuses for the devices. During my first couple of cracks at this, my status table was computed in Power Query after the data was pulled from ADX. However this was not working properly as my newly computed table was in Import mode and would not refresh along with the fresh data. Eventually, I offloaded all of my status computation to ADX and pull the final table down freshly computed.

The relevant subset of my model looks like this:

All of the "imported" tables there come from my Excel sheet. Now here comes the kicker... How can I limit the contents of the StatusFacts table to only include those serial numbers that were defined in SerialNumbers table?

Any attempt to inject the serial numbers defined in SerialNumbers table into ADX query works beautifully in Power Query editor, but results in the following when applying to model:

I tried using the new dynamic M parameters, which works when the report is up and running, but that initial load doesn't work as I have to hard-code an initial value into the parameter.

I'm really looking for some ideas on what can be done. Is there perhaps a different architectural solution for this problem that doesn't involve Excel? Is this even possible?

[sslezic]

Hi @v-yangliu-msft ,

Thank you for your reply. However, I don't quite understand it. "Similar issues" listed have nothing to do with my problem. I'm also not sure what you're trying to say with: "Direct query does not support queries"!?

I'm aware of Direct Query limitations (or at least think I am), and with that my original question still stands. Perhaps I made it too verbose in an attempt to explain as much as I could.

I wish to inject data to be used as a filter for Direct Query sent to ADX (e.g. TargetTable | where ID in (my_imported_list_of_ids)). When I hard-code that filter data into the query, everything works fine. But I wish to externalize this filter data so that it's editable by my users. When I attempt to do it through an Excel sheet on Sharepoint, Power BI blows up on me. How else could I do this?

[v-yangliu-msft]

Hi @sslezic，

Direct query does not support custom steps and queries. If you apply some custom steps in power query, you need to switch to the import model.，Please refer to the limited data conversion mentioned here.

Links related to similar issues, I hope to help you:

https://community.powerbi.com/t5/Desktop/Unable-to-delete-or-rename-Direct-Queries-due-to-new-error/...

https://community.powerbi.com/t5/Desktop/Query-Changes-error/td-p/1339556

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.