Forum Discussion

sdzolin's avatar
sdzolin
New Member
2 years ago
Solved

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 ...
Need Help
Tips and Tricks
Tutorial Requests
  • dufoq3's avatar
    dufoq3
    2 years ago

    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
sdzolin's avatar
sdzolin
New Member
2 years ago

How 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

ronrsnfld's avatar
ronrsnfld
Icon for Super User rankSuper User
2 years ago

You 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