Need to filter the data with the following conditions

[shamilka]

Hi All, 

Please check the table 1 and table 2 data below. I want to filter the data of table 1 by comparing data of table2. If the Part, Car, Location is the same in table 2 compared to table1, the whole row of table 1 should be ignored. Otherwise the whole row of table 1 has to be copped to table 3. As per the example, the location of the second row in table 1(Bold text) is different from the location of the table 2. Therefore, the whole row of table 1 has to be copied as the output. All other column data can be different.

Table 1

Part	Cab	Location	LA	Table
1222	AA	20	A13333	R
1333	BB	30	A13334	R
1444	CC	40	A13335	R
1555	DD	5	A13336	R

Table 2

Part	Cab	Location	LA	Table
1222	AA	20	A13339	I
1333	BB	33	A13340	I
1444	CC	40	A13341	I
1555	DD	5	A13342	I

Output - Table 3

Part	Cab	Location	LA	Table
1333	BB	30	A13334	R

Thank you.

[v-cgao-msft · Accepted solution]

Hi @shamilka ，

Please try this:

let
    Source = Table.NestedJoin(Table1, {"Part", "Cab", "Location"}, Table2, {"Part", "Cab", "Location"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Part"}, {"Table2.Part"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each ([Table2.Part] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Table2.Part"})
in
    #"Removed Columns"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[AlienSx · Accepted solution]

let
    tbl01 = your_table_one,
    tbl02 = your_table_two,
    rows02 = Table.ToRecords(tbl02[[Part], [Cab], [Location]]),
    tbl03 = Table.RemoveMatchingRows(tbl01, rows02, {"Part", "Cab", "Location"})
in
    tbl03

[Arul]

@shamilka ,

I can help you by below DAX,

Table 3 =
VAR _table1 =
    SELECTCOLUMNS (
        'Table 1',
        "@Part1", 'Table 1'[Part],
        "@Cab1", 'Table 1'[Cab],
        "@Location", 'Table 1'[Location]
    )
VAR _table2 =
    SELECTCOLUMNS (
        'Table 2',
        "@Part2", 'Table 2'[Part],
        "@Cab1", 'Table 2'[Cab],
        "@Location", 'Table 2'[Location]
    )
VAR _tableOutput =
    EXCEPT ( _table1, _table2 )
VAR _selectColums =
    SELECTCOLUMNS ( _tableOutput, [@Part1] )
VAR _output =
    FILTER ( 'Table 1', 'Table 1'[Part] IN _selectColums )
RETURN
    _output

[shamilka]

Hi Arul, Could please give me the M query for this? 

Thank you.

[v-cgao-msft · Accepted solution]

Hi @shamilka ，

Please try this:

let
    Source = Table.NestedJoin(Table1, {"Part", "Cab", "Location"}, Table2, {"Part", "Cab", "Location"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Part"}, {"Table2.Part"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each ([Table2.Part] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Table2.Part"})
in
    #"Removed Columns"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group