Combine dados de várias pastas de trabalho no Excel (usando o Power Query)

O Power Query pode ser de grande ajuda quando você deseja combinar várias pastas de trabalho em uma única pasta de trabalho.

Por exemplo, suponha que você tenha os dados de vendas para diferentes regiões (Leste, Oeste, Norte e Sul). Você pode combinar esses dados de diferentes pastas de trabalho em uma única planilha usando o Power Query.

Se você tiver essas pastas de trabalho em locais / pastas diferentes, é uma boa ideia mover todas elas para uma única pasta (ou criar uma cópia e colocar essa cópia da pasta de trabalho na mesma pasta).

Portanto, para começar, tenho quatro pastas de trabalho em uma pasta (conforme mostrado abaixo).

Agora, neste tutorial, estou cobrindo três cenários onde você pode combinar os dados de diferentes pastas de trabalho usando o Power Query:

• Cada pasta de trabalho tem os dados em uma Tabela do Excel e todos os nomes de tabela são os mesmos.
• Cada pasta de trabalho possui os dados com o mesmo nome de planilha. Este pode ser o caso quando há uma planilha chamada ‘resumo’ ou ‘dados’ em todas as pastas de trabalho e você deseja combinar tudo isso.
• Cada pasta de trabalho tem muitas planilhas e tabelas, e você deseja combinar tabelas / planilhas específicas. Este método também pode ser útil quando você deseja combinar tabela / planilhas que não têm um nome consistente.

Vamos ver como combinar dados dessas pastas de trabalho em cada caso.

Cada pasta de trabalho possui os dados em uma tabela do Excel com a mesma estrutura

A técnica abaixo funcionaria quando suas tabelas do Excel fossem estruturadas da mesma maneira (mesmos nomes de coluna).

O número de linhas em cada tabela pode variar.

Não se preocupe se algumas das tabelas do Excel tiverem colunas adicionais. Você pode escolher uma das tabelas como modelo (ou como a "chave", como o Power Query a chama), e o Power Query a usaria para combinar todas as outras tabelas do Excel com ela.

Caso existam colunas adicionais em outras tabelas, essas serão ignoradas e apenas as especificadas no modelo / chave serão combinadas. Por exemplo, se a tabela de modelo / chave selecionada tiver 5 colunas e uma das tabelas em alguma outra pasta de trabalho tiver 2 colunas adicionais, essas colunas adicionais serão ignoradas.

Agora tenho quatro pastas de trabalho em uma pasta que desejo combinar.

Abaixo está um instantâneo da tabela que tenho em uma das pastas de trabalho.

Aqui estão as etapas para combinar os dados dessas pastas de trabalho em uma única pasta de trabalho (como uma única tabela).

1. Vá para a guia Dados.
2. No grupo Get & Transform, clique no menu suspenso New Query.
3. Passe o cursor em ‘From File’ e clique em ‘From Folder’.
4. Na caixa de diálogo Pasta, digite o caminho do arquivo da pasta que contém os arquivos ou clique em Procurar e localize a pasta.
5. Clique OK.
6. Na caixa de diálogo que se abre, clique no botão combinar.
7. Clique em ‘Combinar e carregar’.
8. Na caixa de diálogo ‘Combinar arquivos’ que é aberta, selecione a tabela no painel esquerdo. Observe que o Power Query mostra a tabela do primeiro arquivo. Este arquivo atuaria como o modelo (ou a chave) para combinar outros arquivos. O Power Query iria agora procurar 'Tabela 1' em outras pastas de trabalho e combiná-la com esta.
9. Clique OK.

Isso carregará o resultado final (dados combinados) em sua planilha ativa.

Observe que, junto com os dados, o Power Query adiciona automaticamente o nome da pasta de trabalho como a primeira coluna dos dados combinados. Isso ajuda a manter o controle de quais dados vieram de qual pasta de trabalho.

Caso você queira primeiro editar os dados antes de carregá-los no Excel, na Etapa 6, selecione ‘Combinar e Editar’. Isso abrirá o resultado final no editor do Power Query, onde você pode editar os dados.

Algumas coisas para saber:

• Se você selecionar uma Tabela do Excel como modelo (na Etapa 7), o Power Query usará os nomes das colunas nesta Tabela para combinar os dados de outras Tabelas. Se outras tabelas tiverem colunas adicionais, elas serão ignoradas. No caso de essas outras tabelas não terem uma coluna, que está lá em sua Tabela de modelo, o Power Query apenas colocaria "nulo" para ela.
• As colunas não precisam estar na mesma ordem que o Power Query usa cabeçalhos de coluna para mapear colunas.
• Como você selecionou Tabela1 como a chave, o Power Query procurará pela Tabela1 em todas as pastas de trabalho e combinará todas elas. Caso não encontre uma Tabela Excel com o mesmo nome (Tabela1 neste exemplo), o Power Query apresentará um erro.

Adicionando novos arquivos à pasta

Agora, vamos tomar um minuto e entender o que fizemos com as etapas acima (que levaram apenas alguns segundos).

Combinamos os dados de quatro pastas de trabalho diferentes em uma única tabela em alguns segundos, sem nem mesmo abrir nenhuma das pastas de trabalho.

Mas isso não é tudo.

O verdadeiro PODER do Power Query é que agora, quando você adiciona mais arquivos à pasta, não precisa repetir nenhuma dessas etapas.

Tudo o que você precisa fazer é mover a nova pasta de trabalho para a pasta, atualizar a consulta e ela combinará automaticamente os dados de todas as pastas de trabalho dessa pasta.

Por exemplo, no exemplo acima, se eu adicionar uma nova pasta de trabalho - ‘Mid-West.xlsx’ para a pasta e atualizar a consulta, ele me fornecerá instantaneamente o novo conjunto de dados combinado.

Veja como você atualiza uma consulta:

• Clique com o botão direito do mouse na Tabela do Excel que você carregou na planilha e clique em Atualizar.
• Clique com o botão direito do mouse em Consulta no painel ‘Consulta da pasta de trabalho’ e clique em Atualizar
• Vá para a guia Dados e clique em Atualizar.

Cada pasta de trabalho tem os dados com o mesmo nome de planilha

Caso você não tenha os dados em uma tabela do Excel, mas todos os nomes das planilhas (das quais deseja combinar os dados) sejam os mesmos, você pode usar o método mostrado nesta seção.

Existem algumas coisas que você precisa ter cuidado quando se trata apenas de dados tabulares e não de uma tabela do Excel.

• Os nomes das planilhas devem ser iguais. Isso ajudará o Power Query a percorrer suas pastas de trabalho e combinar os dados das planilhas que têm o mesmo nome em cada pasta de trabalho.
• O Power Query diferencia maiúsculas de minúsculas. Isso significa que uma planilha chamada ‘dados’ e ‘Dados’ são considerados diferentes. Da mesma forma, uma coluna com o cabeçalho ‘Store’ e outra com ‘store’ são consideradas diferentes.
• Embora seja importante ter os mesmos cabeçalhos de coluna, não é importante ter a mesma ordem. Se a coluna 2 em ‘East.xlsx’ for a coluna 4 em ‘West.xlsx’, o Power Query fará a correspondência correta mapeando os cabeçalhos.

Agora vamos ver como combinar rapidamente dados de diferentes pastas de trabalho em que o nome da planilha é o mesmo.

Neste exemplo, tenho uma pasta com quatro arquivos.

Em cada pasta de trabalho, tenho uma planilha com o nome 'Dados' que contém os dados no formato a seguir (observe que esta não é uma tabela do Excel).

Aqui estão as etapas para combinar dados de várias pastas de trabalho em uma única planilha:

1. Vá para a guia Dados.
2. No grupo Get & Transform, clique no menu suspenso New Query.
3. Passe o cursor em ‘From File’ e clique em ‘From Folder’.
4. Na caixa de diálogo Pasta, digite o caminho do arquivo da pasta que contém os arquivos ou clique em Procurar e localize a pasta.
5. Clique OK.
6. Na caixa de diálogo que se abre, clique no botão combinar.
7. Clique em ‘Combinar e carregar’.
8. Na caixa de diálogo ‘Combinar arquivos’ que é aberta, selecione ‘Dados’ no painel esquerdo. Observe que o Power Query mostra o nome da planilha do primeiro arquivo. Este arquivo atuaria como a chave / modelo para combinar outros arquivos. O Power Query examinará cada pasta de trabalho, encontrará a planilha chamada ‘Dados e combinará tudo isso.
9. Clique OK. Agora o Power Query examinará cada pasta de trabalho, procurará a planilha chamada ‘Dados’ nela e, em seguida, combinará todos esses conjuntos de dados.

Isso carregará o resultado final (dados combinados) em sua planilha ativa.

Caso você queira primeiro editar os dados antes de carregá-los no Excel, na Etapa 6, selecione ‘Combinar e Editar’. Isso abrirá o resultado final no editor do Power Query, onde você pode editar os dados.

Cada pasta de trabalho tem os dados com nomes de tabelas ou nomes de planilhas diferentes

Às vezes, você pode não obter dados estruturados e consistentes (como tabelas com o mesmo nome ou planilha com o mesmo nome).

Por exemplo, suponha que você obtenha os dados de alguém que criou esses conjuntos de dados, mas nomeou as planilhas como Dados do Leste, Dados do Oeste, Dados do Norte e Dados do Sul.

Ou a pessoa pode ter criado tabelas do Excel, mas com nomes diferentes.

Nesses casos, você ainda pode usar o Power Query, mas precisa fazer isso com algumas etapas adicionais.

1. Vá para a guia Dados.
2. No grupo Get & Transform, clique no menu suspenso New Query.
3. Passe o cursor em ‘From File’ e clique em ‘From Folder’.
4. Na caixa de diálogo Pasta, insira o caminho do arquivo da pasta que contém os arquivos ou clique em Procurar e localize a pasta.
5. Clique OK.
6. Na caixa de diálogo que se abre, clique no botão Editar. Isso abrirá o editor do Power Query, onde você verá os detalhes de todos os arquivos da pasta.
7. Segure a tecla Control e selecione as colunas ‘Conteúdo’ e ‘Nome’, clique com o botão direito e selecione ‘Remover outras colunas’. Isso removerá todas as outras colunas, exceto as colunas selecionadas.
8. Na faixa do Editor de Consultas, clique em ‘Adicionar coluna’ e, em seguida, clique em ‘Coluna Personalizada’.
9. Na caixa de diálogo Adicionar coluna personalizada, nomeie a nova coluna como ‘Importação de dados’ e use a seguinte fórmula = Excel.Workbook ([CONTEÚDO]). Observe que esta fórmula diferencia maiúsculas de minúsculas e você precisa inseri-la exatamente da maneira que mostrei aqui.
10. Agora você verá uma nova coluna que contém a tabela. Agora deixe-me explicar o que aconteceu aqui. Você forneceu ao Power Query os nomes das pastas de trabalho, e o Power Query buscou os objetos, como planilhas, tabelas e intervalos nomeados de cada pasta de trabalho (que reside na célula Tabela a partir de agora). Você pode clicar no espaço em branco ao lado do texto Tabela e verá as informações na parte inferior. Nesse caso, como temos apenas uma tabela e uma planilha em cada pasta de trabalho, você pode ver apenas duas linhas.
11. Clique no ícone de seta dupla na parte superior da coluna ‘Importação de dados’.
12. Na caixa de dados da coluna que é aberta, desmarque "Usar coluna original como prefixo" e clique em OK.
13. Agora você verá uma tabela expandida onde verá uma linha para cada objeto na tabela. Nesse caso, para cada pasta de trabalho, o objeto de pasta e o objeto de tabela são listados separadamente.
14. Na coluna Tipo, filtre a lista para mostrar apenas a Tabela.
15. Segure a tecla Control e selecione a coluna Nome e Dados. Agora, clique com o botão direito e remova todas as outras colunas.
16. Na coluna Dados, clique no ícone de seta dupla no canto superior direito do Cabeçalho de Dados.
17. Na caixa de dados da coluna que é aberta, clique em OK. Isso combinará os dados em todas as tabelas e os mostrará no Power Query.
18. Agora você pode fazer qualquer transformação necessária e, em seguida, vá para a guia Home e clique em Close & Load.

Agora, deixe-me tentar explicar rapidamente o que fizemos aqui. Como não havia consistência nos nomes das planilhas ou tabelas, usamos a fórmula = Excel.Workbook para buscar todos os objetos das pastas de trabalho no Power Query. Esses objetos podem incluir planilhas, tabelas e intervalos nomeados. Uma vez que obtivemos todos os objetos de todos os arquivos, os filtramos para considerar apenas as tabelas do Excel. Em seguida, expandimos os dados nas tabelas e combinamos tudo isso.

Neste exemplo, filtramos os dados para usar apenas tabelas do Excel (na Etapa 13). No caso de você querer combinar folhas e não tabelas, você pode filtrar folhas.

Nota - esta técnica fornecerá os dados combinados mesmo quando houver uma incompatibilidade nos nomes das colunas. Por exemplo, se em East.xlsx, você tem uma coluna com erros ortográficos, você terá 5 colunas. O Power Query preencherá os dados nas colunas se os encontrar e, se não conseguir encontrar uma coluna, reportará o valor como ‘nulo’.

Da mesma forma, se você tiver algumas colunas adicionais em qualquer uma das planilhas das tabelas, elas serão incluídas no resultado final.

Agora, se você obtiver mais pastas de trabalho das quais precisa combinar dados, simplesmente copie e cole na pasta e atualize o Power Query