Date format assumed earlier than expected in Power Query

[Anonymous]

When getting data from Excel source, the date format is assumed when the format of the column is set to Any (early stage of data preparation, see printscreen below) and the header of the column is "Date".

Something system wise happening during header promotion. When we want to replace incorrect values (in this case 00.00.0000 generated for empty date by SAP) by null or another number, it returns an error stating 00.00.0000 is not a date even so the column type is set to any.

Enter a Date Value message

Could the logic be fixed so when the column type is set to any, we are not obliged to add the step before the header promotion generated automatically by the system ?

David

[v-qiuyu-msft]

Hi @Anonymous, 

Please upload your screenshots to clarify the issue. Please do mask sensitive information in the screenshots before uploading them. 

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi @v-qiuyu-msft ,

I have successfully uploaded the picture (strangely enough, I was not able to do so yesterday).

Hope this will help to understand the point.

Best Regards,

David

[v-qiuyu-msft]

Hi @Anonymous, 

I tested on my side with Power BI desktop 2.76.5678.782 64-bit (December 2019) but not able to reproduce the issue. Please run the same Power BI desktop version as ours then test again. 

Best Regards,
Qiuyun Yu

[Anonymous]

Dear @v-qiuyu-msft ,

Thank you for your message. I checked that my version is the latest available (2.76.5678.782 64-bit);

I noticed that your Replace value screen is different than the one I have, is there a reason? (you have the option to choose the data format on the left, which I don't have).

I am anyway getting the error using the "Replace Values" button in the ribbon and it is easily reproductible (dataset coming from SAP). Even with a datatable, it happens (and it is even worse as there is no way to avoid the "auto-conversion-to-date-format");

Thank you for your support,

David

[v-qiuyu-msft]

Hi @Anonymous, 

I would suggest you create a support ticket to let engineers look into the issue on your side.

Best Regards,
Qiuyun Yu