Forum Discussion
Excel Power Query custom column formula nested if and or statement
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.
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.
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
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.
Lhansen435 , you are welcome