Identify and highlight duplicate rows

[Anonymous]

Hello everyone,

Whilst I see there are several posts regarding duplicate rows in a table, I can't find the solution to my particular query.

I have 2 tables (TableA and TableB) containing data from JSON files run on 2 different weeks. They contain the same data columns, but taken from the system 1 week apart therefore potentially different values if there has been a change on the system during the week.

I have to highlight what has changed.

I have created a new table TableMERGE whereby rows from TableA and Table B have been combined into one table.

I now need to ignore any rows of data which are identical, and be left with a table of data whereby there is a difference between the 2 weeks.

For example:

Tables A and B have been combined together (in Power Query) to make "Table Merge".

I then need to identify the rows of data which are duplicate (excelpt for the customNote column) and remove both rows from the table, leaving me with the last table shown in the above example, whereby only rows of data showing a change are left.

Unfortunately the system API does not have any create/modified by timestamp which would have made things a lot easier! 

Any help or suggestions greatly appreciated.

Many thanks. 

[Anonymous]

This has worked perfectly, thank you so much for your help!! Awesome 🙂

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try to create a new table with below dax formula:

Table =
VAR tmp =
    UNION ( 'Table 1', 'Table 2' )
VAR tmp1 =
    GROUPBY (
        tmp,
        [Site ID],
        [PersonID],
        [WeekCommencing],
        [Monday],
        [Thursday],
        [Wednesday],
        [Friday],
        "Ctn", COUNTX ( CURRENTGROUP (), [CustomNote] )
    )
VAR tmp2 =
    FILTER ( tmp1, [Ctn] = 1 )
RETURN
    tmp2

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.