In-between Import and DirectQuery

[Andrey28]

Hi Everyone,

I am still relatively new to Power BI and I am trying to understand specifics related to data import. Currently I am primarily using Import function for my data (files saved at my PC or shared drives), however I am trying to understand better connections to data sources i.e. to Hive. I was poking around with the Direct Query option which looks nice, but based on my understanding it doesn't save data in Power BI model hence when I publish it, nothing is there. I understand that this somehow can be solved by scheduling data refreshes. However here is my question:

Is there an option in Power BI to query certain amount of data from tables, i.e. from Hive and keep it in the Model ? Basically I am dealing with the tables that have millions of records. I don't want to import the whole table but also want to avoid Direct Query. I am searching for some kind of Select From Where SQL-type language that I can add to the query editor that will allow me to keep data in my model. So I am looking for something in between. Can you point me to the right article ?

Thanks,

Andrey

[v-lionel-msft]

Hi @Andrey28 ,

You need to transform the data in 'Edit Query', then apply and close.

Best regards,
Lionel Chen

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

[mahoneypat · Accepted solution]

You should be able to stay in Import mode and set up a dynamic query that returns only a subset of date (e.g., last 30 days).  Depending on your source, you may also benefit from query folding where most of the work (filtering, column selection, etc.) is pushed back to the source.

If you need all the data but don't want to refresh the whole dataset everytime, you can explore the incremental refresh option (which is now available for pro licenses).

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh

Regards,

Pat

[Andrey28]

Pat, this helped, thanks