Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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"
Best Regards,
Qiuyun Yu
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |