Mar 03 2022 04:39 AM
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
Mar 03 2022 05:03 AM
Mar 04 2022 01:39 AM
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 :(