Tutorial avançado do excel‌ consulta (obter e transformar)

Se você é uma daquelas pessoas que trabalha muito com dados, pode ser qualquer pessoa ( contador , RH, analista de dados, etc.), o PowerQuery pode ser sua ferramenta poderosa.

Deixe-me ir direto ao ponto, o Power Query é uma das habilidades avançadas do Excel que você precisa aprender e neste tutorial você explorará o PowerQuery em detalhes e aprenderá como transformar dados com ele.

Vamos começar.

O que é Excel Power Query

Power Query é um suplemento do Excel que você pode usar para ETL. Isso significa que você pode extrair dados de diferentes fontes, transformá- los e carregá-los na planilha. Pode-se dizer que o POWER QUERY é uma máquina de limpeza de dados porque possui todas as opções para transformar dados. É em tempo real e registra cada passo que você dá.

Por que você deve usar o Power Query (Benefícios)?

Se você tem essa pergunta em mente, aqui está minha resposta para você:

  • Diferentes fontes de dados : você pode carregar dados em um poderoso editor de consultas de diferentes fontes de dados, como CSV , TXT, JSON, etc.
  • Transforme dados facilmente: normalmente você usa fórmulas e tabelas dinâmicas para transformações de dados, mas com o POWER QUERY você pode fazer muito com apenas alguns cliques.
  • É em tempo real: escreva uma consulta uma vez e você poderá atualizá-la sempre que houver uma alteração nos dados, e isso transformará os novos dados que você atualizou.

Deixe-me compartilhar um exemplo:

Imagine que você tem 100 arquivos Excel contendo dados de 100 cidades e agora seu chefe quer que você crie um relatório com todos os dados desses 100 arquivos. OK, se você decidir abrir cada arquivo manualmente e copiar e colar os dados desses arquivos e precisar de pelo menos uma hora para isso.

Mas com o Power Query, você pode fazer isso em minutos. Você está se sentindo animado? BOM.

Posteriormente neste tutorial você aprenderá como usar o Power Query com muitos exemplos, mas primeiro você precisa entender seu conceito.

O conceito de demanda de energia

Para aprender a consulta avançada, você precisa entender seu conceito que funciona em 3 etapas:

1. Obtenha dados

A consulta avançada permite que você obtenha dados de diferentes fontes, como web, CSV, arquivos de texto, várias pastas de trabalho de uma pasta e muitas outras fontes onde podemos armazenar dados.

2. Transforme os dados

Depois de inserir os dados na consulta avançada, você terá várias opções que pode usar para transformá-los e limpá-los. Ele cria consultas para todas as etapas que você executa (em sequência, uma etapa após a outra).

3. Carregar dados

No Editor de Consulta Avançado, você pode carregar os dados transformados na planilha, ou pode criar diretamente uma Tabela Dinâmica ou Gráfico Dinâmico ou criar uma conexão somente de dados.

Onde está o Power Query (como instalar)?

Abaixo você pode ver como instalar o Power Query Access em diferentes versões do Microsoft Excel.

Excel 2007

Se você estiver usando o Excel 2007, lamento que o PQ não esteja disponível para esta versão, então você precisa atualizar para a latest version do Excel (Excel para Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010).

power-query-excel-2007

Excel 2010 e Excel 2013

Para 2010 e 2013 , você precisa instalar um suplemento separadamente, que pode ser baixado neste link e, uma vez instalado, você obterá uma nova guia na faixa do Excel, como abaixo:

power-query-excel-2013-2010
  • Em primeiro lugar, baixe o add-in aqui (site oficial da Microsoft).
  • Após o download do arquivo, abra-o e siga as instruções.
  • Depois disso, você obterá automaticamente a guia “Power Query” na faixa do Excel.

Se de alguma forma esta aba “POWER QUERY” não aparecer, não há necessidade de se preocupar com isso. Você pode adicioná-lo usando a opção Suplementos COM.

  • Vá para a guia Arquivo ➜ Opções ➜ Suplementos.
com-complementos-1
  • Nas opções de “Suplementos”, selecione “Suplementos COM” e clique em IR.
  • Depois disso, marque a caixa “Microsoft Power Query for Excel”.
marca de escala-microsoft-power-query-for-excel-1
  • No final, clique em OK.

