 # Power Query Validate Calculations

HI there,

I have an input sheet containing data from a CRM

For a report I want to calculate remainig hours. For this I use two entrys / Numbers in tha data sheet.

A / B  = C

There can be a couple of situation

A can be positive Value, Negative Value

B can be zero or a positive value

Results I want to achieve

If A = Negative value = C should be 0

If A = 0 then C should be 0

If A = a positive Value C should be >0

If B = 0 then C should be zero.

Currently the output's for C are

Infinity ( when B= Zero)

NaN (when A = Zero)

Value < 0 (when A = Negative B = Positive)

Value > 0 (when A = Positive B = Positive)

I have no problems creating this in normal sheet calculation but in Power Query I am still searching for the most efficient way.

Should I use the Custom Column functions?

Or is the best way to replace values with the PQ "Transform" "Replace Values" function

2 Replies

# Re: Power Query Validate Calculations

You may add custom column like

``````=if A <= 0 or B = 0
then C = 0
else Number.Abs(A/B)
``````

# Re: Power Query Validate Calculations

Thanks

Tht solved most of my questions

But the one below somehow does not work.

Dont understand why,

``if Duration.Days ([End date]- DateTime.Date(DateTime.LocalNow()) > 0 then Number.Round(Duration.Days ([End date]- DateTime.Date(DateTime.LocalNow()))/30.4) else 0``

The error it gives is "Token comma expected." Pointing at the "Number.Round" formula.

If I leave out the "If's" the function works fine.

``Number.Round(Duration.Days ([End date]- DateTime.Date(DateTime.LocalNow()))/30.4)``

Must be a syntax thing but I have not been able to pin point it 