Skip to main content
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Frequent Visitor

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

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?

Frequent Visitor

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?



Community Support
Community Support

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:


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.

Helpful resources

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.