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

[Elyse808]

Hi,

I'm new to powerBI and am looking to create a Calendar table with a common Fire Service Schedule (Kelly Schedule) which is a repeating 9 day cycle representing Platoons.  Below is a sample of the columns I have so far.  I'm trying to create a column that has {1st, 2nd, 1st, 3rd, 1st, 3rd, 2nd, 3rd, 2nd} in the first 9 rows, and then that same pattern the next 9 rows, continued on for the duration of this table.  1st would be in row 1, 2nd in row 2, 1st in row 3, etc.

Anyone's assistance would be greatly appreciated!

[edhans]

Basically you wll add a comma to your final step, then add this code (I picked up with your final else clause. Delete everything below that and add this:

    ...
    else [Year]),
    varRowCount = Table.RowCount(#"Added Custom2"),
    varListNeeded = 
        List.FirstN(
            List.Repeat(tblCycle, Number.RoundAwayFromZero(varRowCount / 9)),
            varRowCount
        ),
    CombinedWithCycleList = Table.ToColumns(#"Added Custom2") & {varListNeeded},
    BackToTable = 
        Table.FromColumns(
            CombinedWithCycleList, 
            Table.ColumnNames(#"Added Custom2") & {"Cycle"}
        )
in
    BackToTable

This assumes the Cycle data (1st, 2nd, etc) is in a list called "tblCycle" - see how I did it in Excel.

If you have a file you can share, I can help you connect it. Or you might look at the blog post at the end of these directions.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Elyse808]

Hi edhans,

I added the M code to the end of the current code, but I'm getting a Syntax Error (Token Comma Expected).  I'm unable to attache the file b/c I think you have to be a Super User to do so?  Here are a few more screenshots of what I'm experiencing. 

Thank you

Elyse

[edhans]

You have to add a comma after your else [Year]) line. Look again at my code.

That is the break telling it there is a difference between your #"Added Custom2" step and the varRowCount step.

[Elyse808]

Hi edhans,

Sorry for the delay I got pulled into another project.  I am now getting an error that says, "Expression.Error: the Import Added Custom2 matches no exports.  Did you miss a module reference?"  

Could you please assist

Thank you,

Elyse

[KNP]

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.

[edhans]

Hi @Elyse808 

Here is what I came up with:

Here is the Excel file to see what I did. You can use the same Power Query logic in Power BI.

The cycle column is just a list. You can see it in Excel, and then I right-clicked on it and selected Drill Down. For performance reasons (not knowing how big your data is, I wrapped that with List.Buffer(). )

This is called tblCycle and will be used later.

let
    Source = Excel.CurrentWorkbook(){[Name="tblCycle"]}[Content],
    Cycle = List.Buffer(Source[Cycle])
in
    Cycle

Now back to the date table. This is the full code:

let
    Source = Excel.CurrentWorkbook(){[Name="tblDate"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
    varRowCount = Table.RowCount(#"Inserted Month Name"),
    varListNeeded = 
        List.FirstN(
            List.Repeat(tblCycle, Number.RoundAwayFromZero(varRowCount / 9)),
            varRowCount
        ),
    CombinedWithCycleList = Table.ToColumns(#"Inserted Month Name") & {varListNeeded},
    BackToTable = 
        Table.FromColumns(
            CombinedWithCycleList, 
            Table.ColumnNames(#"Inserted Month Name") & {"Cycle"}
        )
in
    BackToTable

The stuff through Inserted Month Name is pretty standard.

• varRowCount counts the number of rows in the date table.
• varListNeeded takes the first N items (the row count) from the list after I have repeated the list divided by 9 and rounded up. So this model has 29 days or something. 29/9 = 3.3 (estimate) and it rounded up to 4, so gave me 36 in the list, the list repeating 4 times. I then took the first 29 values, so now my list is the same length as the table.
• CombinedwithCycleList converts the date table at the #"Insterted Month Name" to a series of lists. It looks weird because every column is now a list.

The list on line 1 is the dates, the list on line 2 is the year, etc. the list on line 5 is the Cycle list I added.

• BackToTable simply converts those lists back to a table, and pulls the field names from the original #"Inserted Month Name" table and I hardcoded the cycle list as "Cycle"

[Elyse808]

Hi edhans,

Thank you for your quick response.  I apologize but i'm trying to add the final few steps in your coding to what I already have, and am getting stuck.  I created a tblCycle as you described (I created it in PowerBI, not in excel).  Here is what I already have for the Calendar table that I generated.  

I'm looking to add the "Cycle" column to the Right of "Fiscal Year" in my table.

Thank you!