Help converting list of items with date range into a table for each occurrence per date

[Coltella8013]

Hi. I am fairly new to PowerBI and am trying to work out the best approach to achieveing the following and would appreciate some assistance?

Table Source Data Example:

Item	Type	Start Date	End Date
Item 1	Type 1	01/01/2021	10/01/2021
Item 2	Type 2	03/01/2021	10/01/2021
Item 3	Type 1	05/01/2021	10/01/2021
Item 4	Type 1	07/01/2021	08/01/2021

End Goal - Count how many Items occurred within a slidable date range using an existing Date table.

I am looking to try achieve the following (a row item per date per item) so that I can pivot by Type and date slider.

01/01/2021	Item 1	Type 1
02/01/2021	Item 1	Type 1
03/01/2021	Item 1	Type 1
03/01/2021	Item 2	Type 2
04/01/2021	Item 1	Type 1
04/01/2021	Item 2	Type 2
05/01/2021	Item 1	Type 1
05/01/2021	Item 3	Type 1
05/01/2021	Item 2	Type 2
06/01/2021	Item 1	Type 1
06/01/2021	Item 3	Type 1
06/01/2021	Item 2	Type 2
07/01/2021	Item 1	Type 1
07/01/2021	Item 3	Type 1
07/01/2021	Item 4	Type 1
07/01/2021	Item 2	Type 2
08/01/2021	Item 1	Type 1
08/01/2021	Item 3	Type 1
08/01/2021	Item 4	Type 1
08/01/2021	Item 2	Type 2
09/01/2021	Item 1	Type 1
09/01/2021	Item 3	Type 1
09/01/2021	Item 2	Type 2
10/01/2021	Item 1	Type 1
10/01/2021	Item 3	Type 1
10/01/2021	Item 2	Type 2

Any help would be much appreciated.

Regards.

Nigel

[Payeras_BI · Accepted solution]

Hi @Coltella8013 ,

In PQ editor go to Add Custom Column and paste the code below: 

List.Dates([Start Date], Duration.Days([End Date] - [Start Date]), #duration(1, 0, 0, 0))

Now you can already remove Start and End Date columns and click on "Expand to New Rows":

Regards,

[v-deddai1-msft · Accepted solution]

Hi @Coltella8013 ,

You can create a custom column to get list from start date and end date:

{Number.From([Start Date])..Number.From([End Date])}

Then expand list to rows and change the type to date:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Payeras_BI · Accepted solution]

Hi @Coltella8013 ,

In PQ editor go to Add Custom Column and paste the code below: 

List.Dates([Start Date], Duration.Days([End Date] - [Start Date]), #duration(1, 0, 0, 0))

Now you can already remove Start and End Date columns and click on "Expand to New Rows":

Regards,