Tirar os duplicados Power Query

[christopher zimeni]

Contextualizando:

Tenho uma base de catraca de entradas e saídas onde eu agrupei as entradas e saídas do funcionário  de cada dia afim de calcular quantas horas permaneceu dentro da empresa.

 

Entretanto,  vi um possível problema nesse agrupamento. A base segue sempre o padrão para cada linha  gerada da seguinte forma "Entrada" e na linha abaixo "Saída". Porém, em alguns casos ocorre duas Entradas em seguidas ou mais ou até mesmo duas saídas ou mais e isso acabar saindo do par de Entradas e Saídas. Isso é quando o funcionário passa o crachá e da uma falsa liberação na catraca e ele precisa passar novamente para sair ou entrar.

 

Seria possível criar uma fórmula para identificar quando saí desse padrão de "Entrada" e "Saída" para retirar esse duplicados quando agrupar?

Tava pensando em algo do tipo quando for uma linha que está duplicada ele classificar com o número 0 em uma coluna adicionada ao lado e depois retirar todos aqueles com o valor 0.

 

Tem alguma outra sugestão ou como eu consigo viabilizar isso? Agradeço desde já! 🙂

 

Caso precisem da base para analisar eu posso simular uma e anexar... Só pedir.

[Erick Oliveira]

Você pode utilizar as medidas abaixo:

Aux Permanência = 
 VAR vValorEntrada = 
    CALCULATE(
        MIN('Table'[Data do Evento]),
        'Table'[Direção do Evento] = "Entrada"
    )
VAR vValorSaida = 
    CALCULATE(
        MAX('Table'[Data do Evento]),
        'Table'[Direção do Evento] = "Saída"
    )
RETURN
vValorSaida - vValorEntrada

Permanência = 
VAR vDuracao =
SUMX(
    VALUES(dCalendario),
    [Aux Permanência]
)
RETURN
IF(NOT ISBLANK(vDuracao),FORMAT(INT( vDuracao ), "0:") & FORMAT(vDuracao,"HH:NN:SS"))

Com essas medidas DAX nem será necessário o tratamento feito no Power Query. Mesmo com as duplicadas a cálculo funciona.

Tirar os duplicados Power Query.pbix

[Renato Carlos Rossato]

Olá @christopher zimeni, anexa uma base fictícia aqui para que possamos te ajudar melhor.

Mas basicamente caso tenha uma coluna de data, você pode fazer ordenações nas colunas primeiro por data, depois por funcionário e depois por direção do evento e em seguida tentar realizar esse agrupamento ou até mesmo realizar a remoção de duplicatas selecionando todas aquelas colunas que acredita que trará os resultados corretos para realizar o agrupamento depois.

[christopher zimeni]

Boa noite Renato, tudo bem?

Agradeço sua disponibilidade em querer ajudar. Referente ao que citou acima eu cheguei até fazer porém ocorreu alguns problemas na hora dele contabilizar as horas total que a pessoa ficou em cada dia. Igual citei para alguns dias onde tinha o par de Entrada e Saída deu certo o cálculo e ele somou as horas certinho. Mas para os casos que tinha mais de uma entrada em sequência nao deu 😞

 

Obs.: Mandei a base em anexo, ela vem nessa formatação quando extraí do sistema

 

Base teste.xlsx

[Erick Oliveira]

Boa noite, Christopher;

Considerando que o objetivo é calcular as horas permanecidas na empresa no dia, verifique se a sugestão abaixo  a tende.
No código  abaixo  eu agrupei os eventos e os dias, e caso a direção evento seja "Entrada" ele irá filtrar a data mais antiga, caso o contrario a data mais recente. Então assim teria apenas 1 entrada (a com o horário mais cedo) e 1 saída (a com horário mais tarde). 

let
    Fonte = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "rZnPThsxEMZfJcoZKeOxnU18ozS3qkWN1AviEEEOSFG2DfBEPfcpeLGON7Ty7MphvZ8lDsDhp8/j+fN59u5uviY2vJpfzW83n79/m11/+bHZyl+8WhAvmNjOzDJYF7yT/14/7J+f29lNe3w4vL79eWzlf9ud/LKb31+NYdE6mFXwTYa1Ob6cdh/AGiXMB7ucLqxJhZEJTIAwlwpjgQHCEpYP8mNXdYQJjALnYEXCJGIswjwgzKqIyQ0AEbPqKl1wSI6xyjEOZj1dGKfR9yu5gDrCfHBSTLaKMBdohSV/H2YqhV9gFHydU0pJNlglKZiRPMudskwYxYg5IPk5TX4n0rjKIc8wyiV/MSx2a2CMKBY1wZvpwoweI02+LD8WlrI4WBM8EDENiwmbu8tSYZKyPpdjhcKEZPOtv0hYdAQmWCRiy/QqLyXsCGHLviPItZ4xwrzKMYO0i4QVhV2YlWOEKUfANrjpjsC4njAoYtoROCnM6cJsekhbT5iNpwQcQcpi6RXBAR6qB1sis9IoD8WEtQvq+XQP5BilhkBcJyMR0zCnBu/Xt989YhEqm65TdFkkXxVMLsDUib6N3cLkZnihMBtfNgTkK6UTycm7ErA9A1i2J06CVbrLDsaAI1AsedZnhZWymIOrFn6BQY6gD8u+RopOKU3RG+gBMYTVKaVo1MVe5/xw6SnjqwsQRmvV+iGnmLJ8N9+AmTSEVTqlwHze9Zed8jyTgEEyhAHNZwBD6rIHsxK1GiHr3HDeX4wRpradcUM5fVymLHEqYmORu1SwrpqqCJOkYKzGNYzys7dIWGw+HloekXrYc+RNF9aoqxQW0nwGMKQsNczmy7LslNHcQXN8AIP6hYKRmILpSxrSu/4mzxojTK0cLCPLu5TlY+8BNlEpizt/gSTsMnVRccABldTfq0A5prYEYmJpuvEh/eY10GOc9HeDC8vmEcLUqj+mP5KvCkYXSrxUGBHkYQcwRtqF+m7g13lY0SnfvychwkyaF/79Lm9O+8f98eFpd1jcRuxu9unQ/nrdR9goiZrKZ2tcnxqL6x+jnf1sT7Nte3rZP40MpOldSrZYCwPpOgMP9EqTDhd5pUA+zaSNt/s2V0NYtEMyRetE7AzL7qDLYZzfBJWeki5c5X9h938B",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [Matrícula = _t, Nome = _t, #"Data do Evento" = _t, Evento = _t, #"Direção do Evento" = _t]
    ),
    #"Tipo Alterado" = Table.TransformColumnTypes(
        Fonte,
        {
            {"Matrícula", Int64.Type},
            {"Nome", type text},
            {"Data do Evento", type datetime},
            {"Evento", type text},
            {"Direção do Evento", type text}
        }
    ),
    #"Coluna Duplicada" = Table.DuplicateColumn(#"Tipo Alterado", "Data do Evento", "Data"),
    #"Tipo Alterado1" = Table.TransformColumnTypes(#"Coluna Duplicada", {{"Data", type date}}),
    #"Linhas Agrupadas" = Table.Group(
        #"Tipo Alterado1",
        {"Matrícula", "Nome", "Direção do Evento", "Data"},
        {
            {
                "Table",
                each _,
                type table [
                    Matrícula = nullable number,
                    Nome = nullable text,
                    Data do Evento = nullable datetime,
                    Evento = nullable text,
                    Direção do Evento = nullable text,
                    Data = nullable date
                ]
            }
        }
    ),
    #"Ajustar Horas" = Table.AddColumn(
        #"Linhas Agrupadas",
        "Horas Ajustadas",
        each
            if [Direção do Evento] = "Entrada" then
                Table.SelectRows(
                    [Table], let earliest = List.Min([Table][Data do Evento]) in each [Data do Evento] = earliest
                )
            else
                Table.SelectRows(
                    [Table], let latest = List.Max([Table][Data do Evento]) in each [Data do Evento] = latest
                ),
        type table [Data do Evento = nullable datetime, Evento = nullable text]
    ),
    #"Horas Ajustadas Expandido" = Table.ExpandTableColumn(#"Ajustar Horas", "Horas Ajustadas", {"Evento", "Data do Evento"}, {"Evento", "Data do Evento"}),
    #"Colunas Removidas" = Table.RemoveColumns(#"Horas Ajustadas Expandido",{"Table"}),
    #"Linhas Classificadas" = Table.Sort(#"Colunas Removidas",{{"Data do Evento", Order.Ascending}})
in
    #"Linhas Classificadas"

 

Tirar os duplicados Power Query.pbix

[christopher zimeni]

Boa noite Erick, tudo bem?

Showw, obrigado pela dedicação e por disponibilizar o seu tempo em ajudar.  Desculpa a demora em responder é que eu estava tentando decifrar oq vc mandou hahaha

Aparentemente seria isso. Uma dúvida só,  para apontar a quantidade de horas feitas por dia  por cada pessoa qual melhor forma que indica a fazer nesse caso? 

[christopher zimeni]

Em 29/03/2023 em 23:56, Erick Oliveira disse:

Você pode utilizar as medidas abaixo:

Aux Permanência = 
 VAR vValorEntrada = 
    CALCULATE(
        MIN('Table'[Data do Evento]),
        'Table'[Direção do Evento] = "Entrada"
    )
VAR vValorSaida = 
    CALCULATE(
        MAX('Table'[Data do Evento]),
        'Table'[Direção do Evento] = "Saída"
    )
RETURN
vValorSaida - vValorEntrada

Permanência = 
VAR vDuracao =
SUMX(
    VALUES(dCalendario),
    [Aux Permanência]
)
RETURN
IF(NOT ISBLANK(vDuracao),FORMAT(INT( vDuracao ), "0:") & FORMAT(vDuracao,"HH:NN:SS"))

Com essas medidas DAX nem será necessário o tratamento feito no Power Query. Mesmo com as duplicadas a cálculo funciona.

Tirar os duplicados Power Query.pbix 66.58 kB · 2 downloads

Desculpa a demora em responder... Obrigado pela ajuda deu super certo vou continuar com as análises após essa solução. 🙂 Obrigado por seu tempo e disposição em ajudar! Parabéns!