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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Removing data with the same values in a specific column between 2 different queries

I have data from 2 queries in Power Query. We'll call them A and B. Both queries return a number of columns, including a date column. I need to represent both A and B as a stacked bar chart showing how many rows each has per month. The problem is B contains data included in A. I can't use the "remove duplicates" in Power Query because that only concerns itself with rows/columns with the same data across the entire row and only in the same query.  Only 1 column in A is duplicated in B (we'll call that column "name"). There are names present in B that should only be present in A.

 

So far, I have tried using DAX to count all the rows of B:

  • TotalB = COUNTROWS('B')

and count all rows of A:

  • TotalA = COUNTROWS('A')

then subtract to eliminate duplicates:

  • TrueTotalB = TotalB - TotalA

This works but when I put TrueTotalB in a stacked column chart against the date column as its y axis, I get negative values for most of the date. The reason is TotalB subtracts TotalA from every date, instead of only relative date e.g. a duplicate name in B with date January should only be subtracted from January, not every month.

 

Maybe there is a different solution besides using DAX? Any help is appreciated.

 

Edit: I'm thinking I can merge both A and B together and then use a calculated column to find duplicate names, then just delete them manually.

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@Anonymous,

 

I would use an anti join in Power Query. Exclude the duplicate rows in TableB, and then append the remaining rows to TableA.

 

1. Create a left anti join:

 

DataInsights_0-1666720309388.png

 

2. Remove column "TableA" and name this table TableBAntiJoin:

 

DataInsights_1-1666720383386.png

 

3. Append TableA to TableBAntiJoin:

 

DataInsights_2-1666720429138.png

4. Result:

 

DataInsights_3-1666720479149.png

 

 





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

Proud to be a Super User!




View solution in original post

3 REPLIES 3
DataInsights
Super User
Super User

@Anonymous,

 

I would use an anti join in Power Query. Exclude the duplicate rows in TableB, and then append the remaining rows to TableA.

 

1. Create a left anti join:

 

DataInsights_0-1666720309388.png

 

2. Remove column "TableA" and name this table TableBAntiJoin:

 

DataInsights_1-1666720383386.png

 

3. Append TableA to TableBAntiJoin:

 

DataInsights_2-1666720429138.png

4. Result:

 

DataInsights_3-1666720479149.png

 

 





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

Proud to be a Super User!




Anonymous
Not applicable

Thanks for the help. Though I am confused. I have followed your solution but I got the error "A cyclic reference was encountered" when appending.

 

Also, I don't think I understand your solution. You have name1 - 4 in the final table but isn't this effectively combining the tables into one table, leaving no duplicates. Using your example, the final result should be Table B: name4 (removed name1) and TableA: name1, name2, name3. Am I understanding correctly?

@Anonymous,

 

The cyclic reference can be avoided by using "Merge Queries as New". This creates a new query.

 

If the requirement is to have two tables, each with unique names, then you can skip the append step. TableA would have Name1-3 and TableBAntiJoin would have Name4.





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

Proud to be a Super User!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.