Forum Discussion

nimeshshah's avatar
nimeshshah
Frequent Visitor
7 years ago
Solved

Difference between two list of values

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".               ...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    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.

v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
nimeshshah
Frequent Visitor
7 years ago

Many thanks for the response. This worked perfectly.