How to create a calendar table with multiple rows per date

[wilbur750]

Hi

How do I create a calendar table where there are multiple rows for each date?  I have 15 council recycling collection rounds that collect from residents every week day.  I am ultimately needing to calculate the number of collections per round per day.  I think I can do that from a previous task.

So am I am trying to create a calendar table that looks something like the following with a start date and end date (easy to do for single date rows).  There are 15 rows per date as there are 15 collection rounds.

Date	Day	Recycling Round
03/04/2023	Monday	REC01
03/04/2023	Monday	REC02
03/04/2023	Monday	REC03
03/04/2023	Monday	REC04
03/04/2023	Monday	REC05
03/04/2023	Monday	REC06
03/04/2023	Monday	REC07
03/04/2023	Monday	REC08
03/04/2023	Monday	REC09
03/04/2023	Monday	REC10
03/04/2023	Monday	REC11
03/04/2023	Monday	REC12
03/04/2023	Monday	REC13
03/04/2023	Monday	SPOV
03/04/2023	Monday	VAN
04/04/2023	Tuesday	REC01
04/04/2023	Tuesday	REC02
04/04/2023	Tuesday	REC03
04/04/2023	Tuesday	REC04
04/04/2023	Tuesday	REC05
04/04/2023	Tuesday	REC06
04/04/2023	Tuesday	REC07
04/04/2023	Tuesday	REC08
04/04/2023	Tuesday	REC09
04/04/2023	Tuesday	REC10
04/04/2023	Tuesday	REC11
04/04/2023	Tuesday	REC12
04/04/2023	Tuesday	REC13
04/04/2023	Tuesday	SPOV
04/04/2023	Tuesday	VAN
05/04/2023	Wednesday	REC01
05/04/2023	Wednesday	REC02
05/04/2023	Wednesday	REC03
05/04/2023	Wednesday	REC04
05/04/2023	Wednesday	REC05
05/04/2023	Wednesday	REC06
05/04/2023	Wednesday	REC07
05/04/2023	Wednesday	REC08
05/04/2023	Wednesday	REC09
05/04/2023	Wednesday	REC10
05/04/2023	Wednesday	REC11
05/04/2023	Wednesday	REC12
05/04/2023	Wednesday	REC13
05/04/2023	Wednesday	SPOV
05/04/2023	Wednesday	VAN

Any advice would be welcomed!  Thanks

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start date", type datetime}, {"End date", type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Date", each {Number.From([Start date])..Number.From([End date])}),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom1", "Date"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Date",{"Start date", "End date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Table2),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"RR"}, {"RR"})
in
    #"Expanded Custom"

The source data is

The result is this

Hope this helps.

[wilbur750]

Hi

Sorry for being daft... what is M code?  Seems you have a mixture of excel and PowerQuery?

[Ashish_Mathur]

Mashup (M for short) is the language of Power Query.

[wilbur750]

Sorry havent used tables from excel like this before, just Excel sheets.  I have recreated your tables I think and called them Start, End and RecRds in Excel.  I have loaded the 3 tables into PBI and Im now in PQ.  So do I do 'new data > blank query' then copy/paste your code?  I'm a bit confused.  Cant find any of the tables or the sheet name.

[wilbur750]

Wow!  Looks like what I need.  I'll try it tomorrow when I'm back in work.  Thanks so much!  I'll let you know how I get on

[bchager6 · Accepted solution]

@wilbur750  You're welcome. You could also simply go in to the Query Editor, right-click within the Queries pane, then select New Query, then Blank Query. Once done, select Query1, open the Advanced Editor on the Home tab then paste the code I sent yesterday over the existing code.

[wilbur750]

That works perfectly!  Now I know how to use the advanced editor as well 🙂

Thanks so much!

[bchager6]

@wilbur750  Here's a solution all done in the query editor:

Start by creating a table with your start date, then you can paste this code in the advanced editor after. I borrowed some code to create a date table from Kenneth A. Omorodion at mssqltips.com, added the 13 columns you need, then unpivoted them. You just need to add a Day column.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY30TcyMDJWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t]),
#"Added Custom" = Table.AddColumn(Source, "EndDate", each "12/31/2026"),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"EndDate", type date}}),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type2",{{"StartDate", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Dates", each {Number.From([StartDate])..Number.From([EndDate])}),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"StartDate", "EndDate"}),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Columns1",{"Dates"}),
#"Added Custom8" = Table.AddColumn(#"Removed Other Columns", "REC01", each ""),
#"Added Custom9" = Table.AddColumn(#"Added Custom8", "REC02", each ""),
#"Added Custom10" = Table.AddColumn(#"Added Custom9", "REC03", each ""),
#"Added Custom11" = Table.AddColumn(#"Added Custom10", "REC04", each ""),
#"Added Custom12" = Table.AddColumn(#"Added Custom11", "REC05", each ""),
#"Added Custom13" = Table.AddColumn(#"Added Custom12", "REC06", each ""),
#"Added Custom14" = Table.AddColumn(#"Added Custom13", "REC07", each ""),
#"Added Custom15" = Table.AddColumn(#"Added Custom14", "REC08", each ""),
#"Added Custom16" = Table.AddColumn(#"Added Custom15", "REC09", each ""),
#"Added Custom17" = Table.AddColumn(#"Added Custom16", "REC10", each ""),
#"Added Custom18" = Table.AddColumn(#"Added Custom17", "REC11", each ""),
#"Added Custom19" = Table.AddColumn(#"Added Custom18", "REC12", each ""),
#"Added Custom20" = Table.AddColumn(#"Added Custom19", "REC13", each ""),
#"Added Custom21" = Table.AddColumn(#"Added Custom20", "SPOV", each ""),
#"Added Custom22" = Table.AddColumn(#"Added Custom21", "VAN", each ""),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom22", {"Dates"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Recycling Round"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Value"})
in
#"Removed Columns"

[HotChilli]

That is the desired result above is it?

What are you starting with?