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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.