Excel 2016, 2019, Office 365

Se você estiver usando Excel 2016, Excel 2019 ou tiver uma assinatura do OFFICE 365, ele já estará na guia Dados , como um grupo chamado “GET & TRANSFORM” (gosto desse nome, e você?).

obter e transformar-1

ExcelMac

Se você usa Excel no Mac, infelizmente não há suplemento de consulta avançada para isso e você só pode atualizar uma consulta existente , mas não pode criar uma nova ou mesmo editar uma consulta ( LINK ).

power-query-excel-mac

Editor de consulta avançada

O Power Query possui seu próprio editor onde você pode obter os dados, realizar todos os passos para criar as consultas e depois carregá-los na planilha. Para abrir o Editor do Power Query, você precisa ir para a guia Dados e em Obter e Transformar ➜ Obter Dados ➜ Iniciar o Editor do Power Query.

lançar-power-query-editor-1

Abaixo está uma primeira prévia do editor que você obterá ao abri-lo.

primeira olhada do editor

Agora, vamos explorar cada seção em detalhes:

1. Fita

Vejamos todas as guias disponíveis:

  • Arquivo: Na aba Arquivo você pode carregar os dados, remover o editor e abrir os parâmetros de consulta.
  • Home : Na aba HOME, você tem opções para gerenciar os dados carregados, como, excluir e mover colunas e linhas.
  • Transformar : esta guia contém todas as opções necessárias para transformar e limpar dados, como mesclar colunas, transpor, etc.
  • Adicionar coluna : aqui você tem a opção de adicionar novas colunas aos dados que possui no editor avançado.
  • Visualização : nesta guia você pode fazer alterações na visualização do Editor de Consulta Avançada e nos dados carregados.

2. Etapas aplicadas

No lado direito do editor, você tem um painel de configurações de consulta que inclui o nome da consulta e todas as etapas aplicadas em sequência.

lista de opções de etapas aplicadas

Ao clicar com o botão direito em uma etapa, você tem uma lista de opções que pode fazer, como renomear, excluir, editar, mover para cima ou para baixo, etc. e ao clicar em uma etapa, o editor o levará para a transformação feita naquela etapa.

lista de opções de etapas aplicadas

Veja abaixo onde você aplicou todas as cinco etapas no total e quando clico na 4ª etapa, levo para a transformação na etapa quatro, onde os nomes das colunas não foram alterados.

total de cinco etapas aplicadas

3. Perguntas

O painel de consultas no lado esquerdo lista todas as consultas que você tem atualmente na pasta de trabalho. É basicamente um lugar onde você pode gerenciar todas as consultas.

o painel de consultas

Ao clicar com o botão direito no nome de uma consulta, você pode ver todas as opções que pode usar (copiar, excluir, duplicar, etc.)

clique com o botão direito em um nome de consulta

Você também pode criar uma nova consulta simplesmente clicando com o botão direito no espaço vazio do painel de consultas e selecionando a opção de fonte de dados.

espaço vazio no painel de consulta

4. Barra de fórmulas

Como eu disse, toda vez que você aplica uma etapa no editor, ele gera o código M para aquela etapa, e você pode ver esse código na barra de fórmulas. Você pode simplesmente clicar na barra de fórmulas para editar o código.

Barra de Fórmula

Depois de aprender como usar o código M, você também pode criar uma etapa escrevendo o código e simplesmente clicando no botão “FX” para inserir uma etapa personalizada.

botão FX

5. Visão geral dos dados

A área de visualização de dados se parece com uma planilha do Excel, mas é um pouco diferente de uma planilha normal onde você pode editar diretamente uma célula ou dados. Quando você carrega dados no editor (faremos isso em um momento), ele exibe todas as colunas com os cabeçalhos com os nomes das colunas e depois as linhas com os dados.

a área de visualização de dados

Na parte superior de cada coluna, você pode ver o tipo de dados da coluna. Quando você carrega dados no editor, o Advanced Query aplica automaticamente o tipo de dados correto (quase sempre) a cada coluna.

Você pode clicar no botão no canto superior esquerdo do cabeçalho da coluna para alterar o tipo de dados aplicado à coluna. Ele contém uma lista de todos os tipos de dados dos quais você pode extrair.

botão no canto superior esquerdo do cabeçalho da coluna

