Visão geral da avaliação de consultas e da 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 M do Power Query

Qualquer consulta, criada pelo Power Query, escrita manualmente por você no editor avançado ou inserida usando um documento em branco, consiste em funções e sintaxe da linguagem de fórmula do Power Query M. Essa consulta é interpretada e avaliada pelo mecanismo do Power Query para gerar seus resultados. O script M serve como o conjunto de instruções necessárias para avaliar a consulta.

Dica

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, observe 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 usado seja válido.

Observação

O script M é usado 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.
• Exibir um diagrama.

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

• Origem: 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 até uma tabela específica no banco de dados.
• Removidas outras colunas: seleciona quais colunas da tabela manter.
• Linhas classificadas: classifica a tabela usando uma ou mais colunas.
• Manter as linhas superiores: filtra a tabela para manter apenas algumas linhas do início da tabela.

Esse conjunto de nomes de etapas é uma maneira amigável de exibir o script M que o Power Query criou para você. Há várias maneiras de exibir o script M completo. No Power Query, você pode selecionar Editor Avançado na guia Exibir. Você também pode selecionar Editor Avançado no grupo Consulta da guia Página Inicial. Em algumas versões do Power Query, você também pode alterar a exibição da barra de fórmulas para mostrar o script da consulta acessando a guia Exibir e, no grupo Layout, selecionando Exibição de script>Script da consulta.

A maioria dos nomes encontrados no painel Etapas Aplicadas também está sendo usada como está no script M. As etapas de uma consulta são nomeadas usando algo chamado identificador na linguagem M. Às vezes, caracteres extras são encapsulados 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 um identificador, incluindo palavras-chave, espaço em branco, comentários, operadores e pontuadores. Para saber mais sobre identificadores na linguagem M, acesse a estrutura lexical.

Todas as alterações feitas na consulta por meio do editor do Power Query atualizam automaticamente o script M para sua consulta. Por exemplo, usando a imagem anterior como ponto de partida, se você alterar o nome da etapa Manter as linhas superiores para 20 primeiras linhas, essa alteração será atualizada automaticamente no modo de exibição de script.

Embora seja recomendável usar o editor do Power Query para criar todo ou a maior parte do script M para você, você pode adicionar ou modificar manualmente partes do script M. Para saber mais sobre o idioma M, acesse o site oficial de documentos para o idioma M.

Observação

O script M, também conhecido como código M, é um termo usado para qualquer código que use a linguagem M do Power Query. No contexto desse artigo, o script M também se refere ao código encontrado dentro de uma consulta do Power Query e acessível por meio da janela do editor avançado ou por meio da exibição 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 à fonte de dados.
3. A fonte de dados responde à solicitação do Power Query transferindo os dados solicitados para o Power Query.
4. O Power Query recebe os dados de entrada da fonte de dados e faz transformações usando o mecanismo do Power Query, se necessário.
5. Os resultados derivados do ponto anterior são carregados para um destino.

Observação

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

Quando o Power Query lê seu script M, ele executa o script por meio de um processo de otimização para avaliar sua consulta com mais eficiência. Nesse processo, ele determina quais etapas (transformações) de 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 query folding, em que o Power Query tenta enviar o máximo possível da execução para a fonte de dados, otimizando assim 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 lenta desempenha um papel importante durante o processo de otimização. Nesse processo, o Power Query entende quais transformações específicas de sua consulta precisam ser avaliadas. O Power Query também entende quais outras transformações não precisam ser avaliadas porque não são necessárias na saída da 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 Firewall de Privacidade de Dados

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 à 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 quais conjuntos de transformações precisam acontecer dentro do mecanismo do Power Query. Ele envia as instruções para dois outros componentes que cuidam da recuperação dos dados da fonte de dados e da transformação dos dados de entrada no mecanismo do Power Query, se necessário.
4. Depois que os componentes internos do Power Query recebem as instruções, o Power Query envia uma solicitação à fonte de dados usando uma consulta de fonte de dados.
5. A fonte de dados recebe a solicitação do Power Query e transfere os dados para o mecanismo do Power Query.
6. Depois que os dados estiverem no Power Query, o mecanismo de transformação do Power Query (também conhecido como mecanismo de mashup) fará as transformações que não puderam ser dobradas ou descarregadas na fonte de dados.
7. Os resultados derivados do ponto anterior são carregados para um destino.

Observação

Dependendo das transformações e da fonte de dados usadas no script M, o Power Query determina se ele transmite ou armazena em buffer os dados de entrada.

Visão geral da dobragem de consultas

O objetivo do "query folding" é transferir ou deslocar a maior parte da avaliação de uma consulta para uma fonte de dados que consiga executar as transformações da sua consulta.

O mecanismo de dobragem de consulta atinge essa meta traduzindo seu script M para um idioma que sua fonte de dados pode interpretar e executar. Em seguida, ele envia a avaliação para 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 mais rápida do que extrair todos os dados necessários da fonte de dados e executar todas as transformações necessárias no mecanismo do Power Query.

Quando você usa a experiência de obtenção de dados, o Power Query orienta você pelo processo que, em última análise, permite que você se conecte à sua fonte de dados. Ao fazer isso, o Power Query usa uma série de funções na linguagem M categorizadas como acessando funções de dados. Essas funções específicas usam mecanismos e protocolos para se conectar à fonte de dados usando uma linguagem que sua fonte de dados pode entender.

No entanto, as etapas que se seguem na consulta são as etapas ou transformações que o mecanismo de dobragem de consultas tenta otimizar. Em seguida, ele verifica se eles podem ser descarregados para sua fonte de dados em vez de serem processados usando o mecanismo do Power Query.

Importante

Todas as funções de fonte de dados, normalmente mostradas como a etapa de origem de uma consulta, consultam os dados na fonte de dados em seu idioma nativo. O mecanismo de dobragem de consultas é usado em todas as transformações aplicadas à consulta após a função da fonte de dados. Elas podem ser traduzidas e combinadas em uma única consulta de fonte de dados ou em tantas transformações quanto puderem 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 de consultas:

• Dobragem total de consultas: quando todas as transformações de consulta são enviadas de volta à fonte de dados e ocorre o mínimo de processamento no mecanismo do Power Query.
• Dobragem parcial de consultas: quando apenas algumas transformações da consulta, e não todas, podem ser enviadas de volta à fonte de dados. Nesse caso, apenas um subconjunto de suas transformações é feito na fonte de dados e o restante das transformações de consulta ocorrem no mecanismo do Power Query.
• Sem dobragem de consultas: quando a consulta contém transformações que não podem ser traduzidas para o idioma nativo da fonte de dados, seja porque as transformações não têm suporte ou porque o conector não dá suporte à dobragem de consultas. Para esse caso, o Power Query obtém os dados brutos da fonte de dados e usa o mecanismo do Power Query para obter a saída desejada processando as transformações necessárias no nível do mecanismo do Power Query.

Observação

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

O uso de uma fonte de dados que tem mais recursos de processamento e tem 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.

Conteúdo relacionado

Para obter exemplos detalhados dos três resultados possíveis do mecanismo de dobragem de consultas, confira Exemplos de dobragem de consultas.

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