Você pode estar bastante familiarizado com consultas de parâmetro com seu uso no SQL ou no Microsoft Query. No entanto, os parâmetros do Power Query têm diferenças importantes:
- Os parâmetros podem ser usados em qualquer etapa da consulta. Além de funcionar como um filtro de dados, os parâmetros podem ser usados para especificar itens como um caminho de arquivo ou um nome de servidor.
- Os parâmetros não solicitam entrada. Em vez disso, você pode alterar rapidamente seu valor usando o Power Query. Você pode até armazenar e recuperar os valores das células no Excel.
- Os parâmetros são salvos em uma consulta de parâmetro simples, mas são separados das consultas de dados em que são usados. Depois de criadas, você pode adicionar um parâmetro às consultas conforme necessário.
Observação Se você quiser a outra maneira de criar consultas de parâmetro, consulte Criar uma consulta de parâmetro no Microsoft Query.
Criar um parâmetro
Você pode usar um parâmetro para alterar automaticamente um valor em uma consulta e evitar editar a consulta toda vez para alterar o valor. Basta alterar o valor do parâmetro. Depois de criar um parâmetro, ele será salvo em uma consulta de parâmetro especial que você poderá alterar convenientemente diretamente do Excel.
Selecionar Dados>Obter Dados>Outras Fontes>Inicie o Editor do Power Query.
No Editor do Power Query, selecione Página Inicial>Gerenciar Parâmetros > Novos Parâmetros.
Na caixa de diálogo Gerenciar Parâmetro , selecione Novo.
Defina o seguinte conforme necessário:
Nome Isso deve refletir a função do parâmetro, mas mantê-lo o mais curto possível. Descrição Isso pode conter todos os detalhes que ajudarão as pessoas a usar corretamente o parâmetro. Obrigatório Siga um destes procedimentos:
Qualquer valor Você pode inserir qualquer valor de qualquer tipo de dados na consulta de parâmetro.
Lista de valores Você pode limitar os valores a uma lista específica, inserindo-os na pequena grade. Você também deve selecionar um Valor Padrão e um Valor Atual abaixo.
Configurações de Selecione uma consulta de lista, que se assemelha a uma coluna estruturada de lista separada por vírgulas e colocada entre chaves.
Por exemplo, um campo de status de problemas pode ter três valores: {"New", "Ongoing", "Closed"}. Você deve criar a consulta de lista com antecedência abrindo o Editor Avançado (selecione HomeEditor>Avançado), removendo o modelo de código, inserindo a lista de valores no formato de lista de consulta e selecionando Concluído.
Depois que você terminar de criar o parâmetro, a consulta de lista será exibida em seus valores de parâmetro.Tipo Isso especifica o tipo de dados do parâmetro. Valores Sugeridos Se desejar, adicione uma lista de valores ou especifique uma consulta para fornecer sugestões de entrada. Valor Padrão Isso só aparecerá se os Valores Sugeridos estiverem definidos como Lista de valores e especificar qual item da lista é o padrão. Nesse caso, você deve escolher um padrão. Valor Atual Dependendo de onde você usar o parâmetro, se ele estiver em branco, a consulta poderá não retornar resultados. Se Necessário estiver selecionado, o Valor Atual não pode ficar vazio. Para criar o parâmetro, selecione OK.
Usar um parâmetro para alterar uma fonte de dados
Esta é uma maneira de gerenciar alterações nos locais das fontes de dados e ajudar a evitar erros de atualização. Por exemplo, supondo um esquema e uma fonte de dados semelhantes, crie um parâmetro para alterar facilmente uma fonte de dados e ajudar a evitar erros de atualização de dados. Às vezes, o servidor, o banco de dados, a pasta, o nome do arquivo ou o local mudam. Talvez um gerenciador de banco de dados ocasionalmente troque um servidor, uma queda mensal de arquivos CSV vá para uma pasta diferente ou você precise alternar facilmente entre um ambiente de desenvolvimento/teste/produção.
Etapa 1: Criar uma consulta parâmetro
No exemplo a seguir, você tem vários arquivos CSV que importa usando a operação de importação de pasta (Select Data>Get Data>From FilesFrom>Folder) da pasta C:\DataFilesCSV1. Mas, às vezes, uma pasta diferente é usada ocasionalmente como um local para soltar os arquivos, C:\DataFilesCSV2. Você pode usar um parâmetro em uma consulta como um valor substituto para a pasta diferente.
Selecione Página Inicial>Gerenciar Parâmetros>Novo Parâmetro.
Insira as seguintes informações na caixa de diálogo Gerenciar Parâmetro :
Nome CSVFileDrop Descrição Local alternativo de descarte de arquivos Obrigatório Sim Tipo Texto Valores Sugeridos Qualquer valor Valor Atual C:\DataFilesCSV1 Selecione OK.
Etapa 2: Adicionar o parâmetro à consulta de dados
- Para definir o nome da pasta como um parâmetro, em Configurações de Consulta, em Etapas de Consulta, selecione Origem e, em seguida, selecione Editar Configurações.
- Verifique se a opção Caminho do arquivo está definida como Parâmetro e, em seguida, selecione o parâmetro que você acabou de criar na lista suspensa.
- Selecione OK.
Etapa 3: Atualizar o valor do parâmetro
O local da pasta acabou de ser alterado, então agora você pode simplesmente atualizar a consulta de parâmetro.
- Selecione Conexões de Dados>& Consultas guia Consultas>, clique com o botão direito do mouse na consulta de parâmetro e selecione Editar.
- Insira o novo local na caixa Valor Atual , como C:\DataFilesCSV2.
- Selecione Página Inicial>,Fechar & Carregar.
- Para confirmar os resultados, adicione novos dados à fonte de dados e, em seguida, atualize a consulta de dados com o parâmetro atualizado (Selecionar Dados>Atualizar Tudo).
Use um parâmetro para filtrar dados
Às vezes, você quer uma maneira fácil de alterar o filtro de uma consulta para obter resultados diferentes sem editar a consulta ou fazer cópias ligeiramente diferentes da mesma consulta. Neste exemplo, alteramos uma data para alterar convenientemente um filtro de dados.
Para abrir uma consulta, localize uma previamente carregada no Editor do Power Query, selecione uma célula nos dados e selecione Editar Consulta>. Para obter mais informações , consulte Criar, carregar ou editar uma consulta no Excel.
Selecione a seta de filtro em qualquer cabeçalho de coluna para filtrar seus dados e, em seguida, selecione um comando de filtro, como Filtros de Data/Hora>Após. A caixa de diálogo Filtrar Linhas é exibida.
Selecione o botão à esquerda da caixa Valor e siga um destes procedimentos:
- Para usar um parâmetro existente, selecione Parâmetro e, em seguida, selecione o parâmetro desejado na lista exibida à direita.
- Para usar um novo parâmetro, selecione Novo Parâmetro e, em seguida, crie um parâmetro.
Insira a nova data na caixa Valor Atual e selecione Página Inicial>Fechar & Carregar.
Para confirmar os resultados, adicione novos dados à fonte de dados e, em seguida, atualize a consulta de dados com o parâmetro atualizado (Selecionar Dados>Atualizar Tudo). Por exemplo, altere o valor do filtro para uma data diferente para ver novos resultados.
Insira a nova data na caixa Valor Atual .
Selecione Página Inicial>,Fechar & Carregar.
Para confirmar os resultados, adicione novos dados à fonte de dados e, em seguida, atualize a consulta de dados com o parâmetro atualizado (Selecionar Dados>Atualizar Tudo).
Usar um valor de célula para filtrar dados
Neste exemplo, o valor no parâmetro de consulta é lido de uma célula na pasta de trabalho. Você não precisa alterar a consulta de parâmetro, basta atualizar o valor da célula. Por exemplo, você deseja filtrar uma coluna pela primeira letra, mas alterar facilmente o valor para qualquer letra de A a Z.
Na planilha em uma pasta de trabalho em que a consulta que você deseja filtrar é carregada, crie uma tabela do Excel com duas células: um cabeçalho e um valor.
MyFilter G Selecione uma célula na tabela do Excel e selecione Dados>Obter Dados>da Tabela/Intervalo. O Editor do Power Query é exibido.
Na caixa Nome do painel Configurações de Consulta à direita, altere o nome da consulta para ser mais significativo, como FilterCellValue.
Para passar o valor na tabela, e não a tabela em si, clique com o botão direito do mouse no valor na Visualização de Dados e selecione Fazer Drill Down.
Observe que a fórmula mudou para= #"Changed Type"{0}[MyFilter]
Quando você usa a tabela do Excel como um filtro na etapa 10, o Power Query faz referência ao valor da tabela como a condição de filtro. Uma referência direta à tabela do Excel causaria um erro.Selecione Página Inicial>Fechar & Carregar Fechar>& Carregar Para. Agora você tem um parâmetro de consulta chamado "FilterCellValue" que será usado na etapa 12.
Na caixa de diálogo Importar Dados, selecione Criar Somente Conexão e, em seguida, selecione OK.
Abra a consulta que você deseja filtrar com o valor na tabela FilterCellValue, previamente carregada do Editor do Power Query, selecionando uma célula nos dados e, em seguida, selecionando Editar Consulta>. Para obter mais informações , consulte Criar, carregar ou editar uma consulta no Excel.
Selecione a seta de filtro em qualquer cabeçalho de coluna para filtrar seus dados e, em seguida, selecione um comando de filtro, como Filtros de Texto>Começa com. A caixa de diálogo Filtrar Linhas é exibida.
Insira qualquer valor na caixa Valor, como "G", e selecione OK. Nesse caso, o valor é um espaço reservado temporário para o valor na tabela FilterCellValue que você insere na próxima etapa.
Selecione a seta no lado direito da barra de fórmulas para exibir a fórmula inteira. Veja um exemplo de uma condição de filtro em uma fórmula:
= Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))
Selecione o valor do filtro. Na fórmula, selecione "G".
Usando o M IntelliSense, insira as primeiras letras da tabela FilterCellValue que você criou e selecione-a na lista exibida.
Selecionar Página Inicial>Fechar>Fechar & Carregar.
Resultado
Sua consulta agora usa o valor na tabela do Excel que você criou para filtrar os resultados da consulta. Para usar um novo valor, edite o conteúdo da célula na tabela original do Excel na etapa 1, altere "G" para "V" e atualize a consulta.
Controlar o uso de consultas de parâmetro
Você pode controlar se as consultas de parâmetro são permitidas ou não.
- No Editor do Power Query, selecione Opções de Arquivo>e Configurações>Opções de ConsultaEditor>do Power Query.
- No painel à esquerda, em GLOBAL, selecione Editor do Power Query.
- No painel à direita, em Parâmetros, marque ou desmarque Sempre permitir parametrização nas caixas de diálogo de fonte de dados e transformação.