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
dufoq3's avatar
dufoq3
Community Champion
2 years ago

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_Semester