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.
A nossa base de dados possui as seguintes colunas:
- Série NF
- Nota Fiscal
- Valor
- Vencimento
- Cliente
- Vendedor
- Endereço
- Cidade
- UF
- 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.
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.
Depois disto aplicamos na função o CLASSIFICAR, que realiza então a classificação dos dados da lista gerada pelo ÚNICO conforme abaixo.
O resultado desta fórmula é uma lista de vendedores únicos classificada alfabeticamente. Esta lista será utilizada em nosso relatório.
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?
O resultado então é uma lista com os vendedores que será utilizada no relatório como parâmetro do PowerQuery do Excel.
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.
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.
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.
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 e É anterior ou igual a.
Coloque as datas de vencimento.
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.
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.
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:
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.
Veja que os dados serão diretamente retornados no relatório conforme o filtro de período de data inicial, final e do vendedor.
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.
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.
Clique com o botão direito e no botão Atribuir macro, selecione a macro criada.
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 planilhaCurso Excel Completo – Do Básico ao VBA
Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:
