cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Naveennegi119
Helper III
Helper III

show Non match data

Hi All,

 

i have two table with two column given below

 

Table1 Table2
Column A Column B
January February
February April
March May
April June
May November
June December
July June 
August December 
September  
October  
November  
December  

 

what i need find data which is missing in table 2 with reference to table1

 

New Column
January
March
July
August
September
October

 

Best regards,

NICK

5 ACCEPTED SOLUTIONS

@Naveennegi119 Please try “Merge Queries” option in “Power Query Editor” using appropriate Join as required.

 

Let me know if you need more detailed.





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

Proud to be a PBI Community Champion




View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

 

This is the Ma Query i used

 

let
    Source = Table.NestedJoin(All_months,{"All"},criteria_months,{"Months"},"criteria_months",JoinKind.LeftOuter),
    #"Expanded criteria_months" = Table.ExpandTableColumn(Source, "criteria_months", {"Months"}, {"Months"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded criteria_months", each ([Months] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Months"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"All", "Months"}})
in
    #"Renamed Columns"

 

Hope this heps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

ryan_mayu
Super User
Super User

@Naveennegi119

 

Hi , as someone mentioned previously, please use Edit Queries and Merge Queries

1.JPG2.JPG3.JPG





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

Proud to be a Super User!




View solution in original post

sreenathv
Solution Sage
Solution Sage

Hi,

 

You can achieve the result in two ways.

 

Method 1: Merge Queries as New

 

let
    Source = Table.NestedJoin(Table1,{"Column A"},Table2,{"Column B"},"Table2",JoinKind.LeftAnti)
in
    SourceMerge.png

 

 

 

Method 2: Have Fun with DAX

 

Create a calculated table using the following expression

 

Table3 = NATURALLEFTOUTERJOIN(Table1,Table2)

Then filter the calculated table using the following expression

 

FILTER(Table3,ISBLANK(Table3[Column B]))

 

Thanks

View solution in original post

v-frfei-msft
Community Support
Community Support

Hi @Naveennegi119,

 

Here we can create a calculated table using the formula.

 

Table =
CALCULATETABLE (
    Table1,
    FILTER (
        Table1,
        LOOKUPVALUE ( Table2[Column B], Table2[Column B], Table1[ColumnA] ) = BLANK ()
    )
)

 

 Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

10 REPLIES 10
Naveennegi119
Helper III
Helper III

Hi all,

 

apology for my side. i sent you data of one table with one column

 

but my table have more column in table and the reference you give me to achieve my problem is fine when table have one column.

 

i know about merge query but i want to this with dax of(Measure,column or table).

 

Thanx. in advance

 

Best regards,

NICK

 

v-frfei-msft
Community Support
Community Support

Hi @Naveennegi119,

 

Here we can create a calculated table using the formula.

 

Table =
CALCULATETABLE (
    Table1,
    FILTER (
        Table1,
        LOOKUPVALUE ( Table2[Column B], Table2[Column B], Table1[ColumnA] ) = BLANK ()
    )
)

 

 Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi all,

 @v-frfei-msft@sreenathv@ryan_mayu@Ashish_Mathur@PattemManohar

 

Thank u for giving your time. yesterday i can't reply because of illness.

 

all the solution i will try,

 

and again thnx. for help.

 

Best regards,

NICK

sreenathv
Solution Sage
Solution Sage

Hi,

 

You can achieve the result in two ways.

 

Method 1: Merge Queries as New

 

let
    Source = Table.NestedJoin(Table1,{"Column A"},Table2,{"Column B"},"Table2",JoinKind.LeftAnti)
in
    SourceMerge.png

 

 

 

Method 2: Have Fun with DAX

 

Create a calculated table using the following expression

 

Table3 = NATURALLEFTOUTERJOIN(Table1,Table2)

Then filter the calculated table using the following expression

 

FILTER(Table3,ISBLANK(Table3[Column B]))

 

Thanks

ryan_mayu
Super User
Super User

@Naveennegi119

 

Hi , as someone mentioned previously, please use Edit Queries and Merge Queries

1.JPG2.JPG3.JPG





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

Proud to be a Super User!




Ashish_Mathur
Super User
Super User

Hi,

 

This is the Ma Query i used

 

let
    Source = Table.NestedJoin(All_months,{"All"},criteria_months,{"Months"},"criteria_months",JoinKind.LeftOuter),
    #"Expanded criteria_months" = Table.ExpandTableColumn(Source, "criteria_months", {"Months"}, {"Months"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded criteria_months", each ([Months] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Months"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"All", "Months"}})
in
    #"Renamed Columns"

 

Hope this heps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Naveennegi119
Helper III
Helper III

 

 

@Naveennegi119 Please try “Merge Queries” option in “Power Query Editor” using appropriate Join as required.

 

Let me know if you need more detailed.





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

Proud to be a PBI Community Champion




Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.