E no lado esquerdo do cabeçalho da coluna, você tem o botão de filtro que pode usar para filtrar os valores da coluna. Observação: Ao filtrar os valores de uma coluna, a consulta avançada a considera como uma única etapa e a lista em Etapas Aplicadas.

o cabeçalho no lado esquerdo da coluna

Se você clicar com o botão direito no cabeçalho da coluna poderá ver que existe um menu que inclui uma lista de opções que você pode usar para transformar os dados e usar uma das opções e PQ que ele armazena como uma etapa do processo aplicado. não.

clique com o botão direito no cabeçalho da coluna

Fontes de dados para Power Query

A melhor parte do Power Query é que você pode obter dados de várias fontes, transformá-los e carregá-los na planilha. Ao clicar em Obter dados em GET & TRANSFORM, você pode ver a lista completa de fontes de dados que podem ser carregadas no editor.

a opção de obter dados de múltiplas fontes

Agora vamos dar uma olhada em algumas das fontes de dados:

  • Da tabela/intervalo : com esta opção, você pode carregar dados no Advanced Query Editor diretamente da planilha ativa.
  • Da pasta de trabalho : de outra pasta de trabalho que você possui no seu computador. Você só precisa localizar este arquivo usando uma caixa de diálogo aberta e ele obterá automaticamente os dados deste arquivo.
  • De texto/CSV : obtenha dados de um arquivo de texto ou arquivo separado por vírgula e, em seguida, você pode carregá-los na planilha.
  • Da pasta : pega todos os arquivos da pasta e suporta dados em um poderoso editor de consultas. (Veja isto: Combine arquivos Excel de uma pasta).
  • Da Web : Com esta opção você obtém dados de um endereço da web, imagine que você tem um arquivo armazenado na web ou uma página da web da qual precisa obter os dados.

Como carregar dados no Power Query Editor

Agora vamos aprender como carregar dados no poderoso editor de consultas. Aqui você tem uma lista de nomes de alunos e suas pontuações ( LINK ).

aprenda como carregar dados no poderoso editor de consultas

Você carregará os dados diretamente da planilha, então primeiro você precisa abrir o arquivo e depois seguir as etapas abaixo:

  • Primeiro, aplique uma tabela do Excel aos dados (mesmo que não o faça, o Excel fará isso antes de carregar os dados no editor PQ).
  • Agora, selecione uma célula da tabela e clique em “Da tabela/intervalo” (guia Obter e transformar dados).
intervalo de clique na tabela
  • Depois de clicar no botão, o Excel confirma o intervalo de dados para aplicar uma tabela do Excel a ele.
o intervalo de dados para aplicar uma tabela do Excel
  • Neste ponto, você tem os dados no Power Query Editor e eles se parecem com o mostrado abaixo.
editor de consulta poderoso de dados
  • Aqui você pode ver:
    • Na barra de fórmulas, PQ gerou o código M para a tabela que você acabou de carregar no editor.
    • No lado esquerdo do editor você tem o painel de consultas onde está a lista de consultas.
    • No lado direito, nas configurações da consulta, você tem a seção chamada “Etapas Aplicadas” onde você tem todas as etapas listadas. Nota: Você deve pensar que não fez nenhum “Tipo Modificado”, mas existe uma etapa chamada “Tipo Modificado” aí. Deixe-me contar a INTELIGÊNCIA do POWER QUERY quando você carrega dados no editor, ele verifica e aplica automaticamente os tipos de dados corretos para todas as colunas.
barra de fórmula-pq

Exemplos de consulta avançada (dicas e truques)

Você pode aprender como realizar algumas das tarefas básicas que normalmente realiza com fórmulas funcionais no Excel, mas com o PowerQuery você pode fazer isso com apenas alguns cliques:

Você tem uma lista de valores e deseja substituir um ou mais valores por outra coisa. Bem, com a ajuda do Power Query , você pode criar uma consulta e substituir esses valores em um piscar de olhos.

Na lista abaixo, você precisa substituir meu nome “Puneet” por “Punit”.

lista de valores
  • Primeiro, edite a lista no Advanced Query Editor.
  • Depois disso, no Power Query Editor, vá até “Guia Transformar” e clique em “Substituir Valores”.
