Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Elyse808
Regular Visitor

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

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!

 

BITableCalendar.PNG

7 REPLIES 7
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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. 

 

Calendar code.PNGCycle LIst.PNG

 

Thank you

Elyse

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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
Super User
Super User

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.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
edhans
Super User
Super User

Hi @Elyse808 

Here is what I came up with:

edhans_0-1664499402236.png

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.

edhans_1-1664499559513.png

 

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.

edhans_2-1664499812437.png

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"

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.  

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

 

Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors