Creating a "fulfilment table

[Richie W]

Hello

I do a lot of work with Subscription and membership files which tend to have START and STOP dates for subscriptions/membership but no concept of the months in between the dates where a subscription was valid. It can be a long winded process to create a fulfilment table. So what I would like to do in an automated way within Power Query (or equivalent for Power BI) ideally is take the following example;

Name START STOP
John Jan-14 Mar-14
James Jan-14 May-14

To create a fulfilment table

Name Month
John Jan-14
John Feb-14
John Mar-14
James Jan-14
James Feb-14
James Mar-14
James Apr-14
James May-14


The current process I have involves creating an IF/AND statement to create a count by month on a row in a worksheet which I then "Unpivot" through Power Query to create the fulfilment table.

The the file sizes I have are not huge so for example 15k subscribers and their fulfilment records by month for Jan-13 to Dec-16 convert to 670k rows in Power Query/Pivot which I can then use to model with and do usual analysis.

Help greatly appreciated!

 

[Bill Szysz]

Hi Richie W,
Try this solution. This is one of possible ways.

Regards

 

[Richie W]

Wow!

That was fast. Brilliant stuff.

Thanks so much!