Como usar o Power Query no Excel

O Microsoft Excel oferece diversas ferramentas e recursos para manipulação de dados, e o Power Query é um dos melhores. Esta ferramenta de análise de negócios permite importar dados de várias fontes e transformá-los e manipulá-los facilmente no Excel, conforme necessário. Basicamente, elimina tarefas repetitivas e pode ajudar a reduzir esforço e economizar tempo.

Uma grande vantagem do Power Query é que você não precisa de nenhum conhecimento ou experiência em codificação para usá-lo. Vejamos como você pode usá-lo para manipular dados no Microsoft Excel.

Acessando o Power Query

O Power Query está disponível em todas as versões do Microsoft Excel, começando com o Excel 2010. A partir do Excel 2016, ele foi incorporado diretamente ao aplicativo.

No Excel 2016 e posterior

• Abra uma nova planilha do Excel e clique na guia ‘Dados’ na barra de menu.

• Nas opções da guia ‘Dados’, clique na opção ‘Obter dados’ no canto superior esquerdo, abaixo da barra de menu.

• Contém todas as ferramentas e opções do Power Query para importar e transformar dados.

No Excel 2013 e 2010

Para as versões 2013 e 2010 do Excel, o Power Query está disponível como um complemento gratuito que você pode baixar no site da Microsoft.

• Vá para a página de download do Power Query e clique no botão ‘Download’ para iniciar o download da ferramenta.

• Ao clicar no botão ‘Download’, você verá algumas opções nas quais poderá selecionar a apropriada dependendo do seu sistema.

• Após selecionar a opção correta, clique no botão ‘Download’ para baixar a ferramenta.

Usando a ferramenta Power Query

Com uma planilha do Excel aberta, você pode acessar a ferramenta Power Query na aba ‘Dados’ e depois na opção ‘Obter Dados’.

Importando Dados

• Ao clicar na opção ‘Obter dados’, serão mostradas as diversas fontes de onde você pode importar dados. Isso inclui pastas de trabalho do Excel, arquivos de texto ou CSV, arquivos XML e JSON . Além destes, você pode importar dados de bancos de dados online como SQL Server e Microsoft Access, entre outros. Outras fontes das quais você pode importar dados incluem Microsoft Azure e serviços online, como Salesforce e Facebook.
• Para importar dados, clique em qualquer uma das opções, como ‘Do arquivo’, ‘Do banco de dados’, ‘Do Azure’, ‘Dos serviços online’ e ‘De outras fontes’.

• Ao importar dados, o Excel mostrará um pop-up exibindo uma prévia dos dados que serão carregados. Clique no botão ‘Carregar’ na parte inferior para finalizar a importação dos dados.

• Agora você verá os dados em sua planilha Excel e poderá aplicar diferentes transformações a eles.

Componentes do Editor do Power Query

• Você precisa do Editor do Power Query para transformar os dados importados conforme necessário. Clique em ‘Iniciar Editor do Power Query’ depois de clicar no botão ‘Obter dados’.

• Isso iniciará o ‘Power Query Editor’, que é composto por seis componentes principais. Na parte superior, você encontrará a ‘Faixa do Editor de Consultas’, que contém vários comandos em diferentes guias.

• Abaixo da ‘Faixa do Editor de Consultas’ no lado esquerdo está a ‘Lista de Consultas’, que mostra todas as consultas na pasta de trabalho. Haverá também uma seção ‘Data Preview’ no centro, que mostra todas as transformações aplicadas aos dados.

• A ‘Barra de Fórmulas’ permite editar o código M da etapa de transformação. Todas as transformações são registradas e aparecem como etapas na área ‘Etapas Aplicadas’.

• A seção ‘Propriedades’ permite que você forneça nomes às consultas.

Aplicando Transformações

Você pode aplicar várias transformações aos dados importados no Editor do Power Query. Isso inclui formações de texto, corte, transposição e muito mais.

Transformações de texto

O texto pode ser transformado em maiúsculas ou minúsculas após importá-lo para o Editor.

• No Power Query Editor, vá para a guia ‘Transformar’ na parte superior e você verá várias opções, como ‘Transpor’, ‘Substituir Valores’, etc.

• A opção ‘Formatar’ está presente no centro, ao lado da opção ‘Dividir Coluna’. Clique nele para ver as opções de formatação disponíveis.

• Clique em qualquer opção, como ‘minúsculas’ ou ‘MAIÚSCULAS’, para transformar o texto da coluna selecionada em minúsculas ou maiúsculas. Da mesma forma, clicar em outras opções transformará o texto de acordo.

• A opção ‘Formatar’ também permite remover todos os espaços em branco usando a opção ‘Cortar’. Quando você clica no botão ‘Cortar’, todos os espaços em branco extras do texto serão removidos.

Dividindo Colunas

Além de transformar o texto, o Power Query Editor permite dividir colunas de várias maneiras.

• Depois de importar os dados para o Editor do Power Query, clique no cabeçalho da coluna para selecionar a coluna inteira.

• Em seguida, clique no botão ‘Dividir coluna’ à esquerda do botão ‘Formatar’. Isto lhe dará uma lista de opções que permitem dividir a coluna selecionada de diferentes maneiras.

