Trying to sum 2 columns into a new one if a third is zero

[PowerQuairy]

I have a sheet loaded into Power Query. The columns I am working with are New Funds, Active Principal Balance, and Available Credit. I am attempting to add a new column Full Loam Amount which would be populated as follows.

=if New Funds =0,(sum(Active Principal Balance,Available Credit), New Funds

If New funds has any dollar amount, use it in the new column. If it's zero, add Active Principal Balance and Available Credit, use that for the new column

.

Here is what I tried:

= Table.AddColumn(#"Uppercased Text", "Final Amount", each if [New Funds] = 0 then List.Sum([Active Principal Balance],[Available Credit]) else [New Funds])

Here is the error (6395.05 is the first value in the Active Principal Balance cell)

Expression.Error: We cannot convert the value 6395.05 to type List.

Details:

    Value=6395.05

    Type=[Type]

Current Data Types for each:

New Funds=Whole Number

Active Principal Balance= Decimal Number

Available Credit = Decimal Number

[v-jingzhang · Accepted solution]

Hi @PowerQuairy 

You can use + to add two columns directly

= Table.AddColumn(#"Changed Type", "Final Amount", each if [New Funds] = 0 then [Active Principal Balance] + [Available Credit] else [New Funds])

Or use List.Sum. Use {} to surround two values to convert it into a list to be used in the function. 

= Table.AddColumn(#"Changed Type", "Final Amount", each if [New Funds] = 0 then List.Sum( { [Active Principal Balance], [Available Credit] } ) else [New Funds])

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[edhans]

If you just want to add them together, use [Active Principal Balance] + [Available Credit].
That will add the values for each record together, like A1+B1.

If you want the entire column (list) then you use List.Sum, but it only takes one argument, so you have to:

1. Convert each column to a list
2. combine the lists to a single list
3. embed that in List.Sum

This formula will do that:

List.Sum(List.Combine({#"Changed Type"[Column1], #"Changed Type"[Column2]}))

You have to refer to the previous step (mine was #"Changed Type" to work, so it is saying to the #"Changed Type" table (prev step) and get [Column1]. That returns a list. Then it does the same for column2. Those are combined to a single list, then List.Sum will add them up.

That seems really odd though because you will get the same value for every record - the grand total of those two columns.

If you need more help, please post sample data and expected output per directions below.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.