Creating a "fulfilment table

Richie W

New member
Joined
Sep 20, 2016
Messages
2
Reaction score
0
Points
0
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!
 
Wow!

That was fast. Brilliant stuff.

Thanks so much!
 
Back
Top