cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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.

 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

1 ACCEPTED SOLUTION
Super User

@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 9
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}}),
#"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}}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"RR"}, {"RR"})
in
#"Expanded Custom"``````

The source data is

The result is this

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Hi

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

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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.

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

Super User

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

Frequent Visitor

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

Thanks so much!

Super User

@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]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"EndDate", type date}}),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type2",{{"StartDate", type date}}),
#"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"}),
#"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"

Super User

That is the desired result above is it?

What are you starting with?

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors