Combine 2 tables and add missing months according to start and end date

[Ralf123]

Hello,

I would like to combine 2 tables. One with count values and the other one with start and end dates (see tables below). Now I want the result table to have the missing rows (according to start and end date) added with Count cells filled with 0 (see result table below). I already tried with Crossjoin, but then I get all the months as result and not only the ones according to the start and end date.

Anyone an idea how to do this?

Thanks

Count table

Serial	DateName	Count
x	April 2019	5
x	May 2019	4
x	June 2019	3
y	April 2019	2
y	May 2019	1
y	June 2019	7
z	May 2019	2
z	July 2019	7
z	August 2019	9

Start/end date table

Serial	StartDate	EndDate
x	April 2019	June 2019
y	April 2019	July 2019
z	May 2019	August 2019

Result table

Serial	DateName	Count
x	April 2019	5
x	May 2019	4
x	June 2019	3
y	April 2019	2
y	May 2019	1
y	June 2019	7
y	July 2019	0
z	May 2019	2
z	June 2019	0
z	July 2019	7
z	August 2019	9

[Nathaniel_C]

Hi @Ralf123 ,

In your final table you have z June. Where does that come from?

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Ralf123]

I want that to be added, just like for the y July. If you look in the start/end date table you see for y April - July and for z May - August and I want for the months that don't exist in the Count table that they are added with value 0 for Count.

[Nathaniel_C]

Hi @Ralf123 ,

You have no z for  june that I can see. The steps are in pivot query.  Here is my pbix. PBIX

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Ralf123 · Accepted solution]

@Nathaniel_C  The problem with this solution is that missing month in between the start and end dates will not be added.

I added some steps to the startEnd table to get all the months and now I have the right result. I followed this link to add the missing months

https://medium.com/dm-p/generating-rows-by-month-for-date-ranges-in-power-query-9baf62ed8e99

Thanks for your help!