Seeing what changes to employee records have happened

[ZealandZu]

Hi. Im trying to figure out how I get a employee history table which has multiple rows per employee due to when a change has been made to their records. Below is a example of some of the types of columns. However in reality there are about 60 columns that could be altered. Note this is sample data and not real

Surname	Forename	Employee#	Pay#	Country	Location	Business	Status	Hire Date	Termination Date	Changed Date
Bloggs	Joe	12345	8888	UK	South	BMW	A	01/01/2000	null	01/01/2000
Bloggs	Joe	12345	8888	UK	South	Ford	A	01/01/2000	null	05/06/2015
Bloggs	Joe	12345	8888	UK	North	Skoda	A	01/01/2000	null	06/07/2016
Bloggs	Joe	12345	12345	UK	North	Skoda	A	01/01/2000	null	07/07/2017
White	Ben	12121	7885	UK	South	Ford	A	05/06/2006	null	05/06/2006
White	Ben	12121	12121	UK	South	Ford	A	05/06/2006	null	07/07/2017
White	Ben	12121	12121	UK	South	Ford	T	05/06/2006	08/09/2020	08/09/2020

Basically, the end result i need is that if i select Joe Bloggs in a slicer, i want to be able to see all of the changes that have been made (ps i know how to concatenate the employee name). Im assuming i need to do some sort of unpivotting, indexing etc however im stumped as to what to do for this one.

Employee	Changed Field	New Record	Date of Change
Joe Bloggs	Hire Date	01/01/2000	01/01/2000
Joe Bloggs	Business	Ford	05/06/2015
Joe Bloggs	Location	North	06/07/2016
Joe Bloggs	Business	Skoda	06/07/2016
Joe Bloggs	Pay#	12345	07/07/2017

Many thanks

[v-shex-msft · Accepted solution]

HI @ZealandZu,

You can do unpivoted column on your table fields(Pay#, Business, Location) which you want to trace.

Unpivot columns - Power Query | Microsoft Learn

Then you can write measure formula to check the change between current and previous date and return flag.

Change Flag = 
VAR currDate =
    MAX ( 'Table'[Changed Date] )
VAR prevDate =
    CALCULATE (
        MAX ( 'Table'[Changed Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Changed Date] < currDate ),
        VALUES ( 'Table'[Forename] ),
        VALUES ( 'Table'[Surname] )
    )
VAR currValue =
    SELECTEDVALUE ( 'Table'[Value] )
VAR prevValue =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Changed Date] = prevDate ),
        VALUES ( 'Table'[Forename] ),
        VALUES ( 'Table'[Surname] ),
        VALUES ( 'Table'[Attribute] )
    )
RETURN
    IF ( prevDate <> BLANK (), IF ( currValue <> prevValue, "Y", "N" ), "N" )

After these steps, you can create a table visual with category and correspond fields and use measure as filter to filter not matched records.

Regards,

Xiaoxin Sheng

[ZealandZu]

Thankyou so much. You are a genius