Dúvida na transformação dos dados!

[Patricia Amorim Da Cunha]

Oi pessoal!

Alguém consegue me ajudar nessa transformação? Estou importando uma planilha de banco de horas exportada do sistema Tangerino. Consegui remover algumas colunas e linhas que não precisava, mas ainda não consegui finalizar.

Preciso de 3 colunas: Nome do colaborador, mês da competência e banco acumulado (horas).

Quando há quebra de página, ele repete o nome do colaborador, então não consegui ver um padrão para adicionar uma coluna só com o nome para depois poder filtrar.

Existe uma forma e deslocar as linhas para cima? Pensei em criar uma coluna a mais deslocando a célula de baixo para cima, para a quantidade de horas ficar ao lado da competência. Mas não consegui.

Coloquei o modelo do arquivo bruto em anexo!

 

Obrigada!

RelatorioBancoHoras_2023.xls

[Bruno Abdalla de Souza]

Segue proposta de tratamento.

let
    Fonte = Excel.Workbook(File.Contents("SeuCaminho\RelatorioBancoHoras_2023.xls"), null, true),
    relatorio_banco_horas_processad1 = Fonte{[Name="relatorio_banco_horas_processad"]}[Data],
    SelecionaColunas = Table.SelectColumns(relatorio_banco_horas_processad1,{"Column1", "Column11", "Column14"}),
    RemoveLinhasSuperiores = Table.Skip(SelecionaColunas,4),
    FiltraDadosColaboradorCompetencia = Table.SelectRows(RemoveLinhasSuperiores, each not Text.StartsWith([Column1], "Saldo") and not Text.StartsWith([Column1], "Sáb") and not Text.StartsWith([Column1], "Dias") and not Text.StartsWith([Column1], "CPF") and not Text.StartsWith([Column1], "Relatório") and not Text.StartsWith([Column1], "Período") and not Text.StartsWith([Column1], "Empregador") and Text.Select(Text.Start([Column1], 1), {"1", "2", "3", "4", "5", "6", "7", "8", "9"}) = "" and not Text.StartsWith([Column1], "Dom") and not Text.StartsWith([Column1], "Total")),
    SubstituiHorasCalculadasPorNull = Table.ReplaceValue(FiltraDadosColaboradorCompetencia,"Horas Calculadas",null,Replacer.ReplaceValue,{"Column14"}),
    PreencheParaCima = Table.FillUp(SubstituiHorasCalculadasPorNull,{"Column14"}),
    FiltraLinhasNulas_RemoveSaldo = Table.SelectRows(PreencheParaCima, each ([Column11] = null)),
    PassaNomeColaboradorParaOutraColuna = Table.ReplaceValue(FiltraLinhasNulas_RemoveSaldo,null,each if not Text.StartsWith([Column1], "Comp") then [Column1] else null,Replacer.ReplaceValue,{"Column11"}),
    PreencheNomeColaboradorParaBaixo = Table.FillDown(PassaNomeColaboradorParaOutraColuna,{"Column11"}),
    FiltraMeses = Table.SelectRows(PreencheNomeColaboradorParaBaixo, each Text.StartsWith([Column1], "Comp")),
    RemoveTextoCompetencia = Table.ReplaceValue(FiltraMeses,"Competência: ","",Replacer.ReplaceText,{"Column1"}),
    SubstituiVazioPorNulo = Table.ReplaceValue(RemoveTextoCompetencia,"",null,Replacer.ReplaceValue,{"Column14"}),
    TransformaHoraEmDecimal = Table.TransformColumns(SubstituiVazioPorNulo, {{"Column14", each Number.From(Text.BeforeDelimiter(_, ":")) + Number.From(Text.AfterDelimiter(_, ":")) / 60, type number}}),
    RenomeiaColunas = Table.RenameColumns(TransformaHoraEmDecimal,{{"Column1", "Competência"}, {"Column11", "Colaborador"}, {"Column14", "Saldo"}}),
    AlteraTipoDados = Table.TransformColumnTypes(RenomeiaColunas,{{"Colaborador", type text}, {"Competência", type date}})
in
    AlteraTipoDados

 

20240130_TratamentoDados.pbix

[Joao Raulino]