guia transformar
  • Agora em “Valor a encontrar” digite “Puneet” e em “Substituir por” digite “Punit” e depois clique em OK.
valor para encontrar
  • Depois de clicar em OK, todos os valores serão substituídos pelos novos valores e agora clique em “Fechar e Carregar” para carregar os dados na planilha.
fechar e carregar

Assim como na classificação normal, você pode classificar os dados usando o PowerQuery e estou usando a mesma lista de nomes que você usou no exemplo acima.

  • Primeiro, carregue os dados no poderoso editor de consultas.
  • Na guia Início, você tem dois botões de classificação (crescente e decrescente).
  • Clique em um desses botões para classificar.
dois botões de classificação

Digamos que você tenha alguns dados em algum lugar e precise remover algumas colunas deles. A questão é que você precisa excluir essas colunas toda vez que adicionar novos dados, certo? Mas a consulta avançada pode cuidar disso.

  • Selecione a coluna ou várias colunas que deseja excluir.
abrir dados em um poderoso editor de consultas
  • Agora clique com o botão direito e selecione “Excluir”.
retirar

Dica rápida: Também existe uma opção “Remover outras colunas”, onde você pode excluir todas as colunas não selecionadas.

Assim como a opção de texto para coluna, você tem “Dividir coluna” na consulta avançada. Deixe-me contar como funciona.

  • Selecione a coluna e vá para a guia Home ➜ Transformar ➜ Dividir coluna ➜ Por delimitador.
coluna dividida
  • Selecione o personalizado na lista suspensa e digite “-” nele.
  • Agora, aqui você tem três opções diferentes para dividir uma coluna.
    • Delimitador mais à esquerda
    • Delimitador mais à direita
    • Cada ocorrência do delimitador
três opções diferentes para dividir uma coluna

Se você tiver apenas um delimitador em uma célula, todos os três funcionarão da mesma forma, mas se você tiver mais de um delimitador, deverá escolher de acordo.

delimitar em uma célula

Você pode simplesmente renomear uma coluna clicando com o botão direito e clicando em “Renomear”.

renomear uma coluna

Dica rápida : digamos que você tenha uma consulta para renomear uma coluna e alguém a renomeie por engano. Você pode restaurar esse nome com um único clique.

No Power Query, existe uma opção simples para criar uma coluna duplicada. Basta clicar com o botão direito na coluna para a qual deseja criar uma coluna duplicada e clicar em “Coluna Duplicada”.

criar uma coluna duplicada

Na consulta poderosa, a transposição é brincadeira de criança. Sim, apenas um clique.

  • Depois de carregar os dados no Advanced Query Editor, tudo o que você precisa fazer é selecionar a(s) coluna(s) ou linha(s).
  • Vá para a guia Transformar ➜ Tabela ➜ Transpor.
transpor coluna ou linha

Normalmente, para substituir ou remover erros no Excel, você pode usar a opção Localizar e Substituir ou o código VBA. Mas no PowerQuery é muito mais fácil. Veja a coluna abaixo onde há erros e você pode removê-los e substituí-los.

erros de substituição ou remoção

Ao clicar com o botão direito na coluna, você terá as duas opções.

  • Substituir erros
  • Remover erros
substituir erros-remover erros

Você tem dados em uma coluna, mas eles não estão no formato correto. Então, toda vez que você precisar alterar seu formato.

alterar tipo de dados
  • Primeiro, edite os dados no poderoso editor de consultas.
  • Depois disso, selecione a coluna e vá para a aba Transformar.
  • Agora, em Tipo de dados, selecione “Data” como tipo.

Na consulta avançada, existe a opção de adicionar uma coluna de exemplo que na verdade não é um exemplo relacionado à coluna atual.

Deixe-me lhe dar um exemplo:

Imagine que você precisa de nomes de dias em uma coluna de data. Em vez de usar uma fórmula ou qualquer outra opção que você possa usar, você pode usar “Adicionar coluna de exemplos”.

Veja como fazer isso:

  • Clique com o botão direito em uma coluna e clique em “Adicionar coluna de exemplos”.
adicionar coluna de exemplos
  • Aqui você obterá uma coluna vazia. Clique na primeira célula da coluna para obter uma lista de valores que você pode inserir.
coluna vazia
  • Selecione “Nome do dia da semana a partir da data” e clique em OK.