• Para dividir a coluna por delimitador, clique na respectiva opção. Isso mostrará o pop-up de divisão por delimitador, onde você pode selecionar o delimitador, como vírgula, dois pontos, sinal de igual, etc.

• Clique no botão ‘OK’ para dividir a coluna conforme desejado e você verá que a coluna foi dividida.

Transposição de dados

Com a opção ‘Transpor’, os usuários podem alternar dados de linhas para colunas ou vice-versa. Para fazer isso, primeiro importe os dados para o Editor do Power Query, conforme explicado anteriormente.

• Após carregar os dados, vá até a aba ‘Transformar’ na parte superior, onde você encontrará a opção ‘Transpor’.

• Clique na opção ‘Transpor’ para converter as linhas em colunas.

Combinando consultas

O Power Query permite combinar facilmente vários conjuntos de dados usando as opções ‘Mesclar’ e ‘Anexar’.

Usando a opção Mesclar

A operação Merge permite criar uma nova consulta combinando consultas existentes.

• Primeiro, importe os dados para a planilha do Excel de um arquivo, banco de dados ou outras fontes. Nesse caso, você não precisa carregar os dados no Editor do Power Query, mas precisará importar vários conjuntos de dados.

• Você verá outra opção, ‘Combinar Consultas’, abaixo das opções de importação de dados. Aponte o cursor para esta opção e duas opções estarão disponíveis – Anexar e Mesclar.

• Clicar no botão ‘Mesclar’ mostrará um novo pop-up onde você pode selecionar os conjuntos de dados que devem ser mesclados.

• A seleção dos conjuntos de dados mostrará uma visualização. No canto inferior esquerdo, você pode selecionar como deseja mesclar os conjuntos de dados antes de clicar no botão ‘OK’.

Usando a opção Anexar

A opção ‘Anexar’ permite criar uma nova tabela combinando as linhas das consultas anteriores.

• Siga o mesmo procedimento acima para adicionar os conjuntos de dados à planilha Excel e depois vá para a opção ‘Anexar’ na seção ‘Combinar Consultas’.

• No pop-up que aparece, selecione as tabelas para as quais os dados precisam ser combinados antes de clicar no botão ‘OK’. Os usuários podem combinar dados de duas tabelas ou de três ou mais tabelas.

• Os dados combinados aparecerão na janela do Power Query Editor, de onde você pode importá-los para a planilha usando o botão ‘Fechar e Carregar’ no lado superior esquerdo.

Carregando dados na planilha

Quando todas as suas operações forem concluídas no Power Query Editor, você precisará carregar os dados em sua planilha do Excel.

• Existem várias maneiras de carregar os dados transformados em sua planilha do Excel, como um gráfico dinâmico, uma tabela dinâmica, uma tabela ou uma conexão para a consulta. Clique na opção ‘Fechar e carregar’ no canto superior esquerdo e você verá duas opções – ‘Fechar e carregar’ e ‘Fechar e carregar para’.

• Clicar na segunda opção mostrará as várias opções para carregar os dados na planilha.

• O Excel permite escolher o local, como uma célula em uma planilha existente ou uma nova planilha que será criada automaticamente. Também existe a opção ‘Adicionar estes dados ao modelo de dados’.

Usando Fórmulas e Funções

O Power Query também permite o uso de fórmulas e funções semelhantes às planilhas do Excel. Isso requer a adição de colunas personalizadas onde você pode adicionar fórmulas e funções.

• Inicie o Editor do Power Query na guia ‘Obter dados’ e vá para a guia ‘Adicionar coluna’ na parte superior.

• No lado esquerdo, você verá suas dúvidas. Selecione uma clicando nela e a ‘Coluna Personalizada’ ficará ativa. Crie uma nova coluna clicando na opção ‘Coluna Personalizada’.

• Na caixa de diálogo para criar uma coluna personalizada, forneça um nome para a coluna.

• Na seção ‘Fórmula de coluna personalizada’, adicione uma fórmula para criar a coluna. Por exemplo, use uma fórmula como [First Name]&""&[Last Name]. O Editor do Power Query verificará se há algum erro na fórmula.

• Se não houver erros, clique no botão ‘OK’ e o editor criará uma coluna.

• Para usar uma função, repita as etapas até que o pop-up ‘Coluna Personalizada’ apareça. Na seção ‘Fórmula de coluna personalizada’, adicione uma função, como Text.Upper([Full Name]), que criará todos os nomes em letras maiúsculas.

• Para finalizar a adição da coluna, clique no botão ‘OK’ para criar uma coluna com os nomes em letras maiúsculas.

Isso é tudo que você precisa saber para começar a usar o Power Query. Esta ferramenta torna incrivelmente fácil transformar dados no Microsoft Excel conforme necessário, para que você possa analisar e tirar conclusões com o mínimo de esforço. Ele pode ser usado para combinar diferentes conjuntos de dados, alterar sua formatação e realizar outras ações. E você ainda pode usar funções e fórmulas do Excel com o editor, o que o torna ainda mais útil.