Data Modeling

[O-K]

Hello,

I'm creating an FTE (Full-time equivelant) overview, but the date is as it follows: 
1- End Date of 31/12/9999 means the present day + if the status is active then still Active (in service). 

2- If an employee is not active in the present day, I want it to be deleted or only counted till last end date. for example (Employee A) second entry should be deleted/not counted and the status of the first entry to be changed to Inactive

3- Some employees' ID have multiple input (duplicated ; in some casees more than twice) in the start date, due to job rotation, change in departments , etc. but I want to adjust the start date, for the first start date (the earliest) to stay the same, but the one after would be the end date of the previous one and so on. So the start date in the new department needs to be the End date of the previous department.

4- Some employees (in Sample-Employee C)have the same department, but different employee number. That means they moved from an external contract or as an Intern to a company contract. I would like to keep the interns out of the calculations 

Emp.No	Emp Name	Start date	End Date	Status
9503	Employee A	23/05/1972	28/03/2021	Active
9503	Employee A	23/05/1972	31/12/9999	Inactive
257	Employee B	24/03/1986	31/07/2021	Active
257	Employee B	24/03/1986	31/12/2021	Active
257	Employee B	24/03/1986	31/12/9999	Inactive
919	Employee C	01/03/2023	30/10/2023	Active
919	Employee C	01/03/2023	31/12/9999	Inactive
2721	Employee C	29/08/2022	28/02/2023	Active
2721	Employee C	29/08/2022	31/12/9999	Inactive
2778	Employee C	01/02/2024	31/12/9999	Active

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution @johnbasha33  provided, and i want to offer some information for user to refer to.

hello @O-K, you can create a blank query and put the following code to advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZE7CgIxEECvIqkX5pNdkymjWHiGZQuRFIK/QgRv72Q1uErU1XSveLzJTNsaadCayix2x+3hEuMkKLAFbIDEcQIPaIGRSSGsT5tzNF01QrQExCD6FJb71UPlxg3NWTLrVCHx05uJrpAc4WnxX680qZAMzbkC0n0f6fcWgTBD+MF7vxvXT/+ksgD6pOZ7cCH5VfzUdL40bp+pX9Xc7K4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Emp.No = _t, #"Emp Name" = _t, #"Start date" = _t, #"End Date" = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp.No", Int64.Type}, {"Emp Name", type text}, {"Start date", type text}, {"End Date", type text}, {"Status", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Start date", type date}, {"End Date", type date}}, "en-GB"),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type with Locale", each ([Emp.No]=List.Max(Table.SelectRows(#"Changed Type with Locale",(x)=>x[Emp Name]=[Emp Name])[Emp.No]))),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Emp.No"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1), type table }}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Emp Name", "Start date", "End Date", "Status", "Index"}, {"Emp Name", "Start date", "End Date", "Status", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Count", "Custom", each let a=try Table.SelectRows(#"Expanded Count",(x)=>x[Emp Name]=[Emp Name] and x[Status]="Inactive")[End Date]{0} otherwise null,
b=if a<>null then List.Max(Table.SelectRows(#"Expanded Count",(x)=>x[Emp Name]=[Emp Name] and x[End Date]<a)[End Date]) else null
in if [Status]="Inactive" then null  else if [Status]="Active" and [End Date]=b then "Inactive" else [Status]),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] <> null)),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows1",each [Start date],each if [Index]=1 then [Start date] else Table.SelectRows(#"Filtered Rows1",(x)=>x[Emp Name]=[Emp Name] and x[Index]=[Index]-1)[End Date]{0},Replacer.ReplaceValue,{"Start date"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Replaced Value",{"Status", "Index"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Removed Columns1",#date(9999, 12, 31),DateTime.Date(DateTime.LocalNow()),Replacer.ReplaceValue,{"End Date"})
in
    #"Replaced Value1"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnbasha33]

@O-K 

To address the requirements you've outlined, you'll need to perform several steps using Power Query in Power BI to transform and clean your data. Here's a step-by-step guide on how to achieve each of your requirements:

1. **Handle Present Day End Dates**:
- Replace the end dates of "31/12/9999" with the current date to represent present day.
- Identify and mark the employees with "Active" status beyond the present day.

2. **Filter out Inactive Employees**:
- Filter out the rows where the end date is before the present day.
- Update the status of the last record for each employee to "Inactive" if it extends beyond the present day.

3. **Adjust Start Dates for Job Rotations**:
- Sort the data by employee number and start date in ascending order.
- Use the Table.Group function to group the data by employee number.
- Create a custom function to iterate over the rows within each group to adjust the start dates based on the end dates of the previous rows.
- Apply the custom function to each group to adjust the start dates accordingly.

4. **Filter out Interns**:
- Identify and filter out the rows where the employee number appears multiple times but with different statuses.
- Keep only the rows where the status is not "Intern".

Here's a summary of the Power Query transformations you'll need to perform:

- Replace values in the "End Date" column to represent the present day.
- Filter out inactive employees.
- Adjust start dates for job rotations.
- Filter out interns.
- Optionally, perform additional cleaning and formatting as needed.

These transformations can be achieved using the Power Query Editor in Power BI. If you need more specific guidance on any of these steps, feel free to ask!


Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!