Incremental Refresh is failing in PBI Service - We couldn't parse the input provided as DateValue

[monaraya]

Issue Description:

Incremental Refresh is failing in Power BI service with below mentioned error message:

Last refresh failed: Thu Apr 23 2020 16:38:29 GMT+0530 (India Standard Time)

There was an error when processing the data in the dataset. Data source error: We couldn't parse the input provided as a Date value.. The exception was raised by the IDbCommand interface.Cluster URI:  

Activity ID: xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxx

Request ID: xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxx

Time: 2020-04-23 11:08:29Z

Data Source and Mode of Connection: PostgreSQL and Import

The report where transformations are done using Power BI UI has the issue but the report where the transformations are done using DAX query does not have any issue with refresh.

Cause :

• Using Gateway logs, we found the table ‘Product’ is the one with the Incremental setting ‘On’ and have no date conversion issue as the dates are coded in US format(Date columns are in US format in Power BI service by default).
• We had a table ‘Date’ which is the one without the Incremental setting and have the issue with the date conversion as the date columns were in UK Locale.
• Also, this happens if there is a function in the ‘Date’ table that cannot be query folded (i.e. Distinct, Sort).

Solution :

The solution is to enable preview feature, "Store datasets using enhanced metadata format", in Desktop and publish the new pbix to PBI Service.

• Open the pbix in Power BI Desktop (Latest version)
• Go to File --> Options and Settings -->Options -->Preview features -->Store datasets using enhanced metadata format -->Ok
• Save the report -->Refresh in PBI Desktop -->Publish to Power BI Service

The refresh worked well after this.

More info : https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-enhanced-dataset-metadata

Author: Sumana Biswas

Reviewer: Mounika Narayana Reddy