Creating a table of Recurring Costs in Power Query

[alexanderholmes]

Good Afternoon All!

I was hoping someone could help provide the most efficient method of creating a Life Cycle Cost data table based on task frequency and cost data across a defined time period.

I have a table called "Task_Frequency" which contains the task frequency in days and the associated cost:

What I would like to do is create another table that would show these costs across a timeperiod, say 200 days, and then from that I can plot a chart of the costs across the time period.

I should note that the Task_Frequency table may have more or fewer rows depending on data source, so the query would need to be flexible with this.

An example of the table would be below (excluding row 1 which was for visual purposes):

In Excel I could use the following formula to determine the occurance of cost for Frequency_1:

=IF($A3/B$1=INT($A3/B$1),10,"")

Ideally, if any Days do not contain any costs then these shouldn't be present in the table as there could be task frequencies that are far and few between so there'd be many empty rows.

Here's what my expected chart would look like:

Any help would be greatly appreciated and thanks in advance 🙂

Alex

[v-juanli-msft]

Hi @alexanderholmes

With your example table (the second picture), I can easily create a chart as you expected.

It is created in the Report View, while "Power Query" refered in your title is in the "Home->edit queries" where can shape and construct data model but can't create visuals.

If you have any question, please feel free to ask me.

Best Regards

Maggie

[alexanderholmes]

Hi Maggie @v-juanli-msft,

Thanks for your input!  However, my goal is to create the data table from the Frequency_Days table:

Any method to turn the data from the above table to the below table (which I mocked up in Excel) would be greatly appreciated and then I can create the chart in report view.

Thanks, Alex

[v-juanli-msft]

Hi @alexanderholmes

Please refer to my pbix

Best regards

Maggie