How to create custom column in calendar table for 9 day rotating shifts

Hi Elyse808,

Posting this option as I was 3/4 through it when I saw edhans had replied.

PBIX attached. 

Essentially the same with a slightly different approach.

The lookup table was manually entered in PBI Desktop.

The last four steps of the date table is adding index, using modulo, and merging the lookup table with the result.

Lookup:

// lookup
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIsLlGK1YlWMgKyjfJSwGxjJHETINu4CCJuiiRuhiRujqTXAkncEiYeCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}})
in
    #"Changed Type"

Date:

// Date
let
  Source = {Number.From(Date.From("2016-01-01"))..Number.From(Date.AddYears(DateTime.Date(DateTime.FixedLocalNow()), 10))},
  #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}),
  #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}),
  #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
  #"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date]), type date),
  #"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date]), type date),
  #"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([Date]), Int64.Type),
  #"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date),
  #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
  #"Inserted Month Name" = Table.AddColumn(#"Inserted End of Month", "Month Name", each Date.MonthName([Date]), type text),
  #"Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Day Name", each Date.DayOfWeekName([Date]), type text),
  #"Inserted Start of Week" = Table.AddColumn(#"Inserted Day Name", "Start of Week", each Date.StartOfWeek([Date]), type date),
  #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date]), type date),
  #"Inserted Week of Year" = Table.AddColumn(#"Inserted End of Week", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
  #"Inserted Day of Week" = Table.AddColumn(#"Inserted Week of Year", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
  #"Added Custom" = Table.AddColumn(#"Inserted Day of Week", "Current Year Start Date", each Date.StartOfYear(DateTime.Date(DateTime.FixedLocalNow())), type date),
  #"Added Custom1" = Table.AddColumn(#"Added Custom", "_isInPreviousYear", each Date.IsInPreviousYear([Date])),
  #"Added custom" = Table.AddColumn(#"Added Custom1", "Epoch Time", each Duration.TotalSeconds([Date] - #date(1970,1,1))),
  #"Marked key columns" = Table.AddKey(#"Added custom", {"Date"}, false),
  #"Transform columns" = Table.TransformColumnTypes(#"Marked key columns", {{"_isInPreviousYear", type text}}),
  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"_isInPreviousYear", null}}),

  #"Changed column type" = Table.TransformColumnTypes(#"Replace errors", {{"Epoch Time", Int64.Type}}),
  
  #"Sorted rows" = Table.Sort(#"Changed column type", {{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted rows", "Index", 0, 1, Int64.Type),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 9) + 1, type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Calculated Modulo", {"Index"}, lookup, {"id"}, "lookup", JoinKind.LeftOuter),
    #"Expanded lookup" = Table.ExpandTableColumn(#"Merged Queries", "lookup", {"value"}, {"value"})
in
    #"Expanded lookup"

See PBIX file for detail.

Let me know if you have questions.