Poderá estar bastante familiarizado com as consultas parametrizadas com a sua utilização no SQL ou no Microsoft Query. No entanto, Power Query parâmetros têm diferenças principais:
- Os parâmetros podem ser utilizados em qualquer passo de consulta. Além de funcionarem como um filtro de dados, os parâmetros podem ser usados para especificar coisas como um caminho de arquivo ou um nome de servidor.
- Os parâmetros não pedem para a entrada. Em vez disso, pode alterar rapidamente os respetivos valores utilizando Power Query. Pode até armazenar e obter valores de células no Excel.
- Os parâmetros são guardados numa consulta parametrizada simples, mas são separados das consultas de dados em que são utilizados. Depois de criado, pode adicionar um parâmetro às consultas conforme necessário.
Observação Se você quiser a outra maneira de criar consultas parametrizadas, consulte Criar uma consulta parametrizada no Microsoft Query.
Criar um parâmetro
Pode utilizar um parâmetro para alterar automaticamente um valor numa consulta e evitar editar sempre a consulta para alterar o valor. Basta alterar o valor do parâmetro. Depois de criar um parâmetro, este é guardado numa consulta parametrizada especial que pode alterar convenientemente diretamente a partir do Excel.
Selecionar Dados>Obter Dados> OutrasOrigens>Iniciar Editor do Power Query.
Na Editor do Power Query, selecione Página Inicial>Gerir 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 Pode conter quaisquer detalhes que ajudem as pessoas a utilizar o parâmetro corretamente. Obrigatório Siga um dos seguintes procedimentos:
Qualquer Valor Pode introduzir qualquer valor de qualquer tipo de dados na consulta parametrizada.
Lista de Valores Pode limitar os valores a uma lista específica ao introduzi-los numa pequena grelha. Você também deve selecionar um Valor Padrão e um Valor Atual abaixo.
Consulta Selecione uma consulta de lista, que se assemelha a uma coluna estruturada Lista separada por vírgulas e entre chavetas.
Por exemplo, um campo Estado de problemas pode ter três valores: {"Novo", "Em curso", "Fechado"}. Tem de criar previamente a consulta de lista ao abrir o Editor Avançado (selecionar Base>Editor Avançado), remover o modelo de código, introduzir a lista de valores no formato de lista de consulta e, em seguida, selecionar Concluído.
Quando terminar de criar o parâmetro, a consulta de lista será apresentada nos valores dos parâmetros.Tipo Isso especifica o tipo de dados do parâmetro. Valores sugeridos Se pretender, adicione uma lista de valores ou especifique uma consulta para fornecer sugestões de entrada. Valor predefinido Esta opção só é apresentada se a opção Valores Sugeridos estiver definida como Lista de valores e especificar qual o item da lista que é o predefinido. Neste caso, tem de escolher uma predefinição. Valor Atual Dependendo de onde utilizar o parâmetro, se estiver em branco, a consulta poderá não devolver resultados. Se a opção Obrigatório estiver selecionada, o Valor Atual não pode estar vazio. Para criar o parâmetro, selecione OK.
Utilizar um parâmetro para alterar uma origem de dados
Eis uma forma de gerir as alterações às localizações da origem de dados e ajudar a evitar erros de atualização. Por exemplo, assumindo um esquema e uma origem de dados semelhantes, crie um parâmetro para alterar facilmente uma origem de dados e ajudar a evitar erros de atualização de dados. Por vezes, o servidor, a base de dados, a pasta, o nome do ficheiro ou a localização mudam. Talvez um gerente de banco de dados ocasionalmente troque um servidor, uma entrega mensal de arquivos CSV vá para uma pasta diferente ou você precise alternar facilmente entre um ambiente de desenvolvimento/teste/produção.
Passo 1: Criar uma consulta parametrizada
No exemplo a seguir, você tem vários arquivos CSV que importa usando a operação de pasta de importação (Select Data>Get Data>From Files>From Folder) from folder C:\DataFilesCSV1. Mas, às vezes, uma pasta diferente é ocasionalmente usada como um local para soltar os arquivos, C:\DataFilesCSV2. Pode utilizar um parâmetro numa consulta como um valor substituto para a pasta diferente.
Select Home>Manage Parameters>novo parâmetro.
Insira as seguintes informações na caixa de diálogo Gerenciar parâmetro :
Nome CSVFileDrop Descrição Local de colocação de ficheiros alternativo Obrigatório Sim Tipo Text Valores sugeridos Qualquer valor Valor Atual C:\DataFilesCSV1 Selecione OK.
Passo 2: adicionar o parâmetro à consulta de dados
- Para definir o nome da pasta como um parâmetro, nas Definições de Consulta, em Passos de Consulta, selecione Origem e, em seguida, selecione Editar Definições.
- Certifique-se de que a opção Caminho do ficheiro está definida como Parâmetro e, em seguida, selecione o parâmetro que acabou de criar na lista pendente.
- Selecione OK.
Etapa 3: Atualizar o valor do parâmetro
A localização da pasta acabou de mudar, pelo que agora pode simplesmente atualizar a consulta parametrizada.
- Selecione Ligações de Dados>& separador Consultasde Consultas>, clique com o botão direito do rato na consulta parametrizada e, em seguida, selecione Editar.
- Insira o novo local na caixa Valor atual , como C:\DataFilesCSV2.
- Selecione Início>Fechar & Carregar.
- Para confirmar os resultados, adicione novos dados à origem de dados e, em seguida, atualize a consulta de dados com o parâmetro atualizado (Selecione Atualizar>Tudo).
Utilizar um parâmetro para filtrar dados
Por vezes, poderá querer uma forma 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 que tenha sido carregada anteriormente a partir da Editor do Power Query, selecione uma célula nos dados e, em seguida, selecione Editar Consulta>. Para obter mais informações , consulte Criar, carregar ou editar uma consulta no Excel.
Selecione a seta do filtro em qualquer cabeçalho de coluna para filtrar os seus dados e, em seguida, selecione um comando de filtro, como Filtros de Data/Hora>Depois. A caixa de diálogo Linhas de Filtro é exibida.
Selecione o botão à esquerda da caixa Valor e, em seguida, efetue um dos seguintes 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 crie um parâmetro.
Introduza a nova data na caixa Valor Atual e, em seguida, selecione Início>Fechar & Carregar.
Para confirmar os resultados, adicione novos dados à origem de dados e, em seguida, atualize a consulta de dados com o parâmetro atualizado (Selecione Atualizar>Tudo). Por exemplo, altere o valor do filtro para uma data diferente para ver novos resultados.
Introduza a nova data na caixa Valor Atual .
Selecione Início>Fechar & Carregar.
Para confirmar os resultados, adicione novos dados à origem de dados e, em seguida, atualize a consulta de dados com o parâmetro atualizado (Selecione Atualizar>Tudo).
Utilizar um valor de célula para filtrar dados
Neste exemplo, o valor no parâmetro de consulta é lido a partir de uma célula no livro. Não precisa de alterar a consulta parametrizada, apenas atualiza o valor da célula. Por exemplo, poderá querer filtrar uma coluna pela primeira letra, mas alterar facilmente o valor de A a Z para qualquer letra.
Na folha de cálculo de um livro onde a consulta que pretende filtrar é carregada, crie uma tabela do Excel com duas células: um cabeçalho e um valor.
O Meu Filtro G Selecione uma célula na tabela do Excel e, em seguida, selecione Obter Dados>>a partir de Tabela/Intervalo. O Editor do Power Query é apresentado.
Na caixa Nome do painel Definições da Consulta à direita, altere o nome da consulta para que seja mais significativo, por exemplo ValorDaCélulaDeFiltro.
Para passar o valor na tabela, e não na tabela em si, clique com o botão direito do rato no valor na Pré-visualização de Dados e, em seguida, selecione Desagregar.
Repare que a fórmula mudou para= #"Changed Type"{0}[MyFilter]
Quando utiliza a Tabela do Excel como filtro no passo 10, Power Query referencia o valor da Tabela como condição de filtro. Uma referência direta à Tabela do Excel causaria um erro.Selecione Início>Fechar & Carregar>Fechar & Carregar. Agora tem um parâmetro de consulta chamado "FilterCellValue" que utiliza no passo 12.
Na caixa de diálogo Importar Dados, selecione Apenas Criar Conexão e, em seguida, selecione OK.
Abra a consulta que pretende filtrar com o valor da tabela FilterCellValue, uma tabela previamente carregada a partir do Editor do Power Query, ao selecionar uma célula nos dados e, em seguida, selecionar Editar Consulta>. Para obter mais informações , consulte Criar, carregar ou editar uma consulta no Excel.
Selecione a seta do filtro em qualquer cabeçalho de coluna para filtrar os seus dados e, em seguida, selecione um comando de filtro, como os Filtros de Texto>Começam Por. A caixa de diálogo Linhas de Filtro é exibida.
Introduza qualquer valor na caixa Valor, como "G" e, em seguida, selecione OK. Neste caso, o valor é um marcador de posição temporário para o valor da tabela FilterCellValue que introduzir no passo seguinte.
Selecione a seta no lado direito da barra de fórmulas para apresentar a fórmula inteira. Eis um exemplo de uma condição de filtro numa fórmula:
= Table.SelectRows(#"Tipo Alterado", cada Text.StartsWith([Nome], "G"))
Selecione o valor do filtro. Na fórmula, selecione "G".
Utilize M Intellisense, introduza as primeiras letras da tabela FilterCellValue que criou e, em seguida, selecione-a na lista que é apresentada.
Selecione Base>, Fechar, Fechar>& Carregar.
Resultado
A consulta utiliza agora o valor na Tabela do Excel que criou para filtrar os resultados da consulta. Para utilizar um novo valor, edite os conteúdos das células na tabela do Excel original no passo 1, altere "G" para "V" e, em seguida, atualize a consulta.
Controlar a utilização de consultas de parâmetros
Pode controlar se as consultas de parâmetros são ou não permitidas.
- No Editor do Power Query, selecione Opções de Arquivo> eOpções de Consultade Configurações>>Editor do Power Query.
- No painel à esquerda, em GLOBAL, selecione Editor do Power Query.
- No painel à direita, em Parâmetros, selecione ou desmarque Permitir sempre parametrização em caixas de diálogo de origem de dados e de transformação.