dia da semana-data-nome

Estrondo! sua nova coluna está aqui.

nova coluna

Você tem as seguintes opções para alterar maiúsculas e minúsculas no PowerQuery.

  • Pequeno
  • Maiúsculas
  • Coloque cada palavra em maiúscula

Você pode fazer isso clicando com o botão direito em uma coluna e selecionando uma das três opções acima. Ou vá para a guia Transformar ➜ Coluna de Texto ➜ Formatar.

caso de mudança

Para limpar dados ou remover espaços indesejados , você pode usar as opções TRIM e CLEAN no PowerQuery. Os passos são simples:

  • Clique com o botão direito em uma coluna ou selecione todas as colunas se você tiver várias colunas.
  • Vá para a guia Transformar ➜ Coluna de Texto ➜ Formato.
    1. TRIM: para remover espaços em branco do final e do início de uma célula.
    2. LIMPAR: para remover caracteres não imprimíveis de uma célula.
aparar e limpar

Então você tem uma lista de valores e dessa lista deseja adicionar um prefixo/sufixo em cada célula. No Excel você pode usar o método de concatenação, mas no PowerQuery existe uma opção simples de usar para ambos.

  • Primeiro, selecione a coluna onde você precisa adicionar um prefixo/sufixo.
  • Em seguida, vá para a guia Transformar ➜ Coluna de Texto ➜ Formato ➜ Adicionar Prefixo/Adicionar Sufixo.
cortar e limpar-1
  • Depois de clicar em qualquer uma das opções, você receberá uma caixa de diálogo para inserir texto.
caixa de diálogo para inserir texto
  • E após inserir o texto, clique em OK.
digite-texto-clique-ok

Se você é um geek de fórmulas, tenho certeza de que concorda comigo que extrair texto ou número de uma célula requer a combinação de diferentes funções. Mas o PowerQuery resolveu muitos desses problemas. Você tem sete maneiras de extrair valores de uma célula.

extrair valores

Muitas vezes acontece que você tem a data e a hora, ambas em uma célula, mas precisa de uma delas.

apenas data ou hora
  • Selecione a coluna onde você tem a data e hora combinadas.
  • Se você quiser:
    • Data : Clique com o botão direito ➜ Transformar ➜ Somente data.
    • Hora : Clique com o botão direito ➜ Transformar ➜ Somente hora.
se você quiser namorar
se você quiser tempo

Agora você já sabe separar data e hora. Mas a seguir, você precisa saber como combiná-los.

combinar data e hora
  • Primeiro, selecione a coluna de data e clique na opção “Somente data”.
  • Depois disso, selecione as duas colunas (Data e Hora) e vá até a aba Transformar e no grupo “Coluna de Data e Hora”, vá até Data e clique em “Combinar Data e Hora”.
combinar data e hora

Aqui estão as seguintes opções para arredondar números.

  • Arredondar para baixo: Para arredondar um número para baixo.
  • Arredondar: Para arredondar um número.
  • Arredondamento: Você pode escolher até onde pode arredondar minhas casas decimais.
números arredondados

Aqui estão as etapas:

  • Selecione a coluna e clique com o botão direito ➜ Transform ➜ Round .
    1. Arredondar para baixo: Para arredondar um número para baixo.
    2. Arredondar: Para arredondar um número.
    3. Arredondamento: Você pode escolher até onde pode arredondar minhas casas decimais.

Observação: Ao selecionar a opção “#3 Rodada”, você deve inserir o número de casas decimais a serem arredondadas.

Existem opções que você pode usar para realizar cálculos (muitas delas). Você pode encontrar todas essas opções na guia Transformar (no grupo Coluna Numérica).

  • Básico
  • Estatisticas
  • Cientista
  • Trigonometria
  • Redondo
  • Informação
cálculos

Para realizar qualquer um desses cálculos, você deve selecionar a coluna e depois a opção.

Digamos que você tenha um grande conjunto de dados e queira criar uma tabela de resumo. Aqui está o que você precisa fazer:

por grupo
  • Na guia Transformar, clique no botão ‘Agrupar por’ e você verá uma caixa de diálogo.
agrupar por botão
  • Agora nesta caixa de diálogo selecione a coluna com a qual deseja agrupar e depois adicione um nome, selecione a operação e a coluna onde você possui valores.
