Visão geral da avaliação e dobragem de consultas no Power Query

Este artigo fornece uma visão geral básica de como as consultas M são processadas e transformadas em solicitações de fonte de dados.

Script do Power Query M

Qualquer consulta, quer seja criada pelo Power Query, escrita manualmente por si no editor avançado ou introduzida utilizando um documento em branco, consiste em funções e sintaxe da linguagem de fórmulas do Power Query M. Esta consulta é interpretada e avaliada pelo motor do Power Query para produzir os seus resultados. O script M serve como o conjunto de instruções necessárias para avaliar a consulta.

Gorjeta

Você pode pensar no script M como uma receita que descreve como preparar seus dados.

A maneira mais comum de criar um script M é usando o editor do Power Query. Por exemplo, quando você se conecta a uma fonte de dados, como um banco de dados do SQL Server, você notará no lado direito da tela que há uma seção chamada etapas aplicadas. Esta seção exibe todas as etapas ou transformações usadas em sua consulta. Nesse sentido, o editor do Power Query serve como uma interface para ajudá-lo a criar o script M apropriado para as transformações que você está procurando e garante que o código que você usa seja válido.

Nota

O script M é utilizado no editor do Power Query para:

• Exiba a consulta como uma série de etapas e permita a criação ou modificação de novas etapas.
• Exiba um modo de exibição de diagrama.

A imagem anterior enfatiza a seção de etapas aplicadas, que contém as seguintes etapas:

• Fonte: Faz a conexão com a fonte de dados. Nesse caso, é uma conexão com um banco de dados do SQL Server.
• Navegação: Navega para uma tabela específica no banco de dados.
• Outras colunas removidas: seleciona quais colunas da tabela devem ser mantidas.
• Linhas classificadas: classifica a tabela usando uma ou mais colunas.
• Linhas superiores mantidas: filtra a tabela para manter apenas um determinado número de linhas da parte superior da tabela.

Este conjunto de nomes de passos é uma forma amigável de ver o script M que o Power Query criou para si. Há várias maneiras de visualizar o script M completo. No Power Query, pode selecionar Editor Avançado no separador Ver. Você também pode selecionar Editor Avançado no grupo Consulta na guia Página Inicial. Em algumas versões do Power Query, também pode alterar a vista da barra de fórmulas para mostrar o script de consulta acedendo ao separador Ver e, a partir do grupo Esquema, selecione Script de consulta da vista>de script.

A maioria dos nomes encontrados no painel Etapas aplicadas também está sendo usada, assim como no script M. As etapas de uma consulta são nomeadas usando algo chamado identificadores na linguagem M. Às vezes, caracteres extras são enrolados em torno de nomes de etapas em M, mas esses caracteres não são mostrados nas etapas aplicadas. Um exemplo é #"Kept top rows", que é categorizado como um identificador entre aspas devido a esses caracteres extras. Um identificador entre aspas pode ser usado para permitir que qualquer sequência de zero ou mais caracteres Unicode seja usada como identificador, incluindo palavras-chave, espaços em branco, comentários, operadores e pontuadores. Para saber mais sobre identificadores na linguagem M, vá para estrutura lexical.

Quaisquer alterações feitas à sua consulta através do editor do Power Query atualizarão automaticamente o script M para a sua consulta. Por exemplo, usando a imagem anterior como ponto de partida, se você alterar o nome da etapa Linhas superiores mantidas para as 20 principais linhas, essa alteração será atualizada automaticamente na visualização de script.

Embora recomendemos que utilize o editor do Power Query para criar todo ou a maior parte do script M para si, pode adicionar ou modificar manualmente partes do script M. Para saber mais sobre a língua M, visite o sítio oficial de documentos da língua M.

Nota

Script M, também referido como código M, é um termo usado para qualquer código que usa a linguagem M. No contexto deste artigo, o script M também se refere ao código encontrado dentro de uma consulta do Power Query e acessível através da janela do editor avançado ou através da vista de script na barra de fórmulas.

Avaliação de consultas no Power Query

O diagrama a seguir explora o processo que ocorre quando uma consulta é avaliada no Power Query.

1. O script M, encontrado dentro do editor avançado, é enviado para o mecanismo do Power Query. Outras informações importantes também estão incluídas, como credenciais e níveis de privacidade da fonte de dados.
2. O Power Query determina quais dados precisam ser extraídos da fonte de dados e envia uma solicitação para a fonte de dados.
3. A origem de dados responde ao pedido do Power Query transferindo os dados solicitados para o Power Query.
4. O Power Query recebe os dados recebidos da origem de dados e efetua quaisquer transformações utilizando o motor do Power Query, se necessário.
5. Os resultados derivados do ponto anterior são carregados para um destino.

Nota

Embora este exemplo mostre uma consulta com um Banco de Dados SQL como fonte de dados, o conceito se aplica a consultas com ou sem uma fonte de dados.

Quando o Power Query lê o script M, executa o script através de um processo de otimização para avaliar a sua consulta de forma mais eficiente. Nesse processo, ele determina quais etapas (transformações) da sua consulta podem ser descarregadas para sua fonte de dados. Ele também determina quais outras etapas precisam ser avaliadas usando o mecanismo do Power Query. Esse processo de otimização é chamado de dobragem de consulta, em que o Power Query tenta enviar o máximo possível de execução para a fonte de dados para otimizar a execução da consulta.

Importante

Todas as regras da linguagem de fórmula do Power Query M (também conhecida como linguagem M) são seguidas. Mais notavelmente, a avaliação preguiçosa desempenha um papel importante durante o processo de otimização. Neste processo, o Power Query compreende que transformações específicas da sua consulta precisam de ser avaliadas. O Power Query também compreende que outras transformações não precisam de ser avaliadas porque não são necessárias na saída da sua consulta.

Além disso, quando várias fontes estão envolvidas, o nível de privacidade de dados de cada fonte de dados é levado em consideração ao avaliar a consulta. Mais informações: Nos bastidores do Data Privacy Firewall

O diagrama a seguir demonstra as etapas que ocorrem nesse processo de otimização.

1. O script M, encontrado dentro do editor avançado, é enviado para o mecanismo do Power Query. Outras informações importantes também são fornecidas, como credenciais e níveis de privacidade da fonte de dados.
2. O mecanismo de dobragem de consulta envia solicitações de metadados para a fonte de dados para determinar os recursos da fonte de dados, esquemas de tabela, relações entre tabelas diferentes na fonte de dados e muito mais.
3. Com base nos metadados recebidos, o mecanismo de dobragem de consulta determina quais informações extrair da fonte de dados e qual conjunto de transformações precisa acontecer dentro do mecanismo do Power Query. Ele envia as instruções para dois outros componentes que se encarregam de recuperar os dados da fonte de dados e transformar os dados de entrada no mecanismo do Power Query, se necessário.
4. Depois que as instruções forem recebidas pelos componentes internos do Power Query, o Power Query enviará uma solicitação para a fonte de dados usando uma consulta de fonte de dados.
5. A origem de dados recebe o pedido do Power Query e transfere os dados para o motor do Power Query.
6. Quando os dados estão dentro do Power Query, o mecanismo de transformação dentro do Power Query (também conhecido como mecanismo de mashup) faz as transformações que não puderam ser dobradas ou descarregadas para a fonte de dados.
7. Os resultados derivados do ponto anterior são carregados para um destino.

Nota

Dependendo das transformações e da origem de dados utilizadas no script M, o Power Query determina se irá transmitir ou armazenar em buffer os dados recebidos.

Visão geral do dobramento de consultas

O objetivo da dobragem de consulta é descarregar ou enviar o máximo da avaliação de uma consulta para uma fonte de dados que possa calcular as transformações da sua consulta.

O mecanismo de dobragem de consulta atinge esse objetivo traduzindo o script M para uma linguagem que pode ser interpretada e executada pela fonte de dados. Em seguida, envia a avaliação por push para a sua fonte de dados e envia o resultado dessa avaliação para o Power Query.

Essa operação geralmente fornece uma execução de consulta muito mais rápida do que extrair todos os dados necessários da sua fonte de dados e executar todas as transformações necessárias no mecanismo do Power Query.

Quando utiliza a experiência obter dados, o Power Query orienta-o ao longo do processo que, em última análise, lhe permite ligar-se à sua origem de dados. Ao fazê-lo, o Power Query utiliza uma série de funções na linguagem M categorizadas como funções de acesso a dados. Essas funções específicas usam mecanismos e protocolos para se conectar à sua fonte de dados usando uma linguagem que sua fonte de dados possa entender.

No entanto, as etapas que seguem em sua consulta são as etapas ou transformações que o mecanismo de dobragem de consulta tenta otimizar. Em seguida, verifica se podem ser descarregados para a sua origem de dados em vez de serem processados utilizando o motor do Power Query.

Importante

Todas as funções da fonte de dados, geralmente mostradas como a etapa Origem de uma consulta, consultam os dados na fonte de dados em seu idioma nativo. O mecanismo de dobragem de consulta é utilizado em todas as transformações aplicadas à sua consulta após a função de fonte de dados para que possam ser traduzidas e combinadas em uma única consulta de fonte de dados ou em tantas transformações que possam ser descarregadas para a fonte de dados.

Dependendo de como a consulta está estruturada, pode haver três resultados possíveis para o mecanismo de dobragem da consulta:

• Dobragem completa da consulta: quando todas as transformações da consulta são enviadas de volta para a fonte de dados e ocorre um processamento mínimo no mecanismo do Power Query.
• Dobragem parcial da consulta: quando apenas algumas transformações em sua consulta, e não todas, podem ser enviadas de volta para a fonte de dados. Nesse caso, apenas um subconjunto das transformações é feito na fonte de dados e o restante das transformações de consulta ocorre no mecanismo do Power Query.
• Sem dobragem de consulta: quando a consulta contém transformações que não podem ser traduzidas para a linguagem de consulta nativa da fonte de dados, seja porque as transformações não são suportadas ou porque o conector não oferece suporte à dobragem de consulta. Neste caso, o Power Query obtém os dados brutos da sua origem de dados e utiliza o motor do Power Query para obter a saída pretendida, processando as transformações necessárias ao nível do motor do Power Query.

Nota

O mecanismo de dobragem de consulta está disponível principalmente em conectores para fontes de dados estruturados, como, mas não limitado a, Microsoft SQL Server e OData Feed. Durante a fase de otimização, o mecanismo às vezes pode reordenar etapas na consulta.

Aproveitar uma fonte de dados que tenha mais recursos de processamento e recursos de dobragem de consulta pode agilizar os tempos de carregamento da consulta à medida que o processamento ocorre na fonte de dados e não no mecanismo do Power Query.

Próximos passos

Para obter exemplos detalhados dos três resultados possíveis do mecanismo de dobragem de consulta, vá para Exemplos de dobragem de consulta.

Para obter informações sobre indicadores de dobragem de consulta encontrados no painel Etapas Aplicadas , vá para Indicadores de dobragem de consulta