cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anchal5335 Regular Visitor
Regular Visitor

How to extract month numbers with years between start date and end date?

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

10 REPLIES 10
ChrisMendoza Senior Member
Senior Member

Re: How to extract month numbers with years between start date and end date?

@anchal5335,

 

Not sure if this is your desired output however, you could do the following which seems capable of handling varying StartDates & EndDates:

 

let
    Query1 = #table(
 type table
    [
        #"StartDate" = date, 
        #"EndDate"= date
    ], 
 {
  {#date(2018,7,1),#date(2021,6,30)},
  {#date(2018,7,1),#date(2018,7,26)}
 }
),
    DatesBetween = Table.AddColumn(Query1, "DatesBetween", each List.Transform({Number.From([StartDate]) .. Number.From([EndDate])}, each Date.From(_))),
    #"Expanded DatesBetween" = Table.ExpandListColumn(DatesBetween, "DatesBetween"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded DatesBetween",{{"DatesBetween", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Mo-Yr", each Text.Combine({Text.From(Number.From(Date.Month([DatesBetween]))),Text.From(Number.From(Date.Year([DatesBetween])))},"-"))
in
    #"Added Custom"

4.PNG5.PNG

anchal5335 Regular Visitor
Regular Visitor

Re: How to extract month numbers with years between start date and end date?

Hi ChrisMendoza,

 

Thanks a lot for your reply. Appreciated!

 

I thought of this output too but the problem here is that I do not want each row to split into different dates. This is because-

a) I have dax and other power query calculations also that are being already performed on the basis of rows being split into monthly values. If I split each row into no of dates in between that will make others formulas also complex

b) My dataset is large so splitting each row into no of dates in between would make it even larger

 

So, isn't there any way of directly extracting month and year without splitting into dates?

 

ChrisMendoza Senior Member
Senior Member

Re: How to extract month numbers with years between start date and end date?

@anchal5335,

 

So is the expectation that you would end up with 36 rows in my example dataset; unique values for each month & year combination? I guess I do not fully understand what it is you're trying to acheive.

anchal5335 Regular Visitor
Regular Visitor

Re: How to extract month numbers with years between start date and end date?

Yes, you are right. I would like to split it into 36 rows depending only on the no. of months between the start and end date. So in that case each row should contain the unique month number with year for that particular entry.

Is there any way to achieve it?

ChrisMendoza Senior Member
Senior Member

Re: How to extract month numbers with years between start date and end date?

@anchal5335

Is there other data in this Query/Table that I've done this expansion in or is this query/table a standalone dataset? My assumption is there must be (other data) since removing duplicates and the other columns did not solve your problem.

 

Please share those other details so the community can try to assist.

anchal5335 Regular Visitor
Regular Visitor

Re: How to extract month numbers with years between start date and end date?

So it is like for every researcher who visits my organisation has a start date and end date with a certain budget assigned to each for their duration of stay. There are approximately 100 of such researchers. So if we split into dates in between it will split for each researcher individually. Now some of these researchers are coming from like 07/2018 - 06/2021 (the initial example I wrote), 06/2018 to 05/2019 etc. , (basically this duration of time varies for each researcher). So I wanted to split  the start date and end date into between months/year for each reasearcher.

ChrisMendoza Senior Member
Senior Member

Re: How to extract month numbers with years between start date and end date?

@anchal5335,

 

So removing duplicates from [Mo-Yr] & removing the extra helper columns is not an option?

 

It seems that my solution is dynamic with only hard-coding the periods start & end for each that need to be tracked then letting Power Query do the work, if the above is an option.

 

Unfortunately, I do not know how to improve your circumstance if the above isn't an option.

 

For fun, I dynamically allocated a [Budget] over the number of periods that were generated for each [Researcher]. For example, 

 

let
    Query1 = #table(
 type table
    [
        #"Researcher" = text,
        #"StartDate" = date, 
        #"EndDate"= date,
        #"Budget" = number
    ], 
 {
  {"Person 1",#date(2018,7,1),#date(2021,6,30),5000},
  {"Person 2",#date(2018,7,1),#date(2018,7,26),5000},
  {"Person 3",#date(2018,6,1),#date(2019,5,31),5000}
 }
),
    DatesBetween = Table.AddColumn(Query1, "DatesBetween", each List.Transform({Number.From([StartDate]) .. Number.From([EndDate])}, each Date.From(_))),
    #"Expanded DatesBetween" = Table.ExpandListColumn(DatesBetween, "DatesBetween"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded DatesBetween",{{"DatesBetween", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Mo-Yr", each Text.Combine({Text.From(Number.From(Date.Month([DatesBetween]))),Text.From(Number.From(Date.Year([DatesBetween])))},"-")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Researcher", "Budget", "Mo-Yr"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Researcher", "Budget"}, {{"Count", each _, type table}}),
    #"Expanded Count1" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Mo-Yr"}, {"Mo-Yr"}),
    Custom1 = Table.Group(#"Expanded Count1",{"Researcher"}, {{"CountOfPeriods", each Table.RowCount(Table.Distinct(_)),type number}}),
    Custom2 = Table.Join(#"Expanded Count1","Researcher",#"Custom1","Researcher"),
    #"Added Custom1" = Table.AddColumn(Custom2, "Allocated Budget", each [Budget]/[CountOfPeriods]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Allocated Budget", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Budget", "CountOfPeriods"})
in
    #"Removed Columns"

 

"Person 1" has 36 Mo-Yr periods so the [Allocated Budget] should be 138.889 per period

"Person 2" has 1 Mo-Yr period so the [Allocated Budget] should be 5000 per period

"Person 3" has 12 Mo-Yr period so the [Allocated Budget] should be 416.667 per period

 

 Of course, Power Query did the real work for me.

 

 

Community Support Team
Community Support Team

Re: How to extract month numbers with years between start date and end date?

Hi @anchal5335

Thaks to ChrisMendoza's solution, I made a test and found it can meet your requirement:

1.extract the month numbers with years (in power query not DAX) between a given startdate and enddate

2. for example, for a researcher, startdate="07/01/2018" enddate=""06/30/2021", only get 36 rows for this researcher.

3.png

4.png

 

 

Best Regards

Maggie

anchal5335 Regular Visitor
Regular Visitor

Re: How to extract month numbers with years between start date and end date?

Hi @ChrisMendoza and Maggie,

 

Since I am new to coding also, so I still cannot understand some steps. I understand that we first extracted the dates in between and then expanded that column but what is happening after that is not very clear to me. To be precise the steps I did not understand are:

 

#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Researcher", "Budget"}, {{"Count", each _, type table}}),
#"Expanded Count1" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Mo-Yr"}, {"Mo-Yr"}),
Custom1 = Table.Group(#"Expanded Count1",{"Researcher"}, {{"CountOfPeriods", each Table.RowCount(Table.Distinct(_)),type number}}),
Custom2 = Table.Join(#"Expanded Count1","Researcher",#"Custom1","Researcher"),

It would be great if you could also explain me the purpose of these steps so that I can also apply accordingly. Many thanks to all your help!

 

Regards,

Anchal

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 330 members 3,555 guests
Please welcome our newest community members: