SOLVED

Excel Power Query custom column formula nested if and or statement

Copper Contributor

https://lucid.app/lucidspark/df2b2fa9-f052-4ed4-988d-7658718eaa7f/edit?invitationId=inv_919df7fb-cab... 

 

I need assistance with the formula to place into the custom column in excel power query.  The attached flowchart shows the logic.  

 

Thanks.

5 Replies

@Lhansen435 

Link requires sign-up. Even if to do that, someone shall generate the model to illustrate how to receive the result.

 

Better if you share small sample file with logic explained in plain text or with screenshots from the tool.

Lhansen435_0-1655403699658.png

 

Can you read this?  Thanks for your help.  There is too much going on with this for me to keep it straight in the formula

best response confirmed by Lhansen435 (Copper Contributor)
Solution

@Lhansen435 

That could be like

    AddCustom = Table.AddColumn(Source,
        "Custom",
        each
            if List.Contains( { "D", "H", "M", "N", "P", "R", "T", "W"}, [Status] )
            then
                if [Date] > #date(2020, 12, 31)
                then
                    if ([Balance] > 0 or [Withdarwal] > 0)
                    then 4 else 5
                else 
                    if ([Balance] > 0 or [Withdarwal] > 0)
                    then 7 else 10
            else
                if List.Contains( { "A", "B"}, [Status] )
                then
                    if ([Balance] > 0 or [Withdarwal] > 0)
                    then 1
                    else 2
                else
                    if List.Contains( { "E", "F", "S"}, [Status] )
                    then 3
                    else
                        if [Staus] = "Z" then 9 else null
    )

but perhaps more effective will be make referenced tables, filter and combine back; and/or add intermediate columns for the conditions. That all could affect performance and could be critical on large files.

Thanks! You just made are lives much easier and I thank you for that.
1 best response

Accepted Solutions
best response confirmed by Lhansen435 (Copper Contributor)
Solution

@Lhansen435 

That could be like

    AddCustom = Table.AddColumn(Source,
        "Custom",
        each
            if List.Contains( { "D", "H", "M", "N", "P", "R", "T", "W"}, [Status] )
            then
                if [Date] > #date(2020, 12, 31)
                then
                    if ([Balance] > 0 or [Withdarwal] > 0)
                    then 4 else 5
                else 
                    if ([Balance] > 0 or [Withdarwal] > 0)
                    then 7 else 10
            else
                if List.Contains( { "A", "B"}, [Status] )
                then
                    if ([Balance] > 0 or [Withdarwal] > 0)
                    then 1
                    else 2
                else
                    if List.Contains( { "E", "F", "S"}, [Status] )
                    then 3
                    else
                        if [Staus] = "Z" then 9 else null
    )

but perhaps more effective will be make referenced tables, filter and combine back; and/or add intermediate columns for the conditions. That all could affect performance and could be critical on large files.

View solution in original post