How to Duplicate Rows and Change Values

[LostintheBIu]

Hello kind community!

I need help with coding to transform some HR data.
We have some employees, who start working for my organisation as a trial employee and after some time they become regular employees. When this happens, in the HR data, they are given a date "trial-to-full employment date" and their "job title" is updated to their specific new job title.

For my data purposes, I need to split this person's record to be split into two rows, so the 1st row shows their time period as a "trial employee", then the 2nd row shows the time period in their new non-trial job title. For example in the screenshot below, highlighted yellow John Brown.

I have written out the data transformation steps required, but I lack the coding skills to be able to do this myself. Please help!

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Employee ID", Int64.Type}, {"Employment Start Date", type date}, {"Employment End Date", type date}, {"Job Title", type text}, {"Trial to full employment Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Rows to create", each if [Trial to full employment Date]=null then 1 else 2),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each {1..[Rows to create]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom", "End date", each if [Custom]=1 and [Trial to full employment Date]<>null then Date.AddDays([Trial to full employment Date],-1) else [Employment End Date]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Job title.1", each if [Custom]=1 and [Trial to full employment Date]<>null then "Trial Employee" else [Job Title]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Employment End Date", "Job Title", "Rows to create", "Custom"})
in
    #"Removed Columns"

Hope this helps.

[LostintheBIu]

Brilliant, it worked, thanks Ashish!

[Ashish_Mathur]

You are welcome.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...


https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...