Forum Discussion
How to create a calendar table with multiple rows per date
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.
