Forum Discussion

wilbur750's avatar
wilbur750
Frequent Visitor
2 years ago
Solved

How to create a calendar table with multiple rows per date

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 needin...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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"