CAGR rate in Excel Power Query

[Syndicate_Admin]

Hi,

I'm trying to calculate the CAGR rate using power query but running into a brick wall!  In PowerBi I believe this is the correct formula...

CAGR = ([Ending Value]/[Beginning Value])^(1/[# of Years])-1

 ....but in Excel, Power Query doesn't seem to recognise the ^ symbol.  Has anyone had any success calculating CAGR through Power Query before, can't find much about it online!

Thanks

[Syndicate_Admin · Accepted solution]

Found a solution, for anyone else who may ever be looking for one!

Used the Number.Power function under the Scientific option and just changed the formula 

Table.AddColumn(#"Removed Columns1", "Power", each Number.Power([End]/[Start], 1/[CAGR Yrs])-1, Int64.Type)

[AlexisOlson]

This looks fine except for Int64.Type. I don't think your CAGR will always be an integer.