Remove rows which column value does not exist in another table

Hi alpb 

Try this code to add a new table to your report:

New Table =
CALCULATETABLE (
    bigger_table,
    INTERSECT (
        VALUES ( bigger_table[customer_id] ),
        VALUES ( smaller_table[customer_id] )
    )
)

bigger_table:

smaller_table:

 

Output [New Table]:

 

 

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

Appreciate your Kudos  !!

 

Hi alpb ,

You could refer to VahidDM 's method which used DAX function: INTERSECT()

Or use merge in Power Query,below are the steps:

1.Select Merge Queries > Merge Queries/Merge Queries as New from the Home tab on the ribbon.

2.Select Customer_id column in these two tables, choose Inner join kind

3.Remove the new added column:

Here is the whole M code in Advanced Editor dialog:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjRU0lEyNDAAkhUViUqxOtFKSUZAjilEJAkskmwM5BhBRJKVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer_id = _t, Sales = _t, Email = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer_id", type text}, {"Sales", Int64.Type}, {"Email", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Customer_id"}, smaller_table, {"Customer_id"}, "smaller_table", JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"smaller_table"})
in
    #"Removed Columns"

Refer to:

https://www.sqlbi.com/articles/set-functions-in-dax-union-intersect-and-except/

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