coluna de seleção de diálogo
  • No final, clique em OK.
no final clique em ok

Nota: Também existem opções avançadas na opção “Agrupar por” que você pode usar para criar uma tabela de grupo multinível.

Em uma das postagens do meu blog, listei sete métodos para remover sinais negativos e a consulta avançada é um deles. Clique com o botão direito em uma coluna e vá até a opção Transformar, a seguir clique em “Valor Absoluto”.

remover valores negativos

Isso remove instantaneamente todos os sinais negativos dos valores.

Como carregar dados na planilha

Depois de transformar seus dados, você pode carregá-los na planilha e usá-los para análises posteriores. Na aba inicial há um botão chamado “Fechar e Carregar”. Ao clicar nele você obtém uma lista suspensa que oferece opções adicionais:

  • Fechar e carregar
  • Fechar e carregar
fechar e carregar
  • Depois de clicar no botão, ele exibirá as seguintes opções:
opções de importação de dados
  • Selecione como você deseja exibir esses dados em sua planilha.
    • Pintura
    • Relatório de tabela dinâmica:
    • Pivô da tabela
    • Crie apenas uma conexão
    • Onde você deseja colocar os dados?
      • Planilha Existente
      • Nova planilha.
    • Adicione esses dados ao modelo de dados.
  • Basta selecionar a opção de tabela e a nova planilha e deixar o modelo de dados desmarcado e clicar em OK.
opções de importação de dados-2
  • No momento em que você clica em OK, uma nova planilha é adicionada com os dados.
nova planilha com dados

Mais exemplos para aprender

Atualizar automaticamente uma consulta

De todos os exemplos que citei aqui, este é o mais importante. Ao criar uma consulta, você pode atualizá-la automaticamente (você pode definir um cronômetro).

E aqui estão as etapas:

  • Na guia Dados, clique em “Consultas e Conexões” e você obterá o painel Consultas e Conexão no lado direito da janela.
solicitações e conexões
  • Agora clique com o botão direito na consulta e marque “Atualizar a cada” e insira os minutos.
clique com o botão direito na marca de seleção da consulta

Como usar uma fórmula e função no Power Query

Assim como você pode usar funções e fórmulas em uma planilha do Excel, a Consulta Avançada tem sua própria lista de funções que você pode usar. Os princípios básicos de funções e fórmulas no PowerQuery são iguais às funções de planilha do Excel.

No PQ você precisa adicionar uma nova coluna personalizada para adicionar uma função ou fórmula.

Vejamos um exemplo: Nos dados abaixo (já no editor PQ) você tem o nome e o sobrenome ( LINK PARA DOWNLOAD ) .

dados-já-no-pq-editor-1

Imagine que você precisa mesclar os dois nomes e criar uma coluna para o nome completo. Neste caso, você pode inserir uma fórmula simples para concatenar os nomes das duas colunas.

  • Primeiro, vá para a guia Adicionar Coluna e clique em “Coluna Personalizada”.
adicionar guia de coluna
  • Agora, na caixa de diálogo da coluna personalizada, insira o nome da nova coluna “Nome completo” ou o nome que desejar para a nova coluna.
coluna personalizada
    • A fórmula da coluna personalizada é onde você precisa inserir a fórmula. Então insira a fórmula abaixo :
 [First Name]&" "&[Last Name] 
fórmula de coluna personalizada
  • Ao inserir uma fórmula na “Fórmula de coluna personalizada”, o PQ verifica a fórmula inserida e exibe a mensagem “Nenhum erro de sintaxe foi detectado” e se houver algum erro exibirá uma mensagem de erro com base no tipo do erro.
  • Depois de inserir a fórmula e ela não apresentar erros, basta pressionar OK.
  • Agora você tem uma nova coluna no final dos dados que contém valores de duas colunas (nome e sobrenome).
nova coluna no final dos dados

Como usar uma função no Power Query

Da mesma forma, você também pode usar uma função ao adicionar uma coluna personalizada e o Power Query tem uma lista enorme de funções que você pode usar.

Vamos entender como usar uma função com um exemplo fácil e simples. Continuo o exemplo acima, onde adicionamos uma nova coluna combinando nome e sobrenome.

