Otimizar o Power Query ao expandir colunas de tabela

A simplicidade e facilidade de uso que permite que os usuários do Power BI reúnam dados rapidamente e gerem relatórios interessantes e poderosos para tomar decisões de negócios inteligentes também permite que os usuários gerem facilmente consultas de baixo desempenho. Isso geralmente ocorre quando há duas tabelas que estão relacionadas na maneira como uma chave estrangeira relaciona tabelas 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 do design e normalização do banco de dados. Mas implica uma maneira melhor de expandir a relação.

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

E a seguinte lista de locais a que 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 para a API do SharePoint (ignorando a chamada de metadados), que você pode ver em qualquer depurador da Web.

Quando você expande o registro, vê os campos unidos 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 de fórmula gerado. Infelizmente, esse método gera uma chamada individual para a segunda tabela para cada linha na primeira tabela.

Isso aumenta o número de chamadas HTTP em uma para cada linha na lista primária. Isso pode não parecer muito no exemplo acima de cinco ou seis linhas, mas em sistemas de produção onde as listas do SharePoint atingem centenas de milhares de linhas, isso pode causar uma degradação significativa da experiência.

Quando as consultas atingem esse gargalo, a melhor atenuação é evitar o comportamento de chamada por linha usando uma associação de tabela clássica. Isso garante que haverá apenas uma chamada para recuperar a segunda tabela, e o resto da expansão pode 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, anotando a coluna que você deseja expandir e garantindo que você tenha a ID do item para que possa correspondê-la. Normalmente, a chave estrangeira é nomeada de forma semelhante ao nome para exibição da coluna com Id acrescentado. Neste exemplo, é LocationId.

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

Finalmente, junte as duas tabelas usando os respetivos nomes de coluna que correspondem. Normalmente, você pode encontrar esse campo primeiro expandindo a coluna e, em seguida, procurando as colunas correspondentes na visualização.

Neste exemplo, você pode ver que LocationId na lista primária corresponde a Id na lista secundária. A interface do usuário renomeia isso 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 consulta e selecionar Nova consulta>combinar>consultas de mesclagem como nova, você verá uma interface do usuário amigável para ajudá-lo a combinar essas duas consultas.

Selecione cada tabela na lista suspensa para ver uma visualização da consulta.

Depois de selecionar ambas as tabelas, selecione a coluna que une as tabelas logicamente (neste exemplo, é LocationId da tabela primária e Id da tabela secundária). A caixa de diálogo instruirá quantas linhas correspondem usando essa chave estrangeira. Você provavelmente desejará usar o tipo de associação padrão (externo esquerdo) para esse tipo de dados.

Selecione OK e você verá uma nova consulta, que é o resultado da associação. Expandir o registro agora não implica chamadas adicionais para o back-end.

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

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

Nota

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