Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
wilbur750
Frequent Visitor

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

1 ACCEPTED SOLUTION
bchager6
Impactful Individual
Impactful Individual

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

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

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

Ashish_Mathur_0-1699583883912.png

The result is this

Ashish_Mathur_1-1699583907862.png

Hope this helps.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi

 

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Frequent Visitor

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
Impactful Individual
Impactful Individual

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

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

 

Thanks so much!

bchager6
Impactful Individual
Impactful Individual

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

 

 

bchager6_0-1699569244860.png

 

 

 

HotChilli
Super User
Super User

That is the desired result above is it?

What are you starting with?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.