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 between a given startdate and enddate andI 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

1 REPLY 1
Moderator v-qiuyu-msft
Moderator

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

Hi @anchal5335,

 

You can create a function fnGetAllocationBase below: 

 

(startdate as date, enddate as date) as table =>
let
 //startdate=#date(2015,8,1),
 //enddate=#date(2016,1,31),
 Source = {Number.From(startdate)..Number.From(enddate)},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Calculated End of Month" = Table.TransformColumns(#"Renamed Columns",{{"Date", Date.EndOfMonth, type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Calculated End of Month"),
    #"Inserted End of Year" = Table.AddColumn(#"Removed Duplicates", "EndOfYear", each Date.EndOfYear([Date]), type date),
    #"Grouped Rows" = Table.Group(#"Inserted End of Year", {}, {{"Original", each _, type table}, {"Months_Total", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

Then call the function: 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcrBCcAwDEPRXXSOQHLAbWcx2X+NOiS3/+FVwRgI+eXDk2Emp7o/YY1CHJH0FW4qzj2WGq0f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, BEGIN = _t, END = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"BEGIN", type date}, {"END", type date}, {"VALUE", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fnGetAllocationBase([BEGIN],[END])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"BEGIN", "END"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Original", "Months_Total"}, {"Custom.Original", "Custom.Months_Total"}),
    #"Expanded Custom.Original" = Table.ExpandTableColumn(#"Expanded Custom", "Custom.Original", {"Date", "EndOfYear"}, {"Custom.Original.Date", "Custom.Original.EndOfYear"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom.Original", "Year", each Date.Year([Custom.Original.Date])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Month", each Date.Month([Custom.Original.Date]))
in
    #"Added Custom2"

q9.PNGq8.PNG

 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.