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

Neste artigo, você terá 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

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

Dica

Pense no script M como uma receita que descreve como preparar seus dados.

O jeito mais comum de criar um script M é com o editor do Power Query. Por exemplo, quando você se conecta a uma fonte de dados, como um banco de dados SQL Server, vê no lado direito da tela a seção Etapas aplicadas, que exibe todas as etapas ou transformações usadas na consulta. Dessa maneira, o editor do Power Query serve como uma interface que ajuda a criar o script M adequado para as transformações que você deseja, além de garantir que o código usado seja válido.

Observação

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

• exibir a consulta como uma série de etapas e permitir a criação ou modificação de novas etapas;
• mostrar uma visualização de diagrama.

A imagem anterior enfatiza a seção "Etapas aplicadas", que mostra:

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

Esse conjunto de nomes de etapas é uma maneira simples de exibir o script M que Power Query criou para você. Há várias maneiras de exibir o script M completo. No Power Query, é possível selecionar Editor Avançado na guia Exibir. Outra opção é selecionar Editor Avançado no grupo Consulta na 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 de consulta. Basta acessar a guia Exibir e, no grupo Layout, selecionar Exibição de script>Script de consulta.

Grande parte dos nomes encontrados no painel Etapas aplicadas também são usados como estão no script M. Vale lembrar que as etapas de uma consulta são nomeadas de acordo com os identificadores na linguagem M. Às vezes, caracteres extras encapsulam nomes de etapas em M, mas não são mostrados nas etapas aplicadas. Um exemplo é #"Kept top rows", categorizado como um identificador entre aspas devido a esses caracteres extras. Um identificador entre aspas permite 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 Estrutura lexical.

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

Embora seja recomendável usar o editor do Power Query para criar todo o script M (ou grande parte dele), é possível adicionar ou modificar manualmente trechos do script M. Para saber mais, acesse o site oficial da documentação da linguagem 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. 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 por meio da janela do Editor Avançado ou pela exibição do script na barra de fórmulas.

Avaliação de consultas no Power Query

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

1. O script M, encontrado no Editor Avançado, é enviado para o mecanismo do Power Query. Outras informações importantes também são incluídas, como credenciais e níveis de privacidade da fonte de dados.
2. O Power Query determina quais informações precisam ser extraídas da fonte de dados e envia uma solicitação à fonte de dados.
3. A fonte de dados responde com a transferência dos dados solicitados para o Power Query.
4. O Power Query recebe os dados de entrada da fonte de dados e faz todas as transformações usando o mecanismo do Power Query, se necessário.
5. Os resultados derivados do ponto anterior são carregados em um destino.

Observação

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, 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) da consulta podem ser descarregadas para a fonte de dados. Além disso, identifica quais outras etapas precisam ser avaliadas com o mecanismo do Power Query. Esse processo de otimização é chamado de dobragem de consultas, ou seja, o Power Query tenta enviar o máximo possível da execução à fonte de dados, a fim de otimizar a execução da consulta.

Importante

Todas as regras da linguagem de fórmula M do Power Query (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 da consulta precisam ser avaliadas. O Power Query também reconhece 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: Bastidores do Firewall de Privacidade de Dados

Veja no diagrama a seguir as etapas desse processo de otimização.

1. O script M, encontrado no Editor Avançado, é enviado para o mecanismo do Power Query. Outras informações importantes também são incluídas, como credenciais e níveis de privacidade da fonte de dados.
2. O mecanismo de dobramento de consultas 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 consultas identifica 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 as instruções forem recebidas pelos componentes internos, o Power Query enviará 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 chegarem ao Power Query, o mecanismo de transformação (também conhecido como mecanismo de mashup) faz as transformações que não poderiam ser dobradas ou descarregadas para a fonte de dados.
7. Os resultados derivados do ponto anterior são carregados em um destino.

Observação

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

Visão geral da dobragem de consultas

O objetivo da dobragem de consultas é descarregar ou enviar por push a maior parte da avaliação de uma consulta para uma fonte de dados que possa calcular as transformações da consulta.

O mecanismo de dobragem de consultas atinge essa meta traduzindo o script M para uma linguagem que pode ser interpretada e executada pela fonte de dados. Em seguida, ele envia a avaliação para a fonte de dados e encaminha o resultado dessa avaliação ao Power Query.

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

Ao usar a experiência Obter dados, o Power Query orienta você pelo processo que, em última análise, permite que se conectar à sua fonte de dados. Ao fazer isso, o Power Query usa 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 à fonte de dados usando uma linguagem compatível com sua fonte de dados.

No entanto, as etapas a seguir na consulta são as etapas ou transformações que o mecanismo de dobragem de consultas tenta otimizar. Em seguida, ele verifica se elas podem ser descarregadas na fonte de dados em vez de serem processadas com o mecanismo do Power Query.

Importante

Todas as funções de fonte de dados, comumente mostradas como a etapa de Fonte de uma consulta, consultam os dados na fonte de dados em sua linguagem nativa. O mecanismo de dobragem de consultas é utilizado em todas as transformações aplicadas à consulta após a função de fonte de dados, para que elas possam ser convertidas e combinadas em uma única consulta de fonte de dados ou em transformações que podem ser descarregadas na fonte de dados.

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

• Dobragem de consultas completa: quando todas as transformações de consulta são enviadas de volta à fonte de dados e ocorre um processamento mínimo no mecanismo do Power Query.
• Dobragem de consultas parcial: quando apenas algumas transformações na consulta, e não todas, podem ser enviadas de volta à fonte de dados. Nesse caso, apenas um subconjunto das transformações é feita na fonte de dados, e o restante ocorre no mecanismo do Power Query.
• Nenhuma dobragem de consultas: quando a consulta contém transformações que não podem ser convertidas para a linguagem de consulta nativa da fonte de dados, seja porque as transformações não são compatíveis ou porque o conector não aceita a dobragem de consultas. Nesse caso, o Power Query obtém os dados brutos da fonte de dados e usa o mecanismo do Power Query para alcançar 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 consultas está disponível principalmente em conectores para fontes de dados estruturadas, como, entre outros, Microsoft SQL Server e OData Feed. Durante a fase de otimização, o mecanismo pode às vezes reordenar as etapas na consulta.

Aproveitar uma fonte de dados que tenha mais recursos de processamento e funcionalidades de dobragem de consultas agiliza os tempos de carregamento da consulta, pois o processamento ocorre na fonte de dados, e não no mecanismo do Power Query.

Próximas etapas

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

Para saber mais sobre os indicadores de dobragem de consultas encontrados no painel Etapas aplicadas, acesse Indicadores de dobragem de consultas.