cancel
Showing results for
Did you mean:
Frequent Visitor

## Creating a table of Recurring Costs in Power Query

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

3 REPLIES 3
Community Support Team

## Re: Creating a table of Recurring Costs in Power Query

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.

Best Regards

Maggie

Frequent Visitor

## Re: Creating a table of Recurring Costs in Power Query

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

Community Support Team