Forum Discussion
Data Flows - How to limit data in Power BI Desktop
"would like to be able to work with this data set in Power BI without having to download the entire thing"
That statement is a little too fluffy. How will you guarantee that your subset of data is sufficiently representative? This is like the column profiling that is based on the top 1000 rows. You can be very sure that the data on row 1001 will break your assumptions.
RangeStart and RangeEnd can serve your intended purpose if you accept the above risk. But they work a little different. Here's what actually happens.
- In Power Query your data source definition has to include a filter that uses RangeStart and RangeEnd. You can set these parameters as you like, for example to cover the last two months of data.
- In DAX when you configure Incremental refresh you IGNORE the values you set in Power Query (!). Instead you specify to keep the last five years of data but only refresh the last seven days (for example).
That means the values you set in Power Query allow you to only fetch partial data without impacting the actual Incremental Refresh mechanics on the service (sort of - until you push structural changes).
Now - if your data source is a dataflow then I can't really see how incremental refresh would be helpful. You're wasting space by keeping your data both in the dataflow and the dataset. I would go with the direct query option, and if necessary with aggregations, rather than having an import mode connection from a datflows source. Measure the performance and decide what the preferred option should be.
Are you saying doing the import from the data flow duplicates / wastes space? Won't an import provide improved performance vs a direct query? That is typically the case. Is it different for a Data Flow?
Yes, that's what I am saying. Using Dataflows as a source in Import mode is not just wasting space, it is also wasting time as you need to first refresh the dataflow and then refresh the dataset.
Mind you, until very recently this was our only option. Direct Query against Dataflows was only made available a few months ago.
Sorry, I want to make sure I fully undestand because you didn't address my question concerning performance. It's my assumption that direct query is always slower than imports. Is this a correct assumption, even with Direct Query against Data Flows in the Service?
If that's a correct assumption, and I find that the performance is too slow, what are my options?
1) Create summarized queries (same recourse when working with Direct Query in general)
2) Not use Data Flows because it would just be duplicating data
" It's my assumption that direct query is always slower than imports. "
That's too ambiguous. You need to differentiate between developer performance and end user performance. What is your goal? Do you want to minimize dataset refresh times or do you want to minimize UI response times?