Hi @Anonymous, let me see if I understand. You are using this formula:
= DateTime.Date(DateTime.FromText([YourColumnHere], "pt-PT"))
But your issue is the hours/min/sec are all zeros even if they exist.
If I have that right, here is why.
- The outer function, DateTime.Date() stripped all time from the field after DateTime.FromText() got it.
- You later formatted that custom column as type datetime, so it automatically puts the time out there, and if none, it will have 12am with zeros for minutes and seconds.
You could trap this with something like this in a new Custom Column:
let
varDateTime = DateTime.FromText([DataEncomenda], "pt-PT"),
varDate = DateTime.Date(varDateTime)
in
if varDateTime = DateTime.From(varDate)
then varDate
else varDateTime
It returns this from your sample data that I tweaked on the first two rows. I used variables above because I didn't want to type those functions in a dozen times.
What my formula did is:
- let it do the DateTime.FromText() conversion - stored in varDateTime
- Then converted to date only with DateTime.Date(varDateTime) - stored in varDate
- Then I compared the varDateTime to varDate converte back to Datetime - using DateTime.From(varDate)
- If varDateTime has bunch of zeros, then they are the same, because DateTime.From(varDate) has no time, so just zeros.
- If they are different, then varDateTime must have valid time codes.
I keep varDateTime if there are valid times, and keep varDate if there are none or the times are all zeros.
Make sense?
Also note that If I then format that final column as DateTime, PQ will put the zero times back in. It essentially does a DateTime.From() on the entire column.
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
