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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sslezic
Helper I
Helper I

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:

sslezic_0-1606889089008.png

 

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:

sslezic_1-1606889575232.png

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?

2 REPLIES 2
sslezic
Helper I
Helper I

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
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:

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.