Derivation of SCD type 2 history table from type 1 and Audit table.

[Rajalaxmi]

Hello,

We are having a requirement to derive historical table from type 1 and Audit table as per below.

Please let us know whether such requirement is feasible with Power BI .

Employee Table
ID	FirstName	LastName	Designation	Account Name	Phone No.	Year
1	Shreekara	Tantry	ASOC	TF	9909	2017		For Example:- We have Employee table having employees records.
2	Rajalaxmi	Pradhan	ITA	Covance	1104	2017
Audit Table
Table	Column	ID	Old Value	New Value	Date			We have Audit table having all changes records with date.If we have changed three values tehn in Employee table ,then in Audit table it will appear with three different rows.
Employee	Designation	1	ITA	AST	2011
Employee	Designation	1	AST	ASOC	2014
Employee	Phone	2	12345	9909	2017
Employee	Account	3	JNJ	TF	2017
Requirement
ID	FirstName	LastName	Change in column Name	Change in column Name	Change in column Name	Date		In our requirement we need one report having all the changed values of different columns of one ID in one row.
1	Shreekara	Tantry	ASOC	TF	9909	2017
1	Sreekara	Tantry	ASOC	JNJ	12345	2017
1	Sreekara	Tantry	AST	JNJ	12345	2014
1	Sreekara	Tantry	ITA	JNJ	12345	2011

Regards,

Rajalaxmi Pradhan

[v-qiuyu-msft]

Hi @Rajalaxmi,

Does the ID column in Audit table match with ID column in Employee table? If it does match as the red section in those two tables, those two columns shouldn't be returned for the employee Shreekara as red section. Please double check the table data and clarify the requirement. 

Best Regards,
Qiuyun Yu