O Power Query realmente é um suplemento fantástico quando se trata de ferramente de ETL. Se você ainda não sabe o que é Power Query, leia os artigos O que é Power Query e Como Instalar o Power Query no Excel.
Estou escrevendo este artigo, porque foi essa função que mudou a vida de um aluno, ele é Analista de grande banco privado.
Imagina que você exporta todos os meses um arquivo do seu ERP com as vendas diárias, ou alguém preenche esta planilha diariamente. Todos os meses eles enviam um novo arquivo do mês fechado. Você precisaria copiar os novos dados em uma nova planilha e atualizar manualmente.
Veja só como o Power Query é ideal para esta situação. Vamos para um novo cenário. Crie uma nova pasta que você irá salvar todos os meses os novos arquivos, pode ser na rede ou até no OneDrive.
Salve o arquivo na pasta e vamos abrir o Microsoft Excel ou o Power BI, os passos no Excel ou no Power BI são praticamente iguais.
Importar de Pasta no Power Query
Agora vamos pro Excel e vamos importar os arquivos desta Pasta que foi criada.
Irá abrir uma janela para selecionar o caminho da pasta. Procure a pasta que foi criada e clique em OK.
A próxima janela Irá exibir o conteúdo desta pasta. Temos 3 Opções para clicar, mas neste artigo vou mostrar o que considero a melhor prática para deixar o Editor de Consulta mais limpo e fácil de trabalhar, por isso clique em Transformar Dados se for Excel e Editar se for Power BI.
Foi criado uma Consulta onde é exibido alguns tributos dos arquivos como Nome, Extensão, Data de Criação, Data Última Modificação, Caminho, etc.
Aqui podemos fazer filtros como por exemplo, importar somente arquivos com extensão xlsx, somente arquivos contendo o nome BD, e assim por diante.
No nosso exemplo queremos somente o conteúdo da coluna binária Content, mas vou exibir também o nome do arquivo que pode ajudar em outras ocasiões.
Então Clique no cabeçalho Content e segurando e tecla CTRL clique também na coluna Name. Clique com o botão direito no nome de uma das duas colunas e clique em Remover Outras Colunas. O mesmo passo no Power BI.
Remover Colunas no Power Query
Agora vamos adicionar uma nova Coluna Personalizada. Clique na guia Adicionar Coluna e clique em Coluna Personalizada. Idem Power BI.
Na Janela que abrir, dê o nome de ObterDados (Este pode ser qualquer nome) e na fórmula de coluna personalizada digite a fómula abaixo e clique em OK.
Excel.Workbook([Content],true)
Será adicionado uma nova coluna com conteúdo Table. Vamos expandir esta coluna. Clique ícone do lado direito no cabeçalho ao lado do nome ObterDados Clique em Selecionar Tudo, Desmarque a caixa Use o nome da coluna original como prefixo e clique em OK.
Teremos 5 Novas Colunas que vou explicar abaixo.
- Name.1: São os nomes que possuem na planilha, como nome da planilha, nome da tabela, ou nome do intervalo nomeado, etc. Neste caso, quando há tabelas sempre vamos dar preferência para as tabelas.
- Data: É o conteúdo do Name. São informações que estão na planilha, tabela ou intervalo nomeado.
- Item: É a mesma coisa que o Name.1
- Kind: É o tipo do Objeto, ou seja, se é uma Planilha, Tabela, Intervalo Nomeado. Sheet = Planilha, Table = Tabela.
- Hidden: Se está oculto ou não.
Como temos tabela, vamos filtrar a coluna Kind e vamos selecionar Table.
Agora temos somente as informações das Tabelas, vamos então excluir as colunas que não são mais necessárias.
Selecione as colunas Content, Name.1, Item, Kind e Hidden, clique com o botão direito do mouse em uma das colunas selecionadas e clique em Remover Colunas.
Calma, já estamos terminando.
Vamos expandir a coluna Data, clique no ícone de expandir e clique em OK.
Pronto, estamos com os nossos dados de Janeiro e Fevereiro. Agora vamos alterar os tipos de dados. Clique no ícone ABC123 e selecione Data para o campo Data e Número Decimal para os Valores.
Caso queira manter o nome da planilha e deixá-la com o formato melhor vamos extrair somente o nome que nos interessa que é o nome do mês.
Clique na coluna Name, Guia Transformar, Extrair, Texto entre Delimitadores.
Nos delimitadores é depois do hífen e antes do ponto certo, assim buscamos o nome e clique em OK
Agora temos uma coluna com o nome correto.
Perceba também que todos os passos que fizemos estão ao lado direito da tela.
Está feito, agora é só fechar a aplicar. Agora toda vez que salvar um novo arquivo na pasta com a mesma estrutura os novos dados serão adicionados na consulta.
Perceba agora que em Consultas temos apenas uma com o nome Dados.
Agora se ao invés de transformar dados ou editar consultas, escolher Combinar e Editar o resultado seria o mesmo, mas olha quantas consultas ele teria criado.
Então é Isso pessoal. Espero que este artigo possa ajudá-los.
Me siga nas Redes Sociais
Youtube: http://www.youtube.com/c/MinhasplanilhasBr
Linkedin: https://www.linkedin.com/in/gersonggv
Instagram: https://instagram.com/mplanilhas
Facebook: https://www.facebook.com/MinhasPlanilhasBR
Portal de Cursos: https://cursos.minhasplanilhas.com.br
Loja Virtual de Planilhas: https://lojavirtual.minhasplanilhas.com.br
E se for arquivo em csv?
funciona da mesma forma
Qual benefício de fazer desta forma? Apenas reduzir as pastas, ou ele deixa o arquivo mais leve?
Lucca, se torna mais prático e objetivo. imagina que você trabalha com as mesmas informações todo tempo ( diária, semanal, mensal), fazendo desta forma você ganha em praticidade. no fim basta você apertar f5, e todas as sua informações estarão atualizadas, assim como suas planilhas dinâmicas e dashboards.
Boa tarde – Para novas planilhas de dados (Adicionados na pasta conectada), como remover os cabeçalhos a partir da segunda planilha add?