Incremental Refresh on custom SQL Query within Power BI

[DataSpace]

I have requirement to implement Incremental refresh in Power BI where it is using one set of SQL Queries to fetch the data from SQL server.

I have collected some information from this video that it is possible to implement with below steps.

1. Create RangeStart & RangeEnd Parameters.

2. Go to Power Query Editor --> click on Respective Query--> Click on Source --> Expand Function bar --> Add RangeStart and RangeEnd parameters properly.

As per the video, above steps to add parameters within M Code of the source will give incremental refresh.

Not sure if this video has partial info or I am lacking something.

When I am refreshing (with above steps) nothing is changing. it has same data as it was having before.

simply, How can I achieve incremental refresh with SQL query (instead of table) within Power BI?

Any suggestion/solution / comment /help is most welcomed.

[NaveenGandhi · Accepted solution]

@DataSpace 

You need to setup gateways for any kind of refresh in Power BI service to work. Once you have setup the gateway and map it properly in the "Gateway and cloud connections" section in your screenshot, this issue will be resolved.


Regards,
NG

[v-jianpeng-msft]

Thank you @NaveenGandhi 

Hi, @DataSpace 

First, you can refer to the instructions on the official website to learn about the mechanism and configuration steps of incremental refresh.

Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn

The following article can be used as a reference:

How to Setup Incremental Refresh in Power BI (iterationinsights.com)

Best Regards

Jianpeng Li

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

[DataSpace]

Thanks @v-jianpeng-msft for response,

I have gone through the concept and steps and followed the same to implement incremental refresh.

I created the 2 parameters(RangeStart & RangeEnd) and put some random dates to filter the table. Also kept historic period as 2 years and incremental refresh for latest 10 days.

So Implementation is done.

Now, in PBI Desktop, when I refresh, it loads all the data between period set in parameters and not as per incremental refresh configuration. is it normal in PBI Desktop?

Then I published pbix in myWorkspace. Refreshing report is working fine. When I am trying to 'Schedule Refresh' for semantic model, it is giving error as "Scheduled Refresh is disabled because at least one data source missing credentials"

                                    

FYI.. I am using free account as of now.

When trying to put credentials in data source, it is actually disabled:

                                  

Some queries here:

 1. To test incremental refresh in PBI service, do I need minimum PRO license? and above issue is because of this only?

 2. Do we need Gateway to make incremental refresh work?

 3. Even if incremetal refresh is giving error, I should be able to change Data source credentials. why that is disabled?

[NaveenGandhi · Accepted solution]

@DataSpace 

You need to setup gateways for any kind of refresh in Power BI service to work. Once you have setup the gateway and map it properly in the "Gateway and cloud connections" section in your screenshot, this issue will be resolved.


Regards,
NG

[DataSpace]

1. do I also need minimum PRO license to implement Incremental refresh ?

2. above mentioned behaviour to load full data based on parameters in Power BI Desktop is expected?

[v-jianpeng-msft · Accepted solution]

Hi, @DataSpace 

As the super user said, you'll need to configure the gateway connection in Power BI, and then you can use incremental refresh.
The licensing question you asked. If your report is published to a shared workspace, you need at least a Pro license and above. Incremental refresh is supported for Power BI Premium, Premium per user, Power BI Pro, and Power BI Embedded models.
In addition, you ask that when you refresh in Power BI Desktop, you refresh all the data, which is normal behavior. Because incremental refresh requires you to publish the report into the Service and then configure a scheduled refresh for it to work. After the first refresh, the data in the incremental interval will be refreshed according to the partition you set on the second refresh.

Best Regards

Jianpeng Li

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

[DataSpace]

Hi @NaveenGandhi ,

After source , there is no transformation steps.

date column in the table (in Power BI) has Date/time Datatype. range Parameters also has Date/Time format from dropdown list(in Manage parameters window), so this is also in Sync.

Any other check or steps you can highlight?

[NaveenGandhi]

Hi 

Do you have any transformation steps after source? Also can you check if the date/datetime format is being followed correct in the parameters according to the date column?

 If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!