Number.Power Function in Power Query M (6 Easy Examples)

If you want to raise a number to a power in Power Query, like squaring a value or finding a square root, the Number.Power function is what you reach for. It takes a base and an exponent and returns the base raised to that exponent. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of Number.Power Function

Number.Power(number as nullable number, power as nullable number) as nullable number

where

number (required, nullable number). The base value you want to raise to a power.
power (required, nullable number). The exponent you want to raise the base to.

Returns: a number, the base raised to the given exponent. If either number or power is null, the result is null.

In plain terms, you hand it a base and an exponent, and it gives you back the base multiplied by itself that many times.

Example 1: Raise a number to a power

Raise 2 to the power of 10.

let
    Source = Number.Power(2,10)
in
    Source

Result: 1024

This is 2 multiplied by itself ten times, the same as 2^10.

Example 2: Find a square root with an exponent of 0.5

A fractional exponent computes a root. An exponent of 0.5 gives the square root.

So Number.Power(144,0.5) is the square root of 144.

let
    Source = Number.Round(Number.Power(144,0.5),4)
in
    Source

Result: 12

The raw floating-point result can run to a lot of decimals, so we wrap it in Number.Round(...,4) to keep the number tidy.

Example 3: Find a cube root with a fractional exponent

The pattern carries on for any root. An exponent of 1/3 gives the cube root, and 1/k gives the k-th root.

Here is the cube root of 27.

let
    Source = Number.Round(Number.Power(27,1/3),4)
in
    Source

Result: 3

3 cubed is 27, so the cube root is 3. We round again because 1/3 produces a long floating-point value.

Example 4: Use a negative exponent for a reciprocal

A negative exponent gives the reciprocal power. So Number.Power(2,-3) is 1 divided by 2^3.

let
    Source = Number.Power(2,-3)
in
    Source

Result: 0.125

This is 1/8, since 2^3 is 8. Negative exponents are handy for decay and discount factors.

Example 5: Apply a compound interest factor down a column

Raising a value to a power per row is a common real use. Here you apply the compound interest formula, Principal * (1+Rate)^Years, to each investment.

Here is the starting data:

Principal	Rate	Years
1000	0.05	10
5000	0.07	5
2000	0.04	20

Add a FutureValue column that raises 1+[Rate] to [Years] and multiplies by [Principal]:

let
    Source = Excel.CurrentWorkbook(){[Name="Investments"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Principal",type number},{"Rate",type number},{"Years",Int64.Type}}),
    Added = Table.AddColumn(Typed,"FutureValue",each Number.Round([Principal] * Number.Power(1+[Rate],[Years]),2)),
    Result = Table.SelectColumns(Added,{"FutureValue"})[FutureValue]
in
    Result

Number.Power(1+[Rate],[Years]) is the growth factor, and Number.Round(...,2) trims the result to two decimals so the figures read like currency. The Table.TransformColumnTypes step first makes sure the columns are numeric so the math runs cleanly.

The FutureValue column comes out as:

FutureValue
1628.89
7012.76
4382.25

Each row used its own rate and term, so Number.Power ran once per row with the values from that row. If you would rather apply the power to every item of a list, List.Transform runs your function across the list the same way.

Example 6: A negative base with a fractional exponent returns NaN

A negative base with a fractional exponent does not throw an error. It returns NaN (not a number), because there is no real square root of a negative number.

You can detect that with Number.IsNaN.

let
    Source = Number.IsNaN(Number.Power(-8,0.5))
in
    Source

Result: true

Number.Power(-8,0.5) is NaN, so Number.IsNaN reports true. Check for this before you use the value, since NaN quietly spreads through any later math.

Things to keep in mind with Number.Power

A fractional exponent is a root, not multiplication. ^0.5 is the square root, ^(1/3) is the cube root, and ^(1/k) is the k-th root (Examples 2 and 3).
Fractional-exponent results are floating point. The cube root of 27 can land at 2.9999999999999996. Wrap the call in Number.Round(...,n) when you need a clean value.
NaN does not throw, it spreads. A negative base with a fractional exponent returns NaN, and any later arithmetic on it stays NaN. Guard with Number.IsNaN (Example 6).
For a plain square root, Number.Sqrt reads clearer. Number.Sqrt(x) and Number.Power(x,0.5) give the same answer, but reach for Number.Power when you need other roots or a variable exponent.
Number.Exp is a different thing. Number.Exp(x) raises Euler’s number e to x. Use Number.Power when you choose the base yourself.
The result is a number, not formatted text. To show it with a currency symbol or fixed decimals, pass it to Number.ToText with a format string after the calculation.

Common questions about Number.Power

What is the difference between Number.Power and Number.Sqrt?

Number.Sqrt(x) only finds the square root, while Number.Power(x,0.5) does the same and also handles any other root or exponent. Use Number.Sqrt when you just want a square root and Number.Power when you need flexibility.

How do I find a cube root or other root with Number.Power?

Use a fractional exponent of 1 over the root you want. Number.Power(n,1/3) is the cube root, and Number.Power(n,1/4) is the fourth root.

List of All Power Query Functions

Related Power Query Functions / Articles: