Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
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
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/
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
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).
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:
By the way, there is only one date field from SharePoint metadata?
Best Regards
Rena
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
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
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
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.
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
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
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:
Best Regards
Rena
@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
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 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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
117 | |
109 | |
107 | |
92 | |
68 |
User | Count |
---|---|
167 | |
130 | |
129 | |
95 | |
91 |