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

 

DateDayRecycling Round
03/04/2023MondayREC01
03/04/2023MondayREC02
03/04/2023MondayREC03
03/04/2023MondayREC04
03/04/2023MondayREC05
03/04/2023MondayREC06
03/04/2023MondayREC07
03/04/2023MondayREC08
03/04/2023MondayREC09
03/04/2023MondayREC10
03/04/2023MondayREC11
03/04/2023MondayREC12
03/04/2023MondayREC13
03/04/2023MondaySPOV
03/04/2023MondayVAN
04/04/2023TuesdayREC01
04/04/2023TuesdayREC02
04/04/2023TuesdayREC03
04/04/2023TuesdayREC04
04/04/2023TuesdayREC05
04/04/2023TuesdayREC06
04/04/2023TuesdayREC07
04/04/2023TuesdayREC08
04/04/2023TuesdayREC09
04/04/2023TuesdayREC10
04/04/2023TuesdayREC11
04/04/2023TuesdayREC12
04/04/2023TuesdayREC13
04/04/2023TuesdaySPOV
04/04/2023TuesdayVAN
05/04/2023WednesdayREC01
05/04/2023WednesdayREC02
05/04/2023WednesdayREC03
05/04/2023WednesdayREC04
05/04/2023WednesdayREC05
05/04/2023WednesdayREC06
05/04/2023WednesdayREC07
05/04/2023WednesdayREC08
05/04/2023WednesdayREC09
05/04/2023WednesdayREC10
05/04/2023WednesdayREC11
05/04/2023WednesdayREC12
05/04/2023WednesdayREC13
05/04/2023WednesdaySPOV
05/04/2023WednesdayVAN

 

Any advice would be welcomed!  Thanks

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.

9 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    2 years ago

    That is the desired result above is it?

    What are you starting with?

  • 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"

     

     

     

     

     

  • wilbur750's avatar
    wilbur750
    Frequent Visitor
    2 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      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.

      • wilbur750's avatar
        wilbur750
        Frequent Visitor
        2 years ago

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

         

        Thanks so much!

  • Ashish_Mathur's avatar
    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

    The result is this

    Hope this helps.

     

    • wilbur750's avatar
      wilbur750
      Frequent Visitor
      2 years ago

      Hi

       

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