usar função ao adicionar uma coluna personalizada

Mas agora você precisa converter o texto do nome completo nesta coluna para letras maiúsculas. A função que você pode usar é Text.Upper . Como o nome sugere, ele converte texto em letras maiúsculas.

  • Primeiro, vá para a guia Adicionar coluna e clique na coluna personalizada.
clique na coluna personalizada
    • Agora, na caixa de diálogo da coluna personalizada, insira o nome da coluna e a fórmula abaixo na caixa de fórmula da coluna personalizada:
 Text.Upper([Full Name]) 
caixa de diálogo de coluna personalizada
  • E quando você clica em OK, cria uma nova coluna com todos os nomes em letras maiúsculas.
  • O próximo passo é excluir a coluna antiga e renomear a nova coluna. Então clique com o botão direito na primeira coluna e selecione excluir.
nova coluna com todos os nomes em maiúsculas
  • Ao final, renomeie a nova coluna para “Nome Completo”.

Há um total de 700 funções que você pode usar na consulta avançada ao adicionar uma nova coluna e aqui está a lista completa fornecida pela Microsoft para essas funções, confira.

Como modificar uma consulta no PQ

Se quiser fazer alterações na consulta que já está em sua pasta de trabalho, você pode simplesmente editá-la e fazer essas alterações. Na aba Dados, há um botão chamado Consultas e Conexões.

solicitações e conexões nomeadas por botão

Quando você clica neste botão, ele abre um painel no lado direito que lista todas as consultas que você tem na pasta de trabalho atual.

listar todas as consultas realizadas

Você pode clicar com o botão direito no nome da consulta e selecionar editar e você o obterá no poderoso editor de consultas para editá-lo.

clique com o botão direito no nome da consulta e selecione editar

Ao editar uma consulta, você pode ver que todas as etapas executadas anteriormente estão listadas nas “Etapas Aplicadas” que você também pode editar ou executar novas etapas.

etapas aplicadas que você também pode editar

E quando terminar as edições, você pode simplesmente clicar no botão “Fechar e carregar”.

Exportar e importar conexões

Se você tiver uma conexão usada para uma consulta e agora quiser compartilhar essa conexão com outra pessoa, poderá exportar essa conexão como um arquivo odc.

Na tabela de consulta existe um botão chamado “Exportar Conexão” e ao clicar nele permite salvar a conexão daquela consulta em seu sistema.

conexão de exportação

E se você quiser importar uma conexão que é compartilhada por outra pessoa, basta ir até a aba Dados e em Obter e Transformar clicar em Conexões Existentes .

importar-conexão-obter-transformação

Em seguida, clique no botão “Procurar mais”, onde você pode localizar o arquivo de conexão que foi compartilhado com você e importá-lo para sua pasta de trabalho.

botão de navegação para saber mais

Linguagem PowerQuery (código M)

Como mencionei anteriormente, para cada etapa realizada no PowerQuery, ele gera um código (no backend) chamado M Code . Na aba Home, existe um botão chamado ” Editor Avançado” que você pode usar para visualizar o código.

editor avançado

E quando você clica no editor avançado, ele mostra o editor de código e este código fica assim:

o editor de código

M é uma linguagem que diferencia maiúsculas de minúsculas e, como todas as outras linguagens, usa variáveis e expressões. A estrutura básica do código é semelhante a abaixo, onde o código começa com a expressão LET.

estrutura de código básico

Neste código temos duas variáveis e os valores definidos para elas. Ao final, para obter o valor, foi utilizada a expressão IN. Agora ao clicar em OK, retornará o valor atribuído à variável “Variablename” no resultado.

Nome variável

Confira este recurso para saber mais sobre a linguagem Power Query.

No final

O que é Excel PowerQuery?

O Power Query é um mecanismo de transformação de dados que você pode usar para obter dados de várias fontes, limpar e transformar esses dados e, em seguida, usá-los posteriormente na análise.

Você não pode se dar ao luxo de evitar o POWER QUERY. Se você pensa assim, muitas coisas que fazemos com funções do Excel ou códigos VBA podem ser automatizadas usando-o, e tenho certeza que este tutorial irá inspirar você a usá-lo cada vez mais.

Adicione um comentário

O seu endereço de email não será publicado. Campos obrigatórios marcados com *