SOLVED

Power Query - Start and End Dates

%3CLINGO-SUB%20id%3D%22lingo-sub-2353022%22%20slang%3D%22en-US%22%3EPower%20Query%20-%20Start%20and%20End%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2353022%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20with%20rates%20per%20company%20and%20a%20value%20per%20company.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20extend%20the%20charges%20from%20a%20Start%20Date%20to%20an%20End%20Date.%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20power%20query%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20attached.%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2353022%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2353995%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Start%20and%20End%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2353995%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3BYes%20it%20can!%3C%2FP%3E%3CP%3EAttached%20a%20PQ%20solution%20that%20perhaps%20needs%20some%20optimising%2C%20but%20it's%20totally%20dynamic.%20You%20can%20add%20companies%20and%2For%20change%20the%20dates%2C%20and%20it%20will%20be%20reflected%20when%20you%20refresh%20the%20green%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20make%20it%20a%20bit%20easier%2C%20I%20added%20a%20column%20for%20the%20cost%20calculation%20to%20the%20raw%20data%20table.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2354698%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Start%20and%20End%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2354698%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20ask%20you%20a%20follow%20up%20since%20you%20have%20great%20knowledge%20of%20PQ.%20The%20amount%20is%20set%20in%20my%20example%20but%20if%20I%20had%20another%20table%20with%20a%20list%20of%20values%20per%20month%20then%20how%20could%20I%20incorporate%20the%20%22new%20values%22%20and%20calculate%20a%20fee%20based%20on%20this%20%22new%20amount%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKindly%20see%20pic%20attached.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3Egrateful%20for%20your%20insight.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

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.

8 Replies
best response confirmed by Tony2021 (Frequent Contributor)
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. 

Wow. YOu are quite talented with PQ. Thank you for your great assistance. Enjoy the day!

@Riny_van_Eekelen 

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.

 

@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.

 

@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. 

 

@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?

Hi Riny I do have the calculations in excel but thought possibly PQ could make it easier somehow. Your answer confirms that PQ is not really meant for this and doing in excel might be easier. Thank you for your insight. Much appreciated.

@Tony2021 Well, don't take my word for it. Just my personal opinion! I wouldn't use PQ for such calculations.