Power Query Validate Calculations

Brass Contributor

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

@Castellum812 

You may add custom column like

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

@Sergei Baklan 

 

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 :(

 

 

 

Castellum812_0-1646386523687.png