Difference between two list of values

[nimeshshah]

Hi,

I am trying to add a new column to the table below which would have the list of codes which are in column "Codes on Card" but not in the column "Codes in System".

I tried using the List.Difference function but it gives this error

This table has approx. million rows.

Can someone please let me know if there is any other option in Power Query or DAX to achieve this? 

Thanks,

Nimesh

[v-yuta-msft · Accepted solution]

@nimeshshah ,

You can split the text values in "Codes on Card" and "Codes in System" and then unpivot these new columns, then remove useless columns and rename some column like below:

 

Duplicate the table and filter based on attribute column in two tables you will get two tables as below:

Table1

 

#"Table1 (2)"

In final, create a blank query using M code: 

let
    Source = List.Difference(Table1[Value], #"Table1 (2)"[Value])
in
    Source

 

You may also refer to the appendix as well.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nimeshshah]

Many thanks for the response. This worked perfectly.