Forum Discussion
Remove ALL Duplicates Entirerly
Hi Anonymous
Let me rephrase what i understood, and then provide the solution.
You have two data sets A and B. A has 300000 rows, B also has 300000 rows and a new set of data i.e. 50000 rows. You would like to filter the newly added records from table B.
If my understanding is correct then below is the solution.
Import both A and B in separate queries. Now that you would like to see the remaining rows in Table B or new records in B, please select Query for table B and then follow the steps:
Select B Query --> Home --> Merge Queries / Merge Queries as New (if you want the data in a new table then select the second option) --> Select table A from the dropdown list --> Highlight the Unique ID# column in both the tables --> in the JOIN KIND select LEFT ANTI --> Click OK.
You're sorted.
If you think this is the solution then please do mark it as a solution
Pradip Pardeshi
Microsoft Certified Trainer
Hi Anonymous
Let me rephrase what i understood, and then provide the solution.
You have two data sets A and B. A has 300000 rows, B also has 300000 rows and a new set of data i.e. 50000 rows. You would like to filter the newly added records from table B.
If my understanding is correct then below is the solution.
Import both A and B in separate queries. Now that you would like to see the remaining rows in Table B or new records in B, please select Query for table B and then follow the steps:
Select B Query --> Home --> Merge Queries / Merge Queries as New (if you want the data in a new table then select the second option) --> Select table A from the dropdown list --> Highlight the Unique ID# column in both the tables --> in the JOIN KIND select LEFT ANTI --> Click OK.
You're sorted.
If you think this is the solution then please do mark it as a solution
Pradip Pardeshi
Microsoft Certified Trainer
This was great. How do I get A&B not to load? I only want C (the new merged query) to be visible in the workbook.
Anonymous
When you import table A and Table B, before merging go to HOME --> Close & Load To --> on the new screen you will find a radio button that says "Connection only", select that --> if you don't want to add the table to data model then uncheck the box --> Add to data model --> OK
This will create a connection only query.
Again go back to Power Query Editor --> Merge Query as New --> This creates a new merged table C --> Close & Load To --> Select Table --> Uncheck Add to Data model (If needed) --> Ok.
Hope this helps. If this is the solution please do mark this as solution :)
Pradip Pardeshi
Microsoft Certified Trainer
For the dataset you don't want to load into your Power BI model, from Power Query you can -
>right-click the dataset
>uncheck 'Enable load'
>then close & apply
Hi PradipMCT
I believe the connection query will still be valid, however, the dataset will not be included in the report.
If "enable load" is unchecked, the dataset will be included in subsequent refreshes but it won't be loaded into the report. Only the merged query will be loaded. The helps to improve performance.
