Forum Discussion

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
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.

Ashish_Mathur

Super User

2 years ago

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