Jun 16 2022 05:49 AM
I need assistance with the formula to place into the custom column in excel power query. The attached flowchart shows the logic.
Thanks.
Jun 16 2022 11:11 AM
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.
Jun 16 2022 11:23 AM
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
Jun 16 2022 11:51 AM
SolutionThat 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.
Jun 17 2022 07:26 AM
Jun 17 2022 02:10 PM
@Lhansen435 , you are welcome
Jun 16 2022 11:51 AM
SolutionThat 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.