Remove Duplicate in Power Query

One can make better and faster-informed decisions when they use Microsoft Power BI Integrations. The business data that is there on your systems have a lot of information that can help you to scale up your business. You can unlock its true potential when you use business intelligence software. Microsoft is the leading company when it comes to creating products that help a business to expand beyond leaps and bounds. It is easy to learn and use.

But, unfortunately not many people struggle with the software once they install it as they do not know how to use it. For example, removing duplicate in power query might look like a real struggle if you do not know how to do it.

In this blog, we are going to see how to remove duplicate from the column using Power Query, as we know that we can directly apply a transformation on the column and then use remove duplicate. But there is situation when remove duplicate does not work.

Situation 1:- Consider the following scenario, we have the same GUID but the case is different in that case duplicate will not get removed.

Solution:- Power Query is case sensitive language here both abcdefg123  and Abcdefg123 are considered as different. If you are going to do remove duplicates despite their case of letters, then you have to apply a transformation to change them all to one case; either UPPERCASE or lowercase.

Situation 2:- In other scenarios sometimes we have data with leading or trailing space is present so changing it to UPPERCASE, you would still have the extra space, which makes the two texts differ. So, to deal with that we have to use Trim Transformation to remove extra space.