Dataset Left Anti Join - issue with decimals

unkxowx · January 25

Hi all,

I have two datasets that are similar in their structure and my goal is to get a dataset that has records of the left table that do not have a match in the right table and as a result, I used Left Anti Join.

I am matching the tables on a number of keys and one of those keys is a column which is of type decimal double in both the datasets, but the format is a little bit different, as in, Dataset_1 has values like 1.20, 2.45 etc. and on the other hand Dataset_2 has values like 1.20000, 2.45000 etc.

I tried formatting them to reach an equivalent number of digits after decimal using format, substring etc. etc. but failed to achieve the desired results (gives me records in the left table that do have a match in the right table but that particular key varies by just a few trailing 0s. I feel this is causing an issue with the joining because when I tried joining them in PBI after changing the decimal format to an equal number of digits, I was able to join and get the dataset that I wanted.

I am kind of baffled as to how and why this is happening. Any solutions?

Thank you