How can I create a custom column that evaluates positive values in two tables and assigns positive or negative values based on the conditions?

Question

I want to create a custom column in PowerBI/PowerQuery where if there is a positive value in Column A (Table 1) and a positive value in Column B (from Table 2). then the conditional column will also be positive. Else, it'll be negative.

I added a custom column on Table 2 with the following code:

if [#"Column B"] = "Positive" and Table1[#"Column A"] = "Positive" then "Positive" else "Negative"

It works in adding a column but all the resulting values are negative (and this would be wrong).

I've thought about duplicating the columns but all these tables are connected by unique IDs and the information in each row is specific to each ID.

The code itself seems to work since a new column is added but all values are negative. I manually checked some values to see if this is correct, but there should be a lot of final positive values.

Answer 1

Do a left-outer merge of the tables on the unique ID (Home > Merge Queries), and expand only the column needed (e.g., Column B if you are merging onto Table 1). Then your existing function should work. If you don't want Column B loaded to Table 1 in the data model, just remove it after the merge (Home > Remove Columns > Remove Columns).