Modelado de datos

Hola

Gracias por la solución @johnbasha33 proporcionada, y quiero ofrecer información para que el usuario la consulte.

hola @O-K, puede crear una consulta en blanco y poner el siguiente código en el editor avanzado.

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=Table.SelectRows(#"Expanded Count",(x)=>x[Emp Name]=[Emp Name] and x[Status]="Inactive")[End Date]{0},
b=List.Max(Table.SelectRows(#"Expanded Count",(x)=>x[Emp Name]=[Emp Name] and x[End Date]<a)[End Date])
in if [Index]=1 then [Status] else if [Status]="Active" and [End Date]=b then "Inactive" else null),
    #"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"})
in
    #"Removed Columns1"

Salida

¡Saludos!

Yolo Zhu

Si esta publicación ayuda, considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.