Forum Discussion
Return a value vased on dates
I am very new.
I work on a college campus and I have a form that has a date column. I want to create a column that converts the date to the semester in which that date occurs. So for example if the form was completed for January 4, 2024 that would be "Winter 2024" I am trying to figure out how to specify which dates go to which semester and then have it return the correct value for the date.
Of course 😉
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzTVNzIwMlaK1QFzjQyRuPomII4JmGOmb2gG5cUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), Semesters = Table.Buffer(Table.SelectColumns(Table.ExpandListColumn(Table.AddColumn(#table(type table[Month From=Int64.Type, Day From=Int64.Type, Month To=Int64.Type, Day To=Int64.Type, Semester=text], { {8,27, 12,19, "Fall"}, {12,20, 1,20, "Winter"}, {1,21, 5,17, "Spring"}, {5,18, 8,26, "Summer"} }), "DateCode", each [ from = #date(if [Semester]="Winter" then 2019 else 2020, [Month From], [Day From]), to = #date(2020, [Month To], [Day To]), dates = List.Dates(from, Duration.Days(to-from)+1, #duration(1,0,0,0)), dateToNumber = List.Transform(dates, (x)=> Number.From(Date.ToText(x, "MMdd")) ) ][dateToNumber], type list ), "DateCode"), {"DateCode", "Semester"})), ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}, "en-US"), Ad_Semester = Table.AddColumn(ChangedType, "Semester", each [ a = Number.From(Date.ToText([Date], "MMdd")), b = Semesters{[DateCode = a]}[Semester], c = b & " " & Text.From(if b = "Winter" and a >= 1219 then Date.Year(Date.AddYears([Date], 1)) else Date.Year([Date]) ) ][c], type text) in Ad_Semester
4 Replies
Hi sdzolin, try this:
Result
If you have different semesters, edit them here:
Left number is semester starting month, right number is semester ending month. Do not overlap months!
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzTVNzIwMlaK1QFy9U1AHBMwx0zf0AzKiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), Semesters = Table.RemoveColumns(Table.ExpandListColumn(Table.AddColumn(#table(type table[Month From=Int64.Type, Month To=Int64.Type, Semester=text], { {9, 1, "Winter"}, {2, 6, "Summer"} }), "Months", each if [Month From] > [Month To] then {[Month From]..12} & {1..[Month To]} else {[Month From]..[Month To]}, type list), "Months"), {"Month From", "Month To"}), ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}, "en-US"), Ad_Semester = Table.AddColumn(ChangedType, "Semester", each Semesters{[Months = Date.Month([Date])]}[Semester] & " " & Text.From(Date.Year([Date])), type text) in Ad_SemesterHow would this work if the crossovers are mid-month. For example for the 2023-2024 academic year the dates are as follows
Fall 2023 = Aug 27 - Dec 19
Winter 2024 = Dec 20 - Jan 20
Spring 2024 = Jan 21- May 17
Summer 2024 = May 18 - Aug 24
Of course 😉
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzTVNzIwMlaK1QFzjQyRuPomII4JmGOmb2gG5cUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), Semesters = Table.Buffer(Table.SelectColumns(Table.ExpandListColumn(Table.AddColumn(#table(type table[Month From=Int64.Type, Day From=Int64.Type, Month To=Int64.Type, Day To=Int64.Type, Semester=text], { {8,27, 12,19, "Fall"}, {12,20, 1,20, "Winter"}, {1,21, 5,17, "Spring"}, {5,18, 8,26, "Summer"} }), "DateCode", each [ from = #date(if [Semester]="Winter" then 2019 else 2020, [Month From], [Day From]), to = #date(2020, [Month To], [Day To]), dates = List.Dates(from, Duration.Days(to-from)+1, #duration(1,0,0,0)), dateToNumber = List.Transform(dates, (x)=> Number.From(Date.ToText(x, "MMdd")) ) ][dateToNumber], type list ), "DateCode"), {"DateCode", "Semester"})), ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}, "en-US"), Ad_Semester = Table.AddColumn(ChangedType, "Semester", each [ a = Number.From(Date.ToText([Date], "MMdd")), b = Semesters{[DateCode = a]}[Semester], c = b & " " & Text.From(if b = "Winter" and a >= 1219 then Date.Year(Date.AddYears([Date], 1)) else Date.Year([Date]) ) ][c], type text) in Ad_SemesterYou can create a table, and then use that to lookup the relevant date.
In the code below I hard-coded the table, but there are many ways to create it.
The table has three columns Semester | dtStart | dtEnd.
For example, as a function (named: fnSemester)
(dt as date)=> let semesterTable= Table.FromRecords( {[Semester="Fall 2023", dtStart=#date(2023,8,27), dtEnd = #date(2023,12,19)], [Semester="Winter 2024", dtStart=#date(2023,12,20), dtEnd=#date(2024,1,20)], [Semester="Spring 2024", dtStart=#date(2024,1,21), dtEnd=#date(2024,5,17)], [Semester="Summer 2024", dtStart=#date(2024,5,18), dtEnd=#date(2024,8,24)]}, type table[Semester=text, dtStart=date, dtEnd=date]), pickSemester = Table.AddColumn(semesterTable,"Date Semester", each dt >= [dtStart] and dt <= [dtEnd], type logical) in try Table.SelectRows(pickSemester, each [Date Semester]=true)[Semester]{0} otherwise "Not in Current Table"and then, in the main code body
let Source = Table.FromColumns( {{#date(2023,11,30), #date(2024,1,15),#date(2024,9,20)}}, type table[Dates=date] ), #"Invoked Custom Function" = Table.AddColumn(Source, "Semester", each fnSemester([Dates]), type text) in #"Invoked Custom Function"Result
