Forum Discussion

O-K's avatar
O-K
Frequent Visitor
2 years ago
Solved

Data Modeling

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)....
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
johnbasha33
Super User
2 years ago

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 !!