Suave @Patricia Amorim Da Cunha?

Vai aqui uma outra maneira de fazer baseado em filtragem seletiva e colunas customizadas:

Citar

let
    Source = Excel.Workbook(File.Contents("C:\Users\L0550201.MAIN\Downloads\RelatorioBancoHoras_2023.xls"), null, true),
    relatorio_banco_horas_processad1 = Source{[Name="relatorio_banco_horas_processad"]}[Data],
    #"Filtered Rows #All" = Table.SelectRows(relatorio_banco_horas_processad1,
    each 
    ( [Column1] = Text.Upper([Column1])
      or Text.Contains([Column1], "Competência")
      or Text.Contains([Column1], "Validade")
    ) and
    ( [Column1] <> null
      and [Column1] <> "CPF/CNPJ:"
      and Text.Middle([Column1], 2, 1) <> "/"
    )
),
    #"Added Column #Nome" = Table.FillDown(Table.AddColumn(#"Filtered Rows #All", "Nome do Colaborador", each if (not Text.StartsWith([Column1], "Competência") and not Text.StartsWith([Column1], "Validade")) then [Column1] else null, type text), {"Nome do Colaborador"}),
    #"Added Column #Mes" = Table.AddColumn(#"Added Column #Nome", "Mês da competência", each if Text.StartsWith([Column1], "Competência") then [Column1] else null, type text),
    #"Added Column #Banco" = Table.FillUp(Table.AddColumn(#"Added Column #Mes", "Banco Acumulado (Horas)", each if (not Text.StartsWith([Column8], "Hora Excedente") and [Column8] <> null) then [Column8] else null, type text), {"Banco Acumulado (Horas)"}),
    #"Filtered Rows #Null" = Table.SelectRows(#"Added Column #Banco", each ([Mês da competência] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows #Null",{"Nome do Colaborador", "Mês da competência", "Banco Acumulado (Horas)"}),
    #"Replaced #Competencia" = Table.ReplaceValue(#"Removed Other Columns","Competência: ","",Replacer.ReplaceText,{"Mês da competência"}),
    #"Transformed #Horas" = Table.TransformColumns(#"Replaced #Competencia", {{"Banco Acumulado (Horas)", each Number.From(Text.BeforeDelimiter(_, ":")) + Number.From(Text.AfterDelimiter(_, ":")) / 60, type number}})
in
    #"Transformed #Horas"

 

E para matar a curiosidade, da sim para "deslocar" uma coluna para cima através de uma manobra criativa 😁 Para isso você tem que:

1. Duplicar a tabela original
2. Remover o numero de linhas X desta nova tabela duplicada
3. Adicionar um index na tabela original e na tabela duplicada
4. Fazer uma mescla das duas tabelas baseada no index

Neste nosso caso não foi necessário isso, mas quem sabe num próximo desafio.

Não esquece de marcar alguma das respostas como melhor solução 😉

Abs!

Dúvida na transformação dos dados.pbix

Editado Janeiro 31 por Joao Raulino

[Patricia Amorim Da Cunha]

8 horas atrás, Bruno Abdalla de Souza disse:

Segue proposta de tratamento.

