Breaking data model up

[evantse]

Hey all,

I have a pretty niche issue where the data being capture is not fit for Power BI reporting purposes.

Currently, we are capturing procurement activities and three separate types of value that they are adding over time. This is captured in a table like:

Activity	Start date	Value add	Value add months	Cost saving	Cost saving months
Procurement 1	1/01/2019	12,000	12	0	0
Procurement 2	1/02/2019	10,000	10	10,000	10
Procurement 3	1/03/2019	0	0	36,000	36
Procurement 4	1/04/2019	20,000	10	20,000	10
Procurement 5	1/05/2019	15,000	15	0	0

We are looking to create reports based on the amount of value being recognised per month, per project like the following:

Value Add

Activity	01-19	02-19	03-19	04-19	05-19	…
Procurement 1	1,000	1,000	1,000	1,000	1,000	…
Procurement 2	0	1,000	1,000	1,000	1,000	…
Procurement 3	0	0	0	0	0	…
Procurement 4	0	0	0	2,000	2,000	…
Procurement 5	0	0	0	0	1,000	…

This table is required so that we are able to report on financial benefits procurement activities per financial year.

I am at a loss at to how to convert the first table into the second one. The main issue I am facing is that I need to create the second table so that it covers the life of the activity and also for future activities.

[parry2k]

@evantse this can be done, how big is the dataset?

[evantse]

Wow thanks for such a quick response @parry2k !

I don't quite know how to answer how big the data set is. I have attached a sample export of the datasource that we are importing from SharePoint, hopefully that helps.

There is roughly 80 rows from 6months of usage.

[parry2k · Accepted solution]

@evantse solution is attached, most of the work is done in power query

[evantse]

@parry2k  Amazing solution thanks!

I've tweaked it a bit further to break down the other columns using your concept as a basis.