Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi 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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi 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
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 ;). |
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. | Proud to be a Super User! |
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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi 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!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
33 | |
16 | |
12 | |
11 | |
9 |
User | Count |
---|---|
44 | |
24 | |
16 | |
14 | |
14 |