Adding new columns from data

[RichOB]

Hi, I have a data table that tracks employees who have left my company and then returned. I need the Date data when they have returned on the same row, as their singular NI_Number, please. How can I transform Table 1 into Table 2, adding the three new columns at the end?

Table 1 

NI_Number	Name	Title	Date_Joined	Date_Left	Days_Employed
NI112233	Dave Jones	Customer Service	01/04/2024	20/05/2024	49
NI112233	Dave Jones	Customer Service	01/06/2024	10/06/2024	9
NI884455	Eric Davies	Customer Service	01/07/2024	10/10/2024	101
NI884455	Eric Davies	Customer Service	20/10/2024	28/10/2024	8
NI009900	Lloyd Williams	Store Manager	01/04/2024	10/09/2024	90
NI009900	Lloyd Williams	Store Manager	01/10/2024	11/11/2024	41
NI887711	Ian Ians	Store Manager	10/05/2024	10/10/2024	153
NI887711	Ian Ians	Store Manager	01/11/2024		327

Table 2 

NI_Number	Name	Title	Date_Joined	Date_Left	Days_Employed	Date_Employed2	Date_Left2	Days_Employed2
NI112233	Dave Jones	Customer Service	01/04/2024	20/05/2024	49	01/06/2024	10/06/2024	9
NI884455	Eric Davies	Customer Service	01/07/2024	10/10/2024	101	20/10/2024	28/10/2024	8
NI009900	Lloyd Williams	Store Manager	01/04/2024	10/09/2024	90	01/10/2024	11/11/2024	41
NI887711	Ian Ians	Store Manager	10/05/2024	10/10/2024	153	01/11/2024		327

[v-aatheeque]

Hi @RichOB 

We’d like to confirm whether your query has been addressed. If you still need help, please let us know and we’ll provide further guidance.

[v-aatheeque]

Hi @RichOB 

Have you had a chance to look at the earlier response? Let us know if anything is unclear, and we’ll provide further clarification.

[v-aatheeque]

Hi @RichOB 
We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know

[OktayPamuk80 · Accepted solution]

Hi,

I would create an index per Ni_Number (e.g. Solved: Index column based on value/category - Microsoft Fabric Community). With this index you can then create a matrix, where the index is your column, therefore in case of three entries per Ni-Number you would see 3 columns (or 9 due to three measures joined. left and employed).

Regards,

Oktay

If it helped you would appreciate a KUDOS and mark the answer as solution.