remove duplicates

[Anonymous]

good morning everyone

I know that through Power Query we managed to combine two tables, however, I need the opposite way, where I have a fact table of hotel reservations with 34 million rows, and I have a table of changed hotel reservations where I made an EXCEPT query inside the bank SQL, and it only brings me changes of fact.

I would like to remove from the fact table (34 million rows), the reservations based on this table of "modified reservations", (where the reservations had modifications), and combine the fact table with the modified table avoiding me having to upload a fact than 34 million every day.

Is there any way to do this in PowerQuery?

[v-jingzhang]

Hi @Anonymous 

You can use Left-Anti when merging. After that, remove the merged table column. 

Here is a similar thread

https://community.powerbi.com/t5/Desktop/Remove-rows-which-contain-data-in-another-Table/m-p/929743 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[KNP]

If I'm understanding you correctly, you can use either 'Left Anti' or 'Right Anti' when doing the merge to acheive this.

[Anonymous]

Dude, it didn't work for what I need.

I would need the following;

= Table.RemoveMatchingRows(facReservas, {[ID= facChangesReservations[ID]]}, "ID")

I can generate the function using number, anything, but I can't insert the criterion, which is the ID of the table of modified/new reservations.

[KNP]

I don't understand what you mean, and perhaps I don't understand the original question.

You may need to provide more detail and examples.

"I would like to remove from the fact table (34 million rows), the reservations based on this table of "modified reservations","

This comment made me think that Left/Right Anti join may have been what you're looking for, but now when I re-read your post, I'm thinking you just need to setup incremental refresh.

It is open to too much interpretation, can you please provide further detail?