Hello,
I have a new issue with dates coming in a weird format. They are "January 2020/2021", where the first year is calendar year and the second is Fiscal Year.
I want it in month and calendar year to be able use it in the same visualizations that I use for other data for the same periods.
I have managed to extract the month name, and also the calendar year. But when trying to combine them, I don't get it to work. As soon as I change the type from Whole number to dates, the 2020 becomes a date like 1905 July 13.
Is there any way I can merge the column "Calendar year" with "Month number" or "First Characters" to get it YYYY-MM?
Or is there is any other solution.
Thank you!
Hi Mattias,
You need to add a column using the DATE function and rebuild the date using the year, month and day values e.g.:
=DATE([Calendar Year], [Month Number], )
Mynda
Hi,
Thank you. I got that working in Power BI, by adding a "1" as day, and them formatting is YYYY-MM. Is there any way to do that in Power Query, which is where I made the other changes. Would be great to make all the formatting in the same place.
Thank you
Mattias
Hi Mattias,
In Power Query the process is the same except you use the #date function e.g.:
=#date(year, month, day)
Note: the formatting should be done in Power Pivot, not Power Query though. Power Query is not the place for formatting, this is something done in the reporting side of Excel, either in Power Pivot or the Excel grid.
Mynda
