cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alexanderholmes Frequent Visitor
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:

 

 

Frequency Table.PNG

 

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):Table.PNG

 

 

 

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:

 

Chart.PNG

 

Any help would be greatly appreciated and thanks in advance Smiley Happy

 

Alex

3 REPLIES 3
Community Support Team
Community Support Team

Re: Creating a table of Recurring Costs in Power Query

Hi @alexanderholmes

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

7.png

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 Frequent Visitor
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:

 

Frequency Table.PNG

 

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.

 

Table.PNG

 

Thanks, Alex

Community Support Team
Community Support Team

Re: Creating a table of Recurring Costs in Power Query

Hi @alexanderholmes

Please refer to my pbix

 

Best regards

Maggie