Parâmetro de células do Excel no PowerQuery

Parâmetro de células do Excel no PowerQuery

Neste artigo você aprenderá como utilizar células como parâmetros em consultas do PowerQuery no Excel.

Esta técnica é muito útil, neste exemplo nós iremos criar um relatório no Excel aonde passaremos para o filtro de dados do PowerQuery a data inicial e final e o Vendedor.

Passar células como parâmetros no PowerQuery no Excel

No nosso exemplo iremos criar um relatório no Excel com parâmetros.

Abaixo temos a nossa base de dados que será no Excel, apenas como exemplo, mas pode ser qualquer base de dados aceita pelo PowerQuery.

Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 1

A nossa base de dados possui as seguintes colunas:

  • Série NF
  • Nota Fiscal
  • Valor
  • Vencimento
  • Cliente
  • Vendedor
  • Endereço
  • Cidade
  • UF
  • e-mail
  • Username
  • Telefone
  • Ocupação
  • Empresa

Com estes dados queremos então que sejam realizados filtros de Vencimento e Vendedor.

Configurações do Relatório

Na nossa pasta de trabalho nós temos a planilha Configuração do relatório aonde temos os filtros que serão aplicados na planilha.

Para isso utilizamos uma função de matriz dinâmica, chamada ÚNICO.

Aplicamos esta função na nossa tabela de vendas na coluna de Vendedor.

Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 2

O resultado da função é uma lista com os dados únicos, sem duplicação da lista de vendedores. E caso qualquer informação seja incluída ou alterada na tabela ela é alterada automaticamente.

Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 3

Depois disto aplicamos na função o CLASSIFICAR, que realiza então a classificação dos dados da lista gerada pelo ÚNICO conforme abaixo.

Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 4

O resultado desta fórmula é uma lista de vendedores únicos classificada alfabeticamente. Esta lista será utilizada em nosso relatório.

Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 5

Agora na planilha Relatório, aplicamos a validação de dados, selecionando então o local aonde iremos colocar a seleção do vendedor.

Em seguida é selecionado em Dados->Validação de dados, selecionado Lista e clicando na primeira célula da lista de vendedores da fórmula com a lista de vendedores.

Coloque ao final da fonte #, que faz com que a lista inteira seja utilizada na validação de dados. Veja mais sobre funções de matrizes dinâmicas neste artigo: Matrizes Dinâmicas no Excel – O que muda?

Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 7

O resultado então é uma lista com os vendedores que será utilizada no relatório como parâmetro do PowerQuery do Excel.

Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 8

Esta é a aparência dos parâmetros do relatório do Excel que será utilizado no PowerQuery.

Clique na data inicial e nomeie o intervalo como dtInicial, e no campo da data final coloque dtFinal. Nomeie também o campo aonde selecionará o vendedor como Vendedor.

Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 9

Carregando dados da Tabela no PowerQuery Excel

Agora vamos carregar os dados da tabela do Excel no Powerquery para utilizamos no nosso relatório.

Selecione a tabela e clique em Dados->Obter Dados->Da Tabela/Intervalo.

 Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 11

Os dados serão carregados então para o PowerQuery. Você pode fazer qualquer alteração no relatório como desejar, alterar os tipos de dado, ligar com outras tabelas, formatar, criar colunas condicionais ou qualquer outra transformação.

Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 12

Criando os parâmetros do Excel no PowerQuery

Clique no vencimento que é o campo aonde iremos realizar o filtro de vencimentos. Selecione É posterior ou igual aÉ anterior ou igual a.

Coloque as datas de vencimento.

Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 13

Agora realize um filtro no Vendedor e selecione igual a e coloque Ana por exemplo. Este filtros ficarão dinâmicos à partir das células do Excel no PowerQuery.

Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 14

Clique em Página Inicial e Editor Avançado.

Você verá o código conforme abaixo, este é o código M que iremos deixar dinâmico.

Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 15

Para retornar os dados das células do Excel para os parâmetros de filtros do PowerQuery use o seguinte código:

DtInicial = Excel.CurrentWorkbook(){[Name=”dtInicial”]}[Content][Column1]{0},
DtFinal = Excel.CurrentWorkbook(){[Name=”dtFinal”]}[Content][Column1]{0},
Vendedor = Excel.CurrentWorkbook(){[Name=”Vendedor”]}[Content][Column1]{0},

Os textos ficarão conforme abaixo. Altere os valores de data inicial e final do filtro e coloque os nomes de DtInicial, DtFinal e Vendedor no filtro.

Veja que os dados ficarão como abaixo:

Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 17

Os filtros acima retornam os dados utilizando o código Excel.CurrentWorkbook para trazer os dados da planilha.

Então clique em Página Inicial e em Carregar Para, e em Fechar e Carregar Para, selecione Apenas criar conexão.

Uma vez retornado para o Excel clique com o botão direito e em carregar para na conexão para Tabela, logo abaixo do relatório.

Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 19

Veja que os dados serão diretamente retornados no relatório conforme o filtro de período de data inicial, final e do vendedor.

Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 20

Código para atualizar PowerQuery VBA

Clique na guia Desenvolvedor e em Gravar Macro.

Clique com botão gravar macro e clique na tabela com o botão direito Atualizar dados.

Pressione ALT+F11 para abrir o VBE.

Será gerado um código semelhante ao abaixo.

Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 22

Altere o nome do código gerado para lsAtualizar por exemplo.

Clique agora no Excel e em Inserir->Ilustrações e defina uma das imagens.

Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 21

Clique com o botão direito e no botão Atribuir macro, selecione a macro criada.

Relatório com filtros no Excel com Power Query Parâmetros Excel no Power Query 23

Pronto, o seu relatório está pronto e será atualizado toda vez que clicar no botão, puxando os dados do relatório conforme os parâmetros do Excel diretamente para o PowerQuery.

Download da planilha de cotação de ações Bovespa Excel

Para que você possa ver todas as fórmulas e entender melhor o seu uso, recomendo baixar a planilha abaixo e também assistir a vídeo-aula no topo deste artigo.

Baixe a planilha