Duplicates returned with Table.Distinct in M Query

Hi Ashish,

Thanks very much for replying.

The actual data is proprietary so I can't share the actual data, however, some made up data to illustrate the point:

Table 1

Year      Attr1       Attr2

2015     A                Red

2016     A                Red

2015     B                Red

2015     B                Blue

Table 2

Year      Attr1       Attr2

2015     A                Red

2016     A                Red

2015     B                Green

2015     B                Blue

First step is a union.

Combined table

Year      Attr1       Attr2

2015     A                Red  <- duplicate 1

2016     A                Red  <- duplicate 2

2015     B                Red

2015     B                Blue  <- duplicate 3

2015     A                Red  <- duplicate 1

2016     A                Red  <- duplicate 2

2015     B                Green

2015     B                Blue  <- duplicate 3

Next step is to get distinct rows.  I've indicated above the duplicates introduced by the union query.  So the distinct rows remove all but one instance of the duplicates.

Year      Attr1       Attr2

2015     A                Red

2016     A                Red

2015     B                Red

2015     B                Blue

2015     B                Green

The two data tables I'm working with are 291k and 69k records long.  The Table.Distinct query returns 5,284 distinct rows, however de-duping these 5,284 rows reduces the row count to 5,250, so there are 34 duplicated rows (only duplicates, no triplicates etc).  Hence it is *almost* successful in producing distinct rows, just not quite there.

Regards,

Mark