Function: If and statement with numbers and dates

[joslaz]

Hey!

Unfortunatelly, I am still not good at defining own functions in power query.
May someone of you can help me.

I have the following table:

Date - Value
01.01.2018 0.75

The aim is to get for weekdays a maximum value of 0.75.
So if the value ist 0.76 and its mo, it should get a value of 0.75
For the weekend the maximum number ist 0.8.

Of course, these could be management with some conditional columns but I am looking for a more elegent way like a own function.

Maybe someone have any idea.



Best regards

 

[ehans]

I believe this will do what you want.

 if Date.DayOfWeek([Date], Day.Monday) < 5 then if [Amount] <= .75  then [Amount] else .75 else if Date.DayOfWeek([Date], Day.Monday) >= 5  then if [Amount] <= .8 then [Amount] else .8 else 0

That will assign a number 0-6 to the day of the week it is. Sat/Sun are 5/6. I told it to start Monday at 0.

 

[joslaz]

It works perfect! Thanks a lot!

Do you know, how to adapt the code on a duration type of values?
Like: if Date.DayOfWeek([Date], Day.Monday) < 5 then if [Amount] <= 00.07:45:00 then [Amount] else 00.07:45:00 else if Date.DayOfWeek([Date], Day.Monday) >= 5 then if [Amount] <= 00.08:45:00 then [Amount] else 00.08:45:00 else 0

 

[ehans]

You should be able to use the #duration () function. So #duration (0,8,45,0) would be 8hrs and 45min. ​

See this for more info

.​

 

[joslaz]

Thanks a lot!

But this approach doesnt work:

if Date.DayOfWeek([Date], Day.Monday) < 5 then if [Amount] <= #duration (0,8,45,0) then [Amount] else #duration (0,8,45,0) else if Date.DayOfWeek([Date], Day.Monday) >= 5 then if [Amount] <= #duration (0,9,45,0) then [Amount] else #duration (0,9,45,0) else 0
Does anyone know, where the problem is?