Power Query Validate Calculations

%3CLINGO-SUB%20id%3D%22lingo-sub-3245090%22%20slang%3D%22en-US%22%3EPower%20Query%20Validate%20Calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3245090%22%20slang%3D%22en-US%22%3E%3CP%3EHI%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20input%20sheet%20containing%20data%20from%20a%20CRM%3C%2FP%3E%3CP%3EFor%20a%20report%20I%20want%20to%20calculate%20remainig%20hours.%20For%20this%20I%20use%20two%20entrys%20%2F%20Numbers%20in%20tha%20data%20sheet.%3C%2FP%3E%3CP%3EA%20%2F%20B%26nbsp%3B%20%3D%20C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20can%20be%20a%20couple%20of%20situation%3C%2FP%3E%3CP%3EA%20can%20be%20positive%20Value%2C%20Negative%20Value%3C%2FP%3E%3CP%3EB%20can%20be%20zero%20or%20a%20positive%20value%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EResults%20I%20want%20to%20achieve%3C%2FP%3E%3CP%3EIf%20A%20%3D%20Negative%20value%20%3D%20C%20should%20be%200%3C%2FP%3E%3CP%3EIf%20A%20%3D%200%20then%20C%20should%20be%200%3C%2FP%3E%3CP%3EIf%20A%20%3D%20a%20positive%20Value%20C%20should%20be%20%26gt%3B0%3C%2FP%3E%3CP%3EIf%20B%20%3D%200%20then%20C%20should%20be%20zero.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%20the%20output's%20for%20C%20are%3C%2FP%3E%3CP%3EInfinity%20(%20when%20B%3D%20Zero)%3C%2FP%3E%3CP%3ENaN%20(when%20A%20%3D%20Zero)%3C%2FP%3E%3CP%3EValue%20%26lt%3B%200%20(when%20A%20%3D%20Negative%20B%20%3D%20Positive)%3C%2FP%3E%3CP%3EValue%20%26gt%3B%200%20(when%20A%20%3D%20Positive%20B%20%3D%20Positive)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20no%20problems%20creating%20this%20in%20normal%20sheet%20calculation%20but%20in%20Power%20Query%20I%20am%20still%20searching%20for%20the%20most%20efficient%20way.%3C%2FP%3E%3CP%3EShould%20I%20use%20the%20Custom%20Column%20functions%3F%3C%2FP%3E%3CP%3EOr%20is%20the%20best%20way%20to%20replace%20values%20with%20the%20PQ%20%22Transform%22%20%22Replace%20Values%22%20function%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3245090%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3245198%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Validate%20Calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3245198%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1250718%22%20target%3D%22_blank%22%3E%40Castellum812%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20add%20custom%20column%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E%3Dif%20A%20%26lt%3B%3D%200%20or%20B%20%3D%200%0A%20then%20C%20%3D%200%0A%20else%20Number.Abs(A%2FB)%0A%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
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