ETL

[Marcus Cordeiro]

O processo que estou desenvolvendo, baixo um arquivo em XLS da auditoria feita na loja, decidi fazer a extração da pasta, ja que os arquivos possuem as mesmas estruturas, porém preciso transformar uma celula em coluna, que é a de data de execução da auditoria, e a outra célula é a de Filial.

Em outro tópico, foi resolvido a questão de adicionar uma coluna de acordo com uma célula para se repetir em todas as linhas dessa forma:

Porém, quando adiciono outro arquivo, de mesma estrutura, para entrar na análise a localização da célula de referencia para criar uma nova coluna muda, qual a melhor maneira de fazer essa extração?
Usando somente um arquivo o processo fica correto, estou travando apenas pra conseguir fazer o ETL de todos ou apenas dos arquivos que precisar avaliar


 

[Bruno Abdalla de Souza]

@Marcus Cordeiro, baseado no seu ETL eu criei uma função personalizada, que recebe uma tabela (seria cada arquivo Excel) e retorna a tabela tratada. Depois usei a função personalizada para fazer o tratamento de cada tabela (que estava em cada linha qdo vc faz a conexão pela pasta).

Em relação à data, precisamos avaliar qual a regra para achar a célula da data. Sugiro criar outro tópico para isso, pois é outro problema.

[Bruno Abdalla de Souza]

POr favor, anexa 2 arquivos ou mais para simular e anexa tb o pbix com o que foi feito. Aí conseguimos reproduzir o que está feito e reavaliar as modificações necessárias.

[Marcus Cordeiro]

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

POr favor, anexa 2 arquivos ou mais para simular e anexa tb o pbix com o que foi feito. Aí conseguimos reproduzir o que está feito e reavaliar as modificações necessárias.

Esses são os dois formatos de extração do aplicativo, ticket a ticket (preciso filtrar para encontrar um a um) ou extrair uma sequencia de N tickets.
ps: o arquivo pbix ta puxando como fonte uma pasta onde o alterei o nome do arquivo e a aba da planilha do excel pelo numero do ticket

ProjetoN7.pbix Relatório Tickets(6).xls Relatório Tickets(7).xls Relatório Tickets(8).xls

[Bruno Abdalla de Souza]

Criei a seguinte função parar tratar a tabela:

(tabela as table) as table => 

let
    Fonte = tabela,
    #"Outras Colunas Removidas1" = Table.SelectColumns(Fonte,{"Data"}),
    Data = #"Outras Colunas Removidas1"{0}[Data],
    #"Colunas Removidas" = Table.RemoveColumns(Data,{"Column1", "Column4", "Column8"}),
    #"Linhas Filtradas" = Table.SelectRows(#"Colunas Removidas", each [Column5] <> null and [Column5] <> ""),
    #"Personalização Adicionada1" = Table.AddColumn(#"Linhas Filtradas", "DataFiscalizacao", each #"Linhas Filtradas"[Column7]{1}),
    #"Personalização Adicionada2" = Table.AddColumn(#"Personalização Adicionada1", "Personalizar", each #"Personalização Adicionada1" [Column5]{1}),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Personalização Adicionada2",{{"Personalizar", type text}}),
    #"Texto Inserido Antes do Delimitador" = Table.AddColumn(#"Tipo Alterado", "DataFiscal", each Text.BeforeDelimiter([DataFiscalizacao], " "), type text),
    #"Linhas Filtradas1" = Table.SelectRows(#"Texto Inserido Antes do Delimitador", each ([Column9] <> null)),
    #"Outras Colunas Removidas2" = Table.SelectColumns(#"Linhas Filtradas1",{"Column2", "Column3", "Column6", "Column7", "Column9", "DataFiscalizacao", "Personalizar", "DataFiscal"}),
    #"Texto Inserido Antes do Delimitador1" = Table.AddColumn(#"Outras Colunas Removidas2", "Hierarquia", each Text.BeforeDelimiter([Column3], " -"), type text),
    #"Coluna Condicional Adicionada" = Table.AddColumn(#"Texto Inserido Antes do Delimitador1", "Setor", each if Text.StartsWith([Hierarquia], "1") then "Frios" else if Text.StartsWith([Hierarquia], "2") then "Açougue" else if Text.StartsWith([Hierarquia], "3") then "Peixaria" else if Text.StartsWith([Hierarquia], "4") then "Hortifruti/Granjeiro" else if Text.StartsWith([Hierarquia], "5") then "Padaria/Lanchonete" else if Text.StartsWith([Hierarquia], "6") then "Lanchonete/Rotisseria" else if Text.StartsWith([Hierarquia], "7") then "Empório" else if Text.StartsWith([Hierarquia], "8") then "Refeitório" else if Text.StartsWith([Hierarquia], "9") then "Loja" else if Text.StartsWith([Hierarquia], "10") then "Documentação" else null),
    #"Texto Inserido Antes do Delimitador2" = Table.AddColumn(#"Coluna Condicional Adicionada", "Hierarquia_Area", each Text.BeforeDelimiter([Hierarquia], ".", 1), type text),
    #"Coluna Condicional Adicionada1" = Table.AddColumn(#"Texto Inserido Antes do Delimitador2", "Area", each if Text.StartsWith([Hierarquia_Area], "8") then "Refeitório" else if Text.StartsWith([Hierarquia_Area], "9") then "Loja" else if Text.StartsWith([Hierarquia_Area], "10") then "Documentação" else if Text.EndsWith([Hierarquia_Area], "1") then "Área de Manipulação" else if Text.EndsWith([Hierarquia_Area], "2") then "Câmara" else if Text.EndsWith([Hierarquia_Area], "3") then "Área de Vendas" else null),
    #"Linhas Filtradas2" = Table.SelectRows(#"Coluna Condicional Adicionada1", each ([Column6] <> null)),
    #"Linhas Superiores Removidas" = Table.Skip(#"Linhas Filtradas2",1),
    #"Colunas Reordenadas" = Table.ReorderColumns(#"Linhas Superiores Removidas",{"Column2", "Area", "Hierarquia_Area", "Setor", "Hierarquia", "Column3", "Column6", "Column7", "Column9", "DataFiscalizacao", "Personalizar", "DataFiscal"}),
    #"Outras Colunas Removidas3" = Table.SelectColumns(#"Colunas Reordenadas",{"Column2", "Area", "Setor", "Column3", "Column6", "Column7", "Column9", "Personalizar", "DataFiscal"}),
    #"Colunas Renomeadas" = Table.RenameColumns(#"Outras Colunas Removidas3",{{"Personalizar", "Filial"}}),
    #"Tipo Alterado1" = Table.TransformColumnTypes(#"Colunas Renomeadas",{{"DataFiscal", type date}}),
    #"Colunas Renomeadas1" = Table.RenameColumns(#"Tipo Alterado1",{{"Column9", "Comentario"}, {"Column7", "Auditor"}, {"Column6", "Conformidade"}, {"Column2", "Ticket"}, {"Column3", "Questionario"}}),
    #"Tipo Alterado2" = Table.TransformColumnTypes(#"Colunas Renomeadas1",{{"Ticket", type text}, {"Area", type text}, {"Setor", type text}}),
    #"Texto Inserido Após o Delimitador" = Table.AddColumn(#"Tipo Alterado2", "Mes/Ano", each Text.AfterDelimiter(Text.From([DataFiscal], "pt-BR"), "/"), type text)
in
    #"Texto Inserido Após o Delimitador"

Depois usei esta função para tratar cada tabela original de cada arquivo da pasta. Depois disso, foi só expandir os dados.

let
    Fonte = Folder.Files("C:\CaminhoDaPasta"),
    #"Personalização Adicionada" = Table.AddColumn(Fonte, "Personalizar", each Excel.Workbook([Content])),
    #"Outras Colunas Removidas" = Table.SelectColumns(#"Personalização Adicionada",{"Personalizar"}),
    #"Personalização Adicionada1" = Table.AddColumn(#"Outras Colunas Removidas", "TabelaFinal", each fxTrataTabela([Personalizar])),
    #"Outras Colunas Removidas1" = Table.SelectColumns(#"Personalização Adicionada1",{"TabelaFinal"}),
    #"TabelaFinal Expandido" = Table.ExpandTableColumn(#"Outras Colunas Removidas1", "TabelaFinal", {"Ticket", "Area", "Setor", "Questionario", "Conformidade", "Auditor", "Comentario", "Filial", "DataFiscal", "Mes/Ano"}, {"Ticket", "Area", "Setor", "Questionario", "Conformidade", "Auditor", "Comentario", "Filial", "DataFiscal", "Mes/Ano"})
in
    #"TabelaFinal Expandido"

Sò que as colunas de data estão com erro, pois não sei qual a célula que tem referenciar corretamente a data.

20240410_ProjetoN7.pbix

[Marcus Cordeiro]

Show Brunão! Me explica melhor sobre essa segunda formula, no caso a primeira formula tu tratou somente uma tabela, aí depois imputou a segunda formula no próprio editor avançado ? e ai logo depois jogou a tabela dentro da pasta para fazer o tratamento de todas ?

Em relação a coluna de data, consegui da seguinte forma
= Table.AddColumn(#"Linhas Filtradas", "Personalizar", each #"Linhas Filtradas"[Column7]{1})
só que como precisava replicar em outras tabelas, a localização da célula da data acabava alterando

 

O mesmo serve para a FIlial, no arquivo que você me envio, a filial ficou como auditoria de segurança de alimentos, acredito que tenha sido somente uma referência inconforme