Large Model - Power BI desktop loads whole data at incremental refresh step

[Anonymous]

Hi, I'm trying to create a dataset with large model enabled from a dataflow within same capacity

1. Added a new query in power query editor on a table that has 3B rows
2. Created parameters "RangeStart" and "RangeEnd" with values "2020-06-10 00:00:00" and "2020-06-09 00:00:00" respectively. Selected this values so I don't have to load any data at first in power bi desktop
3. Added custom filter equivalent to RangeStart <= Timeline column < RangeEnd
4. When I click on "Apply & Load", power BI loads all the data into desktop

Is there a way to configure incremental load without trying to load all the billions of rows into desktop at first?

[Anonymous]

Hi,

I don't know if your problem got solved.

But I recently encountered the same issue.

My Solution/Problem was that my "DateTime" was a datecolumn in the SQL Database and that I converted it to DateTime PowerQuery. So it has to be DateTime in the source not after some transformations in PowerQuery.

[Anonymous]

Hi @Anonymous , Sorry my question wasn't clear. I missed to mention that source is a dataflow. With some research I arrived at a explanation that this issue occurs since dataflows doesn't support query folding.

[amitchandak]

@Anonymous , I remember there is an option to set date for the initial load

https://radacad.com/all-you-need-to-know-about-the-incremental-refresh-in-power-bi-load-changes-only

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

[Anonymous]

Hi Amit, thanks for the reply but I have already configured the parameters.

In my example I have configured: Date >= '2020-06-10 00:00:00' and Date < '2020-06-09 00:00:00'. This condition should not fetch any rows. But powerbi tries to load whole data when I click on "Apply & Load" in power query editor

[Anonymous]

@Anonymous 
You have RangeStart(2020-06-10 00:00:00) later than RangeEnd (2020-06-9 00:00:00). I am not sure what range do you want with that parameter. Did you mean date between 9th and 10th?

If so you should have Date >'2020-06-09 00:00:00 and Date <= '2020-06-10 00:00:00'.

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
  

[Anonymous]

Hi @Anonymous , thanks for trying to help me with the issue. I'm referring to the Power BI documentation to implement incremental refresh in dataset (Link). One of the initial steps is to create parameters "RangeStart" and "RangeEnd" and use this paratemers in a custom filter on a date/time column. I have intentionally assigned the parameters with values RangeStart="2020-06-10 00:00:00" and RangeEnd="2020-06-09 00:00:00" so this will not load any data and the file size will be smaller which will make the publishing easier. 

Although, this was my intention, power bi is trying to load all the data I have in source (3 Billion rows) to Power BI desktop when I click on "Apply & Load" in the power query editor.

Edit: Please note my datasource is a dataflow. I'm trying to create this dataset on top of a dataflow which has 3.5 B rows

Thanks,

Bharath