Very complicated each if / else if statement in M - need syntax help

[grggmrtn]

Hi!

Right now my code looks like this, and works just fine:

    #"Tilføj Afregning" = Table.AddColumn(#"Udvidet Previous", "Afregning", each if
        [PersonID] = [Previous.PersonID] and
        [Service] = [Previous.Service] and 
        [Provider] = [Previous.Provider] and 
        [Date] = [Previous.Date] +1 and 
        [Day] = "Tuesday" and 
        [Previous.Day] = "Monday" and 
        [Previous.Date] <> [Stopdate] and 
        (
            (
                [Pause] = 0 and 
                [Previous.Pause] = 0
            )
            or 
            (
                [Pause] = 0 and
                [Previous.Pause] = 1
            )
            or 
            (
                [Pause] = 1 and 
                [Previous.Pause] = 0
            )
    )
    then
    1
    else
    0,

I need to modify it according to a new variable I have received, preferably without having to create yet another column.

Basically, I need to enter the following after [Afregning] is determined to be 1 or 0:

If [Service] = [NameOfService] and
[Frequency] = 1
then [Afregning] = [Afregning * 2]

[Frequency] is the new variable and only needs to be taken into account for one [Service]

I was thinking about just adding an "else if" after "then 1" to make a "then 2", but I'm not sure if I'd have to add all of the previous if/and to the else if?

The report is heavy as is, so I'd love to be able to find a solution that wouldn't have me needing to leave the computer on at night when I update the code 😉

Thanks in advance!

[dax]

Hi grggmrtn,

Sccording to your description, it seems that you calculate next expression based on first expression's value, right? If so, you could try to nest your result in expression  like below to see whether it work or not

#"Tilføj Afregning" = Table.AddColumn(#"Udvidet Previous", "Afregning", each If [Service] = [NameOfService] and
[Frequency] = 1
then 2*(
 if
        [PersonID] = [Previous.PersonID] and
        [Service] = [Previous.Service] and 
        [Provider] = [Previous.Provider] and 
        [Date] = [Previous.Date] +1 and 
        [Day] = "Tuesday" and 
        [Previous.Day] = "Monday" and 
        [Previous.Date] <> [Stopdate] and 
        (
            (
                [Pause] = 0 and 
                [Previous.Pause] = 0
            )
            or 
            (
                [Pause] = 0 and
                [Previous.Pause] = 1
            )
            or 
            (
                [Pause] = 1 and 
                [Previous.Pause] = 0
            )
    )
    then
    1
    else
    0) else 0

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[grggmrtn]

hi @dax 

Sorry, I may have explained myself wrong. There are many services, and for all but two of them the [Frequency] may have any value. But for two of them I need a different "Afregning" number if [Frequency] = 1, otherwise I need the original number.

If I'm reading your code correctly it starts by filtering out the service where I need the frequency, but doesn't bring the other services in as well?

[Zubair_Muhammad · Accepted solution]

@grggmrtn 

Try storing the code in a variable first

Something on these lines

#"Tilføj Afregning" = Table.AddColumn(#"Udvidet Previous", "Afregning", each 
let myvar=(if
        [PersonID] = [Previous.PersonID] and
        [Service] = [Previous.Service] and 
        [Provider] = [Previous.Provider] and 
        [Date] = [Previous.Date] +1 and 
        [Day] = "Tuesday" and 
        [Previous.Day] = "Monday" and 
        [Previous.Date] <> [Stopdate] and 
        (
            (
                [Pause] = 0 and 
                [Previous.Pause] = 0
            )
            or 
            (
                [Pause] = 0 and
                [Previous.Pause] = 1
            )
            or 
            (
                [Pause] = 1 and 
                [Previous.Pause] = 0
            )
    )
    then
    1
    else
    0)
in
(if [Service] = [NameOfService] and
[Frequency] = 1
then myvar*2 else myvar),