Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
shamilka
Frequent Visitor

Need to filter the data with the following conditions

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

 

PartCabLocationLATable
1222AA20A13333R
1333BB30A13334R
1444CC40A13335R
1555DD5A13336R

 

Table 2

 

PartCabLocationLATable
1222AA20A13339I
1333BB33A13340I
1444CC40A13341I
1555DD5A13342I

 

Output - Table 3

 

PartCabLocationLATable
1333BB30A13334R

 

Thank you.

2 ACCEPTED SOLUTIONS

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"

vcgaomsft_0-1715930562487.png

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

View solution in original post

AlienSx
Super User
Super User

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

View solution in original post

4 REPLIES 4
AlienSx
Super User
Super User

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
Super User
Super User

@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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


shamilka
Frequent Visitor

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

 

Thank you.

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"

vcgaomsft_0-1715930562487.png

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors