Otimizar o Power Query ao expandir as colunas da tabela

A simplicidade e a facilidade de uso que permitem aos usuários do Power BI coletar dados rapidamente e gerar relatórios interessantes e avançados para tomar decisões de negócios inteligentes, também permite que os usuários gerem facilmente consultas com baixo desempenho. Isso geralmente ocorre quando há duas tabelas que são relacionadas na forma como uma chave estrangeira associa tabelas de SQL ou listas do SharePoint. (Para registro, esse problema não é específico do SQL ou do SharePoint e ocorre em muitos cenários de extração de dados de back-end, especialmente quando o esquema é fluido e personalizável). Também não há nada inerentemente errado em armazenar dados em tabelas separadas que compartilham uma chave comum; na verdade, esse é um princípio fundamental de design e normalização de bancos de dados. Mas implica em uma forma melhor de expandir a relação.

Considere o exemplo a seguir de uma lista de clientes do SharePoint.

E a lista de locais a seguir à qual ela se refere.

Ao se conectar pela primeira vez à lista, o local aparece como um registro.

Esses dados de nível superior são coletados por meio de uma única chamada HTTP à API do SharePoint (ignorando a chamada de metadados), que você pode ver em qualquer depurador da Web.

Ao expandir o registro, você verá os campos da junção da tabela secundária.

Ao expandir linhas relacionadas de uma tabela para outra, o comportamento padrão do Power BI é gerar uma chamada para Table.ExpandTableColumn. Você pode ver isso no campo da fórmula gerada. No entanto, esse método gera uma chamada individual à segunda tabela para cada linha da primeira tabela.

Isso gera o aumento de uma chamada HTTP para cada linha da lista primária. Isso pode não parecer muito no exemplo acima de cinco ou seis linhas, mas em sistemas de produção em que as listas do SharePoint alcançam centenas de milhares de linhas, isso poderá causar uma degradação significativa da experiência.

Quando as consultas atingem esse gargalo, a melhor mitigação é evitar o comportamento de uma chamada por linha usando uma junção de tabela clássica. Isso garante que haverá apenas uma chamada para recuperar a segunda tabela e o restante da expansão poderá ocorrer na memória usando a chave comum entre as duas tabelas. A diferença de desempenho pode ser enorme em alguns casos.

Primeiro, comece com a tabela original, observando a coluna que você deseja expandir, garantindo que você tenha a ID do item para que você possa combiná-lo. Normalmente, a chave estrangeira é nomeada de maneira semelhante ao nome de exibição da coluna com Id acrescentado. Neste exemplo, ela é LocationId.

Em segundo lugar, carregue a tabela secundária, incluindo a Id, que é a chave estrangeira. Clique com o botão direito do mouse no painel Consultas para criar uma consulta.

Por fim, una as duas tabelas usando os respectivos nomes de coluna correspondentes. Normalmente, você pode encontrar esse campo expandindo primeiro a coluna e procurando as colunas correspondentes na visualização.

Neste exemplo, você pode ver que LocationId na lista primária corresponde à Id na lista secundária. A interface do usuário renomeia ela para Location.Id para tornar o nome da coluna exclusivo. Agora, vamos usar essas informações para mesclar as tabelas.

Ao clicar com o botão direito do mouse no painel de consultas e selecionar Nova Consulta>Combinar>Mesclar Consultas como Novas, você verá uma interface do usuário amigável para ajudar você a combinar essas duas consultas.

Selecione cada tabela na lista suspensa para ver uma prévia da consulta.

Depois de selecionar ambas as tabelas, selecione a coluna que une as tabelas logicamente (neste exemplo, ela é a LocationId na tabela primária e Id na tabela secundária). A caixa de diálogo mostrará quantas linhas são correspondentes ao usar essa chave estrangeira. Provavelmente você vai querer usar o tipo de junção padrão (esquerda externa) para esse tipo de dados.

Selecione OK e você verá uma nova consulta, que é o resultado da junção. Agora a expansão do registro não implica em chamadas adicionais ao back-end.

A atualização desses dados resultará em apenas duas chamadas ao SharePoint: uma para a lista primária e outra para a lista secundária. A junção será executada na memória, reduzindo significativamente o número de chamadas ao SharePoint.

Essa abordagem pode ser usada em duas tabelas quaisquer no PowerQuery que tenham uma chave estrangeira correspondente.

Observação

As listas de usuários e a taxonomia do SharePoint também estão acessíveis como tabelas e podem ser unidas exatamente da mesma forma descrita acima, desde que o usuário tenha privilégios adequados para acessar essas listas.