let
    Fonte = Excel.Workbook(File.Contents("SeuCaminho\RelatorioBancoHoras_2023.xls"), null, true),
    relatorio_banco_horas_processad1 = Fonte{[Name="relatorio_banco_horas_processad"]}[Data],
    SelecionaColunas = Table.SelectColumns(relatorio_banco_horas_processad1,{"Column1", "Column11", "Column14"}),
    RemoveLinhasSuperiores = Table.Skip(SelecionaColunas,4),
    FiltraDadosColaboradorCompetencia = Table.SelectRows(RemoveLinhasSuperiores, each not Text.StartsWith([Column1], "Saldo") and not Text.StartsWith([Column1], "Sáb") and not Text.StartsWith([Column1], "Dias") and not Text.StartsWith([Column1], "CPF") and not Text.StartsWith([Column1], "Relatório") and not Text.StartsWith([Column1], "Período") and not Text.StartsWith([Column1], "Empregador") and Text.Select(Text.Start([Column1], 1), {"1", "2", "3", "4", "5", "6", "7", "8", "9"}) = "" and not Text.StartsWith([Column1], "Dom") and not Text.StartsWith([Column1], "Total")),
    SubstituiHorasCalculadasPorNull = Table.ReplaceValue(FiltraDadosColaboradorCompetencia,"Horas Calculadas",null,Replacer.ReplaceValue,{"Column14"}),
    PreencheParaCima = Table.FillUp(SubstituiHorasCalculadasPorNull,{"Column14"}),
    FiltraLinhasNulas_RemoveSaldo = Table.SelectRows(PreencheParaCima, each ([Column11] = null)),
    PassaNomeColaboradorParaOutraColuna = Table.ReplaceValue(FiltraLinhasNulas_RemoveSaldo,null,each if not Text.StartsWith([Column1], "Comp") then [Column1] else null,Replacer.ReplaceValue,{"Column11"}),
    PreencheNomeColaboradorParaBaixo = Table.FillDown(PassaNomeColaboradorParaOutraColuna,{"Column11"}),
    FiltraMeses = Table.SelectRows(PreencheNomeColaboradorParaBaixo, each Text.StartsWith([Column1], "Comp")),
    RemoveTextoCompetencia = Table.ReplaceValue(FiltraMeses,"Competência: ","",Replacer.ReplaceText,{"Column1"}),
    SubstituiVazioPorNulo = Table.ReplaceValue(RemoveTextoCompetencia,"",null,Replacer.ReplaceValue,{"Column14"}),
    TransformaHoraEmDecimal = Table.TransformColumns(SubstituiVazioPorNulo, {{"Column14", each Number.From(Text.BeforeDelimiter(_, ":")) + Number.From(Text.AfterDelimiter(_, ":")) / 60, type number}}),
    RenomeiaColunas = Table.RenameColumns(TransformaHoraEmDecimal,{{"Column1", "Competência"}, {"Column11", "Colaborador"}, {"Column14", "Saldo"}}),
    AlteraTipoDados = Table.TransformColumnTypes(RenomeiaColunas,{{"Colaborador", type text}, {"Competência", type date}})
in
    AlteraTipoDados

 

20240130_TratamentoDados.pbix 17.91 kB · 3 downloads

Oi Bruno, muito obrigada pela solução! Consegui abrir o pbix e ir conferindo os passos que você renomeou, ficou bem fácil de entender. Acho que o principal que eu não tinha conhecimento era a função de preencher para cima e para baixo. Isso ajudou muito!

Só fiquei com uma dúvida em relação ao tipo de dados que você selecionou para as horas, você mudou para número decimal (aqui não consegui alterar dessa forma). Imaginei que precisava deixar como "duração", não é isso o mais adequado nesse caso?

Mais uma vez obrigada!

 

[Patricia Amorim Da Cunha]

2 horas atrás, Joao Raulino disse:

Suave @Patricia Amorim Da Cunha?

Vai aqui uma outra maneira de fazer baseado em filtragem seletiva e colunas customizadas:

 

E para matar a curiosidade, da sim para "deslocar" uma coluna para cima através de uma manobra criativa 😁 Para isso você tem que:

1. Duplicar a tabela original
2. Remover o numero de linhas X desta nova tabela duplicada
3. Adicionar um index na tabela original e na tabela duplicada
4. Fazer uma mescla das duas tabelas baseada no index

Neste nosso caso não foi necessário isso, mas quem sabe num próximo desafio.

Não esquece de marcar alguma das respostas como melhor solução 😉

Abs!

Dúvida na transformação dos dados.pbix 22.34 kB · 3 downloads

Oi João, muito obrigada pela solução! 

A filtragem seletiva seria selecionar o filtro no cabeçalho da coluna e depois filtros de texto?

E entendi sobre a questão de deslocar a linha para cima, faz sentido! Mas realmente o que me faltou nesse caso foi não saber do botão de preencher para cima e para baixo, o que tornou tudo bem mais simples!

 

Muito obrigada pela ajuda!

[Joao Raulino]

Isso @Patricia Amorim Da Cunha!

Se você reparar no código, algumas condições especiais de filtragem são aplicadas:

Mas realmente o FillUp/FillDown é o segredo por trás dessa ETL.

Fico feliz que deu certo por ai 😉

Abs!