Forum Discussion
Excel Power Query custom column formula nested if and or statement
https://lucid.app/lucidspark/df2b2fa9-f052-4ed4-988d-7658718eaa7f/edit?invitationId=inv_919df7fb-cab7-4619-b464-cca3f48369ec#
I need assistance with the formula to place into the custom column in excel power query. The attached flowchart shows the logic.
Thanks.
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.
5 Replies
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.
