Hi Guys,
I wanted to extract the month numbers with years (in power query not DAX) between a given startdate and enddate and I don't want the dates in between but just the month and years extracted together.
For example - I have been given startdate="07/01/2018" enddate=""06/30/2021" (I have many other entries like this but with other combinations)
Initially I thought of using Date.Month(Startdate)..Date.Month(enddate), but then I realized that this function only works when the years of start dates and end dates are same. So when the years change like in my eg this function stops working.
So in order to extract months using this function I had to create four different columns and I also used list.transform function to add the year with the month value. It has been done as follows-
column 1 - extracted the months and year (2018) between startdate and end of year 2018
column 2 - extracted the months and year (2019) between 1 Jan 2019 and 31 Dec 2019
column 3 - extracted the months and year (2020) between 1 Jan 2020 and 31 Dec 2020
column 4 - extracted the months and year (2021) between 1 Jan 2021 and enddate
The function used here is (this code is for column 1, similarly I wrote for other columns by hardcoding the dates value)-
List.Transform(
{Date.Month([StartDate0])..Date.Month(Date.EndOfYear([StartDate0]))},
each Number.ToText(_) & "/2018")
I also used many if-else conditions with each of these to account for other combinations. Then finally I combined all the lists into one and then expanded the final list.
For the time being my work is done and its working but I know that this solution is temporary as it involves a lot of hardcoding and can only take into into account specific entries.
So is there any other better way of solving this problem? I have been struggling on this since past few days!
Any kind of help would be greatly appreciated.
Thanks,
Anchal
