Pastas Dinâmicas no Power Query do Excel
Às vezes você quer fazer um projeto para o seu cliente ou empresa para carregar um monte de arquivos a partir de uma pasta do Windows. Nesta situação, você pode ter apenas alguns dados fictícios ou arquivos parciais. Talvez você tenha um conjunto de arquivos desatualizados apenas para obter os layouts (isso realmente acontece muito, por causa da natureza dos dados, que estão sempre mudando). Mas há outra coisa comum em projetos: talvez você precise alternar pastas para necessidades específicas, como teste e produção! Continue lendo, porque eu vou te ensinar aqui como fazer isso.
Podemos repetir as consultas com diferentes pastas sem ter que construí-las novamente
Neste artigo, eu vou cobrir 2 coisas que as pessoas me perguntam muito: A primeira é como obter dados de vários arquivos em uma pasta. Você verá como é fácil fazer isso usando uma das poderosas ferramentas de dados que vem dentro do Excel: o Power Query. Outro tópico que vou descrever passo a passo neste tutorial é como alternar facilmente pastas usadas para consultas no Excel. Você verá que podemos repetir as consultas com diferentes pastas sem ter que construí-las novamente a cada vez que quisermos especificar uma nova pasta para obter dados. Vamos nessa!
Passo 1: Célula Nomeada com Validação de Dados
A primeira coisa que você precisa ter para um trabalho bem organizado é uma célula nomeada com um nome fácil de lembrar. Como estou trabalhando com extração de dados de uma pasta, o nome que estou definindo para a minha célula é Pasta_Extração. Fácil, não é? Tenha ele em mente e siga meus passos. Você verá que se você se lembrar do nome, você evitará ter que fechar uma tela para procurar por ele quando você executar um dos passos à frente. Se você não sabe como nomear a célula, é simples: basta clicar na célula, ir até a Caixa de Nomes (o retângulo vermelho desta imagem), escrever o nome e pressionar ENTER.
Uma coisa que não é obrigatória, mas recomendo fortemente, é aplicar Validação de Dados nesta célula com as possíveis pastas que você pode usar neste trabalho. Para algumas pessoas, serão períodos diferentes. Para outros, serão regiões, clientes, categorias de produtos ou até mesmo contas ou clientes. Aqui, nos meus exemplos, vou alternar entre ambientes de Produção e Teste. Eu não recomendo deixar esta célula totalmente livre sem a validação, porque a escrita livre pode trazer a possibilidade de falha devido a erro de digitação.
Dica: Copie o caminho da pasta desta célula com Ctrl+C antes do próximo passo e economize o seu tempo.
Passo 2: Obtendo Dados da Pasta
Dentro da guia Dados, basta encontrar o comando dentro do grupo Obter e Transformar Dados que permite obter dados de vários arquivos dentro de uma pasta: Obter dados, De Arquivo, Da Pasta.
Passo 3: Escolha a Pasta
Aqui você digita ou seleciona uma das pastas válidas de sua tarefa. Ela será alterada no futuro, mas para facilitar sua vida, certifique-se de que a pasta atual é válida e contém arquivos válidos. Se você estiver lendo todas as instruções com atenção, simplesmente pressione Ctrl+V e siga em frente.
Passo 4: Transformar Dados
É simples: basta dar uma olhada se as informações sobre os arquivos fazem sentido e pressionar Transformar Dados.
Passo 5: Combinar Arquivos
É isso: pressione o botão Combinar Arquivos para reunir todos os dados.
Passo 6: Confira o Conteúdo
Aqui você confere se o conteúdo parece ok para você. Talvez seja uma boa ideia alterar o arquivo de amostra no primeiro dropdown, onde está selectionado agora o primeiro arquivo. Experimente outros e veja se está tudo certo. Assim que você terminar de conferir, basta pressionar OK.
Passo 7: Remova a Primeira Coluna ou Modifique Ela
Às vezes é uma boa ideia rastrear a fonte dos dados. Se você está trabalhando com períodos, você provavelmente vai querer manter essas informações em uma coluna. Eu só acho que neste caso você poderia modificar esta coluna, porque ter os nomes dos arquivos lá não aparenta tão bem para mim. Você pode gastar um momento para remover a extensão e o prefixo ou sufixo. No meu exemplo, eu não preciso dessa informação, então eu estou removendo a primeira coluna. Tchau, Source.Name!
Passo 8: Abra o Editor Avançado
Vamos nos divertir dentro do Editor Avançado!
Passo 9: Substitua o Caminho da Pasta por uma Variável
Aqui vem o truque: Substitua o caminho completo da pasta por uma variável. Se você nunca fez um programa antes, basta pensar em uma variável como algo que pode variar (e ela vai). Não se esqueça das aspas. Você precisa removê-las, já que você quer que o Power Query pare de interpretar isso como um texto. Os nomes das variáveis seguem algumas regras e cada linguagem tem seus próprios comandos (neste caso, estamos vendo a Linguagem M), por isso é muito sábio evitar escolher nomes muito óbvios que possam existir no linguagem, como Arquivos ou Pasta, por exemplo. Estou escolhendo MinhaPasta como o nome da minha variável.
Passo 9: Apague o Caminho da Pasta e as Aspas
Passo 9: Escreva o Nome de uma Variável
Passo 10: Insira a Linha Mágica no Código
Acima da linha da Fonte, onde você substituiu um caminho de pasta por uma variável, use este comando abaixo, certificando-se de que no lugar dos sublinhados, você escreva o mesmo nome que você deu para a célula na primeira etapa deste tutorial (lembra do que eu te falei?):
MinhaPasta = Excel.CurrentWorkbook(){[Name="__________"]}[Content][Column1]{0},
Nesta imagem abaixo, estou primeiro representando a MinhaPasta com um caminho completo fixo para a pasta, de uma maneira que você pode ver como este mecanismo é capaz de transportar as informações para dentro dos parênteses da próxima linha de código. Você consegue ver o que MinhaPasta realmente é?
Passo 10: Veja o Significado de MinhaPasta
Agora, vamos aplicar a linha mágica em todo o seu potencial, incluindo o nome da célula que você esqueceu: Pasta_Extração. Olhar: Now, let's apply the magic line in it's full potential, including the name of the cell that you forgot: Extraction_Folder. Look:
Passo 10: Insira a Linha Mágica Completa no Código
Passo 11: Carregue os Dados
Confirme tudo e veja os dados fluindo em uma nova planilha do Excel.
Passo 12: Brinque com a Planilha!
Que tal mudar aquela célula inicial para outro caminho de pasta para ver o que acontece? Experimente isso e atualize sua consulta!
Vê aqui? Está funcionando! Em vez de carregar 178 mil linhas, está carregando 78 mil agora. Os números também são diferentes, pois os valores vêm de arquivos diferentes de outra pasta. Mas a consulta é a mesma!
Passo 12: Atualize a Consulta!
Se você gostou deste artigo, deixe o seu contato em um formulário abaixo. Eu ficarei feliz em enviar pra você mais tutoriais de Excel, dicas e truques. Eu realmente encorajo você a usar os seus próprios arquivos enquanto executar os passos acima, mas se você precisar de alguma ajuda com dados fictícios para praticar, é só você responder minha primeira mensagem depois que você deixar o seu e-mail no formulário abaixo. Vejo você em breve!
Cristiano Galvão é Microsoft MVP na categoria in the Office Apps & Services, premiado pelas suas contribuições principalmente em Excel. Ele é o organizador do Excel Weekend, o maior e mais legal evento de Excel na América Latina, e também o criador de conteúdo do canal Excel Turbo no YouTube, que é parte do programa Microsoft Creators, curado pela Microsoft. Cristiano é o revisor técnico dos livros mais importantes sobre Excel traduzidos do inglês para o português, e tem cursos publicados com a maior firma de investimentos do Brazil e com a maior rede profissional do mundo. Cristiano é um consultor internacional e está sempre pronto para ajudar você a resolver problemas complexos em dados corporativos.