Problem dealing with date type from different locations

[Victormar]

Hello community,

I am dealing with a Power BI than integrates data from different excels files hosted in a sharepoint. The issue comes from the date type, as sometimes Power BI captures it like "2031-07-24" and others, 07/24/2031, when in the excels it is like "24-07-2031".

That said, sometimes it is also the excel that has formats "22-02-2031" "and 2031-02-29".

I have the column as text type, if I convert it to date then it throws me an error.

I have tried to adjust the region preferences on both excel online and the Report itself, but I understand there is a missmatch. Has somebody faced a similar issue, or knows how could I resolve it?

Ideally I want the format yyyy/mm/dd.

Thanks in advance for your time 🙂

[Victormar · Accepted solution]

Thank you guys!

I ended up doing a super loooong cleaning, splitting by delimiters, and then joining again 🙂

Not the most elegant solution... but for now it works:)

[suparnababu8]

Hi @Victormar 

Yes, Here are some YouTube videos that can help you handle different date formats while loading Excel files into Power BI: It may bw helpfull for you.

https://youtu.be/cPYNYOJAD3Q?si=6yr2FjgOQ9HSo_1P

https://youtu.be/hehYMb9kobw?si=YEK3ZjBFmAraR_mn

https://zebrabi.com/guide/how-to-change-date-format-in-power-bi-visual/

Thanks!

[moncx]

Hey,

Power BI might get confused when reading data from excel if the date format in the same collumn differs (as you said that excel might have formats of "22-02-2031" and "2031-02-29", then the power bi does not know where the day/month/year is). Try changing the format in the source and if all dates have the same format then Power BI should not return error.