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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
augustindelaf
Impactful Individual
Impactful Individual

Implementation of Incremental Refresh on my Dataset (Power Bi pro)

Hi all,

 

I want to implement Incremental Refresh on my Dataset, based on Power BI Pro license.

 

The data : SharePoint Folder (SharePoint.files) connector with numerous Excel files on the SharePoint.

What I did : create the parameter RangeStart and RangeEnd, like on the tutorial videos, and then Filter my tables based on these parameters. (The field is a Date Time field, as required).

https://docs.microsoft.com/fr-fr/power-bi/service-premium-incremental-refresh

 

a.PNG

 

My parameters : 

b.PNG

Then, after the steps are applied in Power Query, I followed the guidelines on the tables in Power BI : 

 

c.PNG

Then, I published the dataset on Power BI Service and it says : "We couldn't parse the input provided as a DateTime value."

 

d.PNG

 

Is there anything I did wrong ?

Just to let you know : 

-I have several queries in the Power Query (~40 queries) but I just filtered my Final tables (the other queries are intermediary).

-In Power Bi, not all my tables have Incremental refresh toggled On.

 

>Do I need to filter all my queries with the parameters in Power Query ?

>Do I need to have Incremental refresh toggled On every table in Power BI ?

 

Can anyone help us ? It is a very important matter for my client to implement Inc. Refresh.

If needed, I can send the sample file but by email.

 

Many thanks

Best regards

Augustin

MYPE Consulting

 

22 REPLIES 22
spock1984
Frequent Visitor

Hi,

Have you managed to solve this? Maybe you can share the link to your files, I will have a look. I am trying to set this up myself also. I do not have this error.

 

My issue is elsewhere - I do not fully understand the outcome of this configuration. What I need is that every time a new Excel file is loaded (with fresh modified date) - a refresh is done.

 

2020-03-04 15_21_23-.png

 

After pushing my dashboard to PBI Service, should I set up a refresh schedule? Like set up an hourly refresh - and then the refresh will happen only if there is a file with new modified date

Baskar
Resident Rockstar
Resident Rockstar

Dear Friend, 

I will help you here, 

Question : 

Do I need to have Incremental refresh toggled On every table in Power BI ?

Answer:

       Based on view native query option in power query you can able to see the incremental refresh in power bi services. pls refer the below attachmnet.

Step 1.PNG

 

 

 

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

http://powerbi.baskarbiconsultant.com/

v-yiruan-msft
Community Support
Community Support

Hi @augustindelaf,

The issue seems to be caused by the date format mismatch . Could you please check the date format of date filed for SharePoint excel files ? At the same time , check the locale of the current report file by clicking File->Options and Settings->Options->CURRENT FILE-->Regional Settings. The date format of two sides should be consistent. For example , the format of the date column in excel is MM/DD/YYYY , then the locale of your report file should also be set to the region with the corresponding date format (Like English (United States )) …

locale.jpg

Best Regards

Rena

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

Hello @v-yiruan-msft ,

 

Thank you for your kind answer.

The date that is called is field [Date Modified]), and it is not from Excel but it is sharePoint metadata.

It seems to have same format as the parameters (RangeStart : 10/02/2020 00:00:00).

 

Date modified is SharePoint metadataDate modified is SharePoint metadata

 

I checked my locale settings that are in french and changed it to English (United States).

Still, i have the same issue from PBI Service : "we couldn't parse the input provided as a DateTime value".

 

Best regards,

Augustin

 

 

Hi @augustindelaf

Could you please select the field [Date Modified] and navigate to "Modeling"ribbon to show me the data type and the date format as below screenshot:

date format.JPG

 

By the way, there is only one date field from SharePoint metadata?

 

Best Regards

Rena

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

@v-yiruan-msft 

 

1. another screenshot here : (Date Modified from the SharePoint metadata) :Capture.PNG

Please note that when I pass the regional parameters from French(France) to English (USA) in Power BI Desktop, the error ("we couldn't parse the input provided as a DateTime value.") appears right at the step where my RangeStart & RangeEnd parameters are invoked. Then, this screenshot is done with locale parameters in French (France).

 

 

Turning on Locale settings to english USA : 

b.PNG

 

3. Error when locale settings are turned to English : 

c.PNG

 

 

thx in advance

Hi @augustindelaf,

Based on the screenshots and some instructions you provided , it seems that setting  the locale  to French is OK . May I ask if this report can be published to Power BI Service successfully before you set any incremental refresh for this report ? In addition , besides did filter the [Date modified] field of table Diagomar based on the parameters RangeStart and RangEnd , is there any other similar operation performed on other tables ? In other words , have be set up incremental refresh for other tables?

 

Here is one documentation about Incremental, hope this is helpful for you .

 

Best Regards

Rena

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

@v-yiruan-msft ,

Thank your for the kind answer.

 

Yes, of course, the file has been published on Power BI Service and the refresh is doing OK.

I can provide multiple sceenshots of the refresh logs is you want.

 

No, it hasn't been applied to all tables but only Final tables.

Other tables (or queries, to say the right word because we are in Power Query), are just intermediary tables, they get fragments of files and then they are merged and expanded to the "Main" Query, which in named VRF Samples and that has the filter with RangeStart and RangeEnd.

 

Do I need to implement the Filter on these tables as well ?

Thx for the radacad link.

 

BR

Augustin

 

 

@v-yiruan-msft 

 

I made the "Filtered Rows" with the Parameters on each query of my model, final or intermediary query.

Just didn't do it for the "Transform Sample" queries that are generated automatically by Power Query. Is it also necessary ?

Capture.PNG

>> In my queries, 2 of them are referencing the query VRF Samples : "Index" and "VRF Samples + Test Unpivot". 

Since they are referencing my main query that has the "Filtered rows" with Parameters, I don't need to do it for these queries, right ?

 

Btw, I still have an error.

jj.PNG

Hi @augustindelaf,

According to your reply, publish the report to Power BI Service works well before before the report be set the incremental refresh, and the problem seems to be after set the incremental refresh...

You don't need to set incremental to refresh for all tables, you can set it for the required tables . I want to ask whether the parameter RangeStart and RangeEnd is to filter the data onto the date field in the table ?

 

Best Regards

Rena

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

@v-yiruan-msft 

 

Many thanks in advance for your quick answer, this is an important issue for us.

I can provide you with a sample file if needed, if you just tell me your email by private message.

 

BR

Hi @augustindelaf ,

If it is convenient to upload your file to OneDrive for Business after remove the sensitive data?

 

Best Regards

Rena

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

Dear @v-yiruan-msft

 

Yes, your analysis seems to be correct.

Thank you also for the answer, then it is understood that I don't need to implement a filter with the Parameters on all my queries, but only on the ones that are huge.

 

"I want to ask whether the parameter RangeStart and RangeEnd is to filter the data onto the date field in the table ?"

>>> Yes, I use the Field [Date Modified] that is coming from SharePoint metadata as a way to filter onto the Latest files that have been Modified on the SharePoint.

Is it clear for you ??

 

Concerning the share of the file, yes I can upload it on OneDrive, please send me you email adress and I will send you the URL of the file location. (it is on Google Drive)

 

Best regards

Augustin

 

Many thanks

Best regards,

Augustin

Hi @augustindelaf ,

Sorry for delay. I just checked your provided PBIX file, it seems you configured incremental refresh for table SharePoint Comments(filter field: Modified)and table VRF Samples (filter field:Date modified). The issue may be caused by the data type of field Date modified in the table VRF Samples. Please add one step(change the data type as datetime) for this table between Step "Filtered Rows"and Step “Filtered for Incremental Refresh” for Applied steps in Query Editor:incremental refresh issue.JPG

Best Regards

Rena

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

@v-yiruan-msft Hello, any news regarding the incremental refresh ?

Have you been able to make it work ?

 

Many thanks for your quick support

BR

Augustin

Hi @augustindelaf ,

Whether your issue has been resolved?

 

Best Regards

Rena

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

Hi @v-yiruan-msft , 

 

No, it hasn't been.

in our case, we will implement Access.

Hi,

 

I am having a similar issue,

 

When i try the service refresh with incremental refresh turned on i get a similar error related to not parsing the value as a date value.

 

When i turn off incremental refresh, or when i publish just with the data source i want to use the incremental refresh for this works, but not when i enable the load of the other data sources within my model.

 

Thanks

 

Chris

Hi @augustindelaf,

According to your provide PBIX file, I didn't find the possible cause of error... Could you please try to change the data type of table SharePoint Comments(field: Modified)and table VRF Samples (field:Date modifiedas "Using locale" in query editor?

locale.jpg

If the above method doesn't work, please remove the incremental refresh from table VRF Samples or table SharePoint Comments, then publish again. Want to check if one of table cause the error...

 

In additional, could you please provide your Power Service region?

REGION.JPG

 

Best Regards

Rena

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

I Have the exact same problem. When i set incremental refresh then Power BI Services shows this:


Message:We couldn't parse the input provided as a Date value.

Cluster URI:WABI-NORTH-EUROPE-redirect.analysis.windows.net

Activity ID:74662d51-ffcf-4ed7-8442-36df2f4af3e0

Request ID:5dbc60c3-74a0-2f1b-b2aa-04eca279672b

Time:2020-03-03 13:57:40Z

 

when i remove incremental refresh everything is ok.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.