Criar uma consulta parametrização (Power Query)

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 parametrização especial que pode alterar convenientemente diretamente a partir do Excel.

1. Selecione Dados > Obter Dados > Outras Origens > Iniciar Editor do Power Query.

2. Na Editor do Power Query, selecione Base > Gerir Parâmetros > Novos Parâmetros.

3. Na caixa de diálogo Gerir Parâmetro, selecione Novo.

4. Defina o seguinte conforme necessário:

   Nome	Isto deve refletir a função do parâmetro, mas mantê-la o mais curta possível.
   Descrição	Isto pode conter quaisquer detalhes que ajudarão as pessoas a utilizar corretamente o parâmetro.
   Necessário	Efetue um dos seguintes procedimentos: Qualquer Valor Pode introduzir qualquer valor de qualquer tipo de dados na consulta parametrização. Lista de Valores    Pode limitar os valores a uma lista específica ao introduzi-los na grelha pequena. Também tem de selecionar um Valor Predefinido 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 (selecione Base > Editor Avançado), remover o modelo de código, introduzir a lista de valores no formato da lista de consultas e, em seguida, selecionar Concluído. Depois de concluir a criação do parâmetro, a consulta de lista é apresentada nos valores dos parâmetros.
   Tipo	Isto 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 de lista predefinido. Neste caso, tem de escolher uma predefinição.
   Valor Atual	Consoante o local onde utiliza o parâmetro, se estiver em branco, a consulta poderá não devolver resultados. Se Necessário estiver selecionado, o Valor Atual não pode estar vazio.

5. Para criar o parâmetro, selecione OK.

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 são alterados. Talvez um gestor de bases de dados troque ocasionalmente um servidor, uma entrega mensal de ficheiros CSV entra numa pasta diferente ou precisa de alternar facilmente entre um ambiente de desenvolvimento/teste/produção.

Passo 1: criar uma consulta parametrização

No exemplo seguinte, tem vários ficheiros CSV que importa através da operação importar pasta (Selecione Dados > Obter Dados > a Partir de Ficheiros > Da Pasta) da pasta C:\DataFilesCSV1. No entanto, por vezes, uma pasta diferente é ocasionalmente utilizada como uma localização para remover os ficheiros, C:\DataFilesCSV2. Pode utilizar um parâmetro numa consulta como um valor de substituição para a pasta diferente.

1. Selecione Base > Gerir Parâmetros > Novo Parâmetro.

2. Introduza as seguintes informações na caixa de diálogo Gerir Parâmetro :

   Nome	CSVFileDrop
   Descrição	Localização de remoção de ficheiro alternativa
   Necessário	Sim
   Tipo	Texto
   Valores Sugeridos	Qualquer valor
   Valor Atual	C:\DataFilesCSV1

3. Selecione OK.

Passo 2: adicionar o parâmetro à consulta de dados

1. Para definir o nome da pasta como um parâmetro, em Definições de Consulta, em Passos da Consulta, selecione Origem e, em seguida, selecione Editar Definições.

2. 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.

3. Selecione OK.

Passo 3: atualizar o valor do parâmetro

A localização da pasta acabou de ser alterada, pelo que agora pode simplesmente atualizar a consulta de parâmetros.

1. Selecione Dados > Ligações & Consultas > separador Consultas , clique com o botão direito do rato na consulta de parâmetros e, em seguida, selecione Editar.

2. Introduza a nova localização na caixa Valor Atual , como C:\DataFilesCSV2.

3. Selecione Base > Fechar & Carregar.

4. Para confirmar os resultados, adicione novos dados à origem de dados e, em seguida, atualize a consulta de dados com o parâmetro atualizado (Selecione Dados > Atualizar Tudo).

Por vezes, quer 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.

1. Para abrir uma consulta, localize uma que foi carregada anteriormente a partir do Editor do Power Query, selecione uma célula nos dados e, em seguida, selecione Consulta > Editar. Para obter mais informações , consulte Criar, carregar ou editar uma consulta no Excel.

2. Selecione a seta de 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. É apresentada a caixa de diálogo Filtrar Linhas .
   
   

3. Selecione o botão à esquerda da caixa Valor e, em seguida, efetue um dos seguintes procedimentos:

   • Para utilizar um parâmetro existente, selecione Parâmetro e, em seguida, selecione o parâmetro pretendido na lista que aparece à direita.

   • Para utilizar um novo parâmetro, selecione Novo Parâmetro e, em seguida, crie um parâmetro.

4. Introduza a nova data na caixa Valor Atual e, em seguida, selecione Base > Fechar & Carregar.

5. Para confirmar os resultados, adicione novos dados à origem de dados e, em seguida, atualize a consulta de dados com o parâmetro atualizado (Selecione Dados > Atualizar Tudo). Por exemplo, altere o valor do filtro para uma data diferente para ver novos resultados.

6. Introduza a nova data na caixa Valor Atual .

7. Selecione Base > Fechar & Carregar.

8. Para confirmar os resultados, adicione novos dados à origem de dados e, em seguida, atualize a consulta de dados com o parâmetro atualizado (Selecione Dados > Atualizar Tudo).

Neste exemplo, o valor no parâmetro de consulta é lido a partir de uma célula no seu livro. Não tem de alterar a consulta de parâmetros, basta atualizar o valor da célula. Por exemplo, quer filtrar uma coluna pela primeira letra, mas alterar facilmente o valor para qualquer letra de A para Z.

1. 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.
    

   MyFilter
   G

2. Selecione uma célula na tabela do Excel e, em seguida, selecione Dados > Obter Dados > De Tabela/Intervalo. É apresentada a Editor do Power Query.

3. Na caixa Nome do painel Definições da Consulta à direita, altere o nome da consulta para ser mais significativo, como FilterCellValue. 

4. Para transmitir o valor na tabela e não a própria tabela, clique com o botão direito do rato no valor em 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 um filtro no passo 10, Power Query referencia o valor da Tabela como a condição de filtro. Uma referência direta à Tabela do Excel causaria um erro.

5. Selecione Base > Fechar & Carregar > Fechar & Carregar Para. Tem agora um parâmetro de consulta com o nome "FilterCellValue" que utiliza no passo 12.

6. Na caixa de diálogo Importar Dados , selecione Apenas Criar Ligação e, em seguida, selecione OK.

7. Abra a consulta que pretende filtrar com o valor na tabela FilterCellValue, uma que foi carregada anteriormente a partir do Editor do Power Query, selecionando uma célula nos dados e, em seguida, selecionando Consulta > Editar. Para obter mais informações , consulte Criar, carregar ou editar uma consulta no Excel.

8. Selecione a seta de filtro em qualquer cabeçalho de coluna para filtrar os seus dados e, em seguida, selecione um comando de filtro, como Filtros de Texto > Começa Com. É apresentada a caixa de diálogo Filtrar Linhas . 

9. 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 na tabela FilterCellValue que introduzir no passo seguinte.

10. Selecione a seta no lado direito da barra de fórmulas para apresentar toda a fórmula. Eis um exemplo de uma condição de filtro numa fórmula:
    
    = Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))

11. Selecione o valor do filtro. Na fórmula, selecione "G".

12. Com o M Intellisense, introduza a primeira letra da tabela FilterCellValue que criou e, em seguida, selecione-a na lista apresentada.

13. 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 o conteúdo da célula na tabela original do Excel no passo 1, altere "G" para "V" e, em seguida, atualize a consulta.

Pode controlar se as consultas de parâmetros são permitidas ou não.

1. Na Editor do Power Query, selecione Opções de> de Ficheiros e Definições > Opções de Consulta > Editor do Power Query.

2. No painel esquerdo, em GLOBAL, selecione Editor do Power Query.

3. No painel à direita, em Parâmetros, selecione ou desmarquePermitir sempre a parametrização nas caixas de diálogo de transformação e origem de dados.