I am trying to figure out how to convert a column of ordinal dates to Gregorian, but I'm not at all proficient with M code. I found the below on a Power BI forum that was flagged as a problem solved, but it returns the day after for each date (it adds the day number of the year to 1/1/YYYY). Can anybody help me out with this, please?
Thanks!
Date.AddDays(Date.From(DateTimeZone.From("20" & Text.Start(Number.ToText(116343),2) & "-01-01")),Int64.From(Text.End(Number.ToText(116343),3)))
Hi Kenny,
I don't profess to understand Julian dates, but you could just add another Custom Column to adjust the date returned by the above formula by 1:
=Date.AddDays([date column],-1)
Then you can delete the column containing the incorrect date.
Mynda
Thanks! I don't know why that didn't occur to me! I had been trying to do it in the same column.
Thanks again!
Hi Kenny,
I thought for a second on how to edit the formula in the same column too, but I'm lazy and it was going to require learning how those date formats work. It was just easier to add another column 😉
Mynda
