Otimizar o Power Query ao expandir colunas de tabela

A simplicidade e facilidade de utilização que permitem aos utilizadores de Power BI recolher rapidamente dados e gerar relatórios interessantes e poderosos para tomar decisões empresariais inteligentes, também permite aos utilizadores gerar facilmente consultas com baixo desempenho. Isto ocorre frequentemente quando existem duas tabelas relacionadas da mesma forma que uma chave estrangeira relaciona tabelas SQL ou listas do SharePoint. (Para que conste, este problema não é específico de SQL ou SharePoint, e ocorre em muitos cenários de extração de dados backend, especialmente quando o esquema é fluido e personalizável.) Também não há nada de inerentemente errado em armazenar dados em tabelas separadas que partilham uma chave comum — na verdade, este é um princípio fundamental do design e normalização de bases de dados. Mas implica uma forma melhor de expandir a relação.

Considere o seguinte exemplo de uma lista de clientes do SharePoint.

A lista de localizações a que se refere é a seguinte.

Ao estabelecer ligação pela primeira vez à lista, a localização é exibida como um registo.

Estes dados de topo são recolhidos através de uma única chamada HTTP à API do SharePoint (ignorando a chamada de metadados), que pode ser vista em qualquer depurador web.

Quando expandes o registo, vês os campos associados à 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. Pode ver isto no campo da fórmula gerada. Infelizmente, este método gera uma chamada individual para a segunda tabela para cada linha da primeira tabela.

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

Quando as consultas atingem este gargalo, a melhor mitigação é evitar o comportamento de chamada por linha usando uma junção clássica de tabelas. Isto garante que haverá apenas uma chamada para recuperar a segunda tabela, e o restante 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, começa pela tabela original, anotando a coluna que queres expandir e certificando-te de que tens o ID do item para o poderes comparar. Normalmente, a chave estrangeira tem um nome semelhante ao nome de exibição da coluna com o Id adicionado. Neste exemplo, é o LocationId.

Segundo, carrega a tabela secundária, certificando-te de incluir o Id, que é a chave estrangeira. Clique com o botão direito no painel de Consultas para criar uma nova consulta.

Finalmente, une as duas tabelas usando os respetivos nomes das colunas que correspondam. Normalmente consegues encontrar este campo primeiro expandindo a coluna e depois procurando as colunas correspondentes na pré-visualização.

Neste exemplo, pode ver que o LocationId na lista primária corresponde ao Id na lista secundária. A interface renomeia isto para Location.Id para tornar o nome da coluna único. Agora vamos usar esta informação para fundir as tabelas.

Ao clicar com o botão direito no painel de consultas e selecionar Nova Consulta>Combinar>Merge Queries como Novas, vê uma interface amigável para o ajudar a combinar estas duas consultas.

Selecione cada tabela na lista pendente para ver uma pré-visualização da consulta.

Depois de selecionares ambas as tabelas, seleciona a coluna que as une logicamente (neste exemplo, é o LocationId da tabela principal e o Id da tabela secundária). O diálogo vai indicar quantas das linhas correspondem usando essa chave estrangeira. Provavelmente, você vai querer usar o tipo de junção por defeito (externa à esquerda) para este tipo de dados.

Selecione OK e verá uma nova consulta, que é o resultado da junção. Expandir o registo agora não implica chamadas adicionais para o backend.

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

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

Observação

As listas de utilizadores e a taxonomia do SharePoint também são acessíveis como tabelas, podendo ser unidas exatamente da forma descrita acima, desde que o utilizador tenha privilégios adequados para aceder a estas listas.