Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
anchal5335
Helper I
Helper I

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
v-juanli-msft
Community Support
Community Support

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

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

@anchal5335,

 

Sure thing, I did leave the code in a mess didn't I? I think the part that is confusing is that I expanded and then grouped again. #"Expanded Count1" is actually not necessary and the new code might show what's really going on a bit better.

 

Hope this explanation helps.

 

    // At #Removed Duplicates we have 1 table with 49 rows
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),

    // #Grouped Rows" takes the 1 table of 49 rows and makes 3 tables by "Researcher"
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Researcher", "Budget"}, {{"Count", each _, type table}}),

    // Custom1 is essentially doing the same thing as #"Grouped Rows" however, this time it is counting the number of records
    // Notice that I'm using the #"Removed Duplicates" step - where 1 table with 49 rows 
    Custom1 = Table.Group(#"Removed Duplicates",{"Researcher"}, {{"CountOfPeriods", each Table.RowCount(Table.Distinct(_)),type number}}),

    // Custom2 is then joining #"Grouped Rows" and "Custom1"
// This is so I can get the row count for each "Researcher" to later do the division in the row context Custom2 = Table.Join(#"Grouped Rows","Researcher",#"Custom1","Researcher"),

 

Full Code in Spoiler:

Spoiler
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"}),

// At #Removed Duplicates we have 1 table with 49 rows
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),

// #Grouped Rows" takes the 1 table of 49 rows and makes 3 tables by "Researcher"
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Researcher", "Budget"}, {{"Count", each _, type table}}),

// Custom1 is essentially doing the same thing as #"Grouped Rows" however, this time it is counting the number of records
// Notice that I'm using the #"Removed Duplicates" step - where 1 table with 49 rows
Custom1 = Table.Group(#"Removed Duplicates",{"Researcher"}, {{"CountOfPeriods", each Table.RowCount(Table.Distinct(_)),type number}}),

// Custom2 is then joining #"Grouped Rows" and "Custom1"
Custom2 = Table.Join(#"Grouped Rows","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"}),
#"Expanded Count" = Table.ExpandTableColumn(#"Removed Columns", "Count", {"Mo-Yr"}, {"Count.Mo-Yr"})
in
#"Expanded Count"

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



ChrisMendoza
Resident Rockstar
Resident Rockstar

@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






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



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?

 

@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.






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



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?

@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.






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



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.

@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.

 

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.