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

[augustindelaf]

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

My parameters : 

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

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

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

[spock1984]

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.

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]

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.

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]

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 )) …

Best Regards

Rena

[augustindelaf]

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 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

[v-yiruan-msft]

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:

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

Best Regards

Rena

[augustindelaf]

@v-yiruan-msft 

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

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 : 

3. Error when locale settings are turned to English : 

thx in advance

[v-yiruan-msft]

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

[augustindelaf]

@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

[augustindelaf]

@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 ?

>> 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.

[v-yiruan-msft]

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

[augustindelaf]

@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

[v-yiruan-msft]

Hi @augustindelaf ,

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

Best Regards

Rena

[augustindelaf]

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

[v-yiruan-msft]

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:

Best Regards

Rena

[augustindelaf]

@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

[v-yiruan-msft]

Hi @augustindelaf ,

Whether your issue has been resolved?

Best Regards

Rena

[augustindelaf]

Hi @v-yiruan-msft , 

No, it hasn't been.

in our case, we will implement Access.

[csearle]

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

[v-yiruan-msft]

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 modified) as "Using locale" in query editor?

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?

Best Regards

Rena

[ch_livardas]

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.