Creating a new column using parameter list of values in power query

[Dunner2020]

Hi there,

I got a table which contains the name of the staff. I want to create a new column that contains either 0 or 1. 1 means staff is from the active list and 0 means staff is not. To create a new column, I first created a text parameter that has a list of values. I entered the name of all active staff names into the parameter and set one staff name as the current value.  

I created a conditional column and change the operator from equal to contains and select the parameter name in the value as shown in the figure:

The problem is that it only returns 1 when the staff name matches to the current value of the parameter. It does not return the 1 for other staff name present in the parameter list. Could anyone guide me where I am making the mistake?

[v-easonf-msft · Accepted solution]

Hi, @Dunner2020 

It is not suitable to use parameters here, it is recommended to use ‘list’.

Sample data:

Step1:add a custom column to convert the table2 to a list

Step2: You can use the function 'List.combine' to determine if the row value  exists in the list

You can also consider creating a calculated column:

Result_Dax = 
var tab=CALCULATE (
    COUNTROWS ( Table2 ),
    FILTER (
        Table2,
        Table2[Registeration Officers] = EARLIER ( Table1[Case Owner] )
    )
)
return IF(tab>=1,1,0)

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason

[sevenhills]

Just curious why you did not try this approach

a) Create a table of values , "Enter Data" and have the columns as "Name", "Active".
         Values for active is 1 or 0. .

b) Merge the two tables and get the value for matching ones and the active status

c) Optional: Replace all status those are nulls as zero

Thanks