Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
@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.
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.
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.
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.
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
@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!
@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"
That is the desired result above is it?
What are you starting with?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |