May 13 2021 02:51 PM
Hello,
I have a table with rates per company and a value per company.
I want to extend the charges from a Start Date to an End Date.
Can power query do this?
Please see attached.
thank you.
May 14 2021 12:43 AM
Solution@Tony2021 Yes it can!
Attached a PQ solution that perhaps needs some optimising, but it's totally dynamic. You can add companies and/or change the dates, and it will be reflected when you refresh the green table.
To make it a bit easier, I added a column for the cost calculation to the raw data table.
May 14 2021 05:43 AM
May 14 2021 05:59 AM
I would like to ask you a follow up since you have great knowledge of PQ. The amount is set in my example but if I had another table with a list of values per month then how could I incorporate the "new values" and calculate a fee based on this "new amount".
Kindly see pic attached.
grateful for your insight.
May 14 2021 07:18 AM
@Tony2021 That's very well possible. but it would be some guesswork without more information. How does that "other table" look like?
If you have tables with matching company names, you can use the merge function(s) in PQ to create a table like the one in the example, and then build further with that one.
May 14 2021 01:50 PM
@Riny_van_Eekelen Hi Riny, thanks for the response.
I have attached a file that has the dates of the increase or decrease of the amount.
Would be interested in knowing how that could be incorporated.
grateful for your assistance.
May 14 2021 11:30 PM
@Tony2021 Not sure if PQ is the right tool for this kind of work. Importing, cleaning and transforming is what PQ does best, in my opinion. You ask for a dynamic calculation of costs over time, for multiple companies. It seems to me that it is much easier done in Excel itself. any particular reason why you insist on using PQ?
May 15 2021 08:23 AM
May 15 2021 08:31 AM
@Tony2021 Well, don't take my word for it. Just my personal opinion! I wouldn't use PQ for such calculations.
May 14 2021 12:43 AM
Solution@Tony2021 Yes it can!
Attached a PQ solution that perhaps needs some optimising, but it's totally dynamic. You can add companies and/or change the dates, and it will be reflected when you refresh the green table.
To make it a bit easier, I added a column for the cost calculation to the raw data table.