Crie tabelas com diferentes cabeçalhos de vários livros

Formulação do problema

Temos vários arquivos (no nosso exemplo – 4 peças, no caso geral – quantos você quiser) em uma pasta Relatórios:

Dentro, esses arquivos se parecem com isso:

Em que:

• A folha de dados que precisamos é sempre chamada Fotos, mas pode estar em qualquer lugar na pasta de trabalho.
  
• Além da folha Fotos Cada livro pode ter outras folhas.
• Tabelas com dados têm um número diferente de linhas e podem começar com uma linha diferente na planilha.
• Os nomes das mesmas colunas em tabelas diferentes podem diferir (por exemplo, Quantidade = Quantidade = Qtde).
• As colunas nas tabelas podem ser organizadas em uma ordem diferente.

Tarefa: coletar dados de vendas de todos os arquivos da planilha Fotos em uma tabela comum para, posteriormente, construir um resumo ou qualquer outra análise sobre ela.

Etapa 1. Preparando um diretório de nomes de colunas

A primeira coisa a fazer é preparar um livro de referência com todas as opções possíveis de nomes de colunas e sua correta interpretação:

Convertemos esta lista em uma tabela dinâmica “inteligente” usando o botão Formatar como tabela na guia Início (Início — Formatar como Tabela) ou atalho de teclado Ctrl+T e carregue-o no Power Query com o comando Dados - Da Tabela/Intervalo (Dados — Da Tabela/Intervalo). Nas versões recentes do Excel, ele foi renomeado para Com folhas (Da folha).

Na janela do editor de consultas do Power Query, tradicionalmente excluímos a etapa Tipo alterado e adicione um novo passo ao invés dele clicando no botão f_xna barra de fórmulas (se não estiver visível, você poderá habilitá-lo na guia Avaliações) e insira a fórmula lá na linguagem interna do Power Query M:

=Tabela.ParaLinhas(Fonte)

Este comando irá converter o carregado no passo anterior fonte tabela de referência em uma lista que consiste em listas aninhadas (List), cada uma das quais, por sua vez, é um par de valores Foi-se tornou de uma linha:

Precisaremos desse tipo de dados um pouco mais tarde, ao renomear em massa os cabeçalhos de todas as tabelas carregadas.

Após concluir a conversão, selecione os comandos Home — Fechar e carregar — Fechar e carregar em… e tipo de importação Basta criar uma conexão (Home — Close&Load — Close&Load to… — Só cria conexão) e volte para o Excel.

Etapa 2. Carregamos tudo de todos os arquivos como está

Agora vamos carregar o conteúdo de todos os nossos arquivos da pasta – por enquanto, como está. Escolhendo equipes Dados – Obter dados – Do arquivo – Da pasta (Dados — Obter dados — Do arquivo — Da pasta) e depois a pasta onde estão nossos livros de origem.

Na janela de visualização, clique em Converter (Transformar) or Mudar (Editar):

E, em seguida, expanda o conteúdo de todos os arquivos baixados (Binário) botão com setas duplas no cabeçalho da coluna Conteúdo:

Power Query no exemplo do primeiro arquivo (Vostok.xlsx) nos perguntará o nome da planilha que queremos tirar de cada pasta de trabalho - escolha Fotos e pressione OK:

Depois disso (de fato), ocorrerão vários eventos que não são óbvios para o usuário, cujas consequências são claramente visíveis no painel esquerdo:

1. O Power Query pegará o primeiro arquivo da pasta (teremos ele Vostok.xlsx — Vejo Exemplo de arquivo) como exemplo e importa seu conteúdo criando uma consulta Converter arquivo de amostra. Esta consulta terá alguns passos simples como fonte (acesso ao arquivo) Navegação (seleção de folhas) e possivelmente elevando os títulos. Esta solicitação só pode carregar dados de um arquivo específico Vostok.xlsx.
   
2. Com base nessa solicitação, a função associada a ela será criada Converter arquivo (indicado por um ícone característico f_x), onde o arquivo de origem não será mais uma constante, mas um valor de variável – um parâmetro. Assim, essa função pode extrair dados de qualquer livro que inserirmos nela como argumento.
   
3. A função será aplicada por sua vez a cada arquivo (Binário) da coluna Conteúdo – step é responsável por isso Chamar função personalizada em nossa consulta que adiciona uma coluna à lista de arquivos Converter arquivo com resultados de importação de cada pasta de trabalho:

   

4. Colunas extras são removidas.
5. O conteúdo das tabelas aninhadas é expandido (passo Coluna da tabela estendida) – e vemos os resultados finais da coleta de dados de todos os livros:

   
   

Etapa 3. Lixar

A captura de tela anterior mostra claramente que a montagem direta "como está" acabou sendo de baixa qualidade:

• As colunas estão invertidas.
• Muitas linhas extras (vazias e não só).
• Os cabeçalhos de tabela não são percebidos como cabeçalhos e são misturados com dados.
  

Você pode corrigir todos esses problemas com muita facilidade – basta ajustar a consulta Converter arquivo de amostra. Todos os ajustes que fizermos cairão automaticamente na função Converter arquivo associada, o que significa que eles serão usados ​​posteriormente ao importar dados de cada arquivo.

Ao abrir um pedido Converter arquivo de amostra, adicione etapas para filtrar linhas desnecessárias (por exemplo, por coluna Column2) e levantando os títulos com o botão Use a primeira linha como cabeçalhos (Use a primeira linha como cabeçalhos). A mesa ficará muito melhor.

Para que as colunas de arquivos diferentes se ajustem automaticamente umas às outras posteriormente, elas devem ter o mesmo nome. Você pode realizar essa renomeação em massa de acordo com um diretório criado anteriormente com uma linha de código M. Vamos pressionar o botão novamente f_x na barra de fórmulas e adicione uma função para alterar:

= Table.RenameColumns(#”Cabeçalhos Elevados”, Cabeçalhos, MissingField.Ignore)

Esta função pega a tabela do passo anterior Cabeçalhos elevados e renomeia todas as colunas de acordo com a lista de pesquisa aninhada headlines. Terceiro argumento MissingField.Ignorar é necessário para que nos títulos que estão no diretório, mas não estão na tabela, não ocorra um erro.

Na verdade, isso é tudo.

Voltando ao pedido Relatórios veremos uma imagem completamente diferente – muito mais bonita que a anterior:

• O que é Power Query, Power Pivot, Power BI e por que um usuário do Excel precisa deles
  
• Coletando dados de todos os arquivos em uma determinada pasta
• Coletando dados de todas as folhas do livro em uma tabela