Otimizar o Power Query ao expandir as colunas da tabela

a simplicidade e a facilidade de uso que permitem que Power BI usuários coletem dados rapidamente e gerem relatórios interessantes e eficientes para tomar decisões de negócios inteligentes também permitem que os usuários gerem facilmente consultas com mau desempenho. isso geralmente ocorre quando há duas tabelas relacionadas no modo como uma chave estrangeira se relaciona SQL tabelas ou listas de SharePoint. (para o registro, esse problema não é específico para SQL ou SharePoint e ocorre em muitos cenários de extração de dados de back-end, especialmente onde o esquema é fluido e personalizável.) Também não há nada de errado com o armazenamento de dados em tabelas separadas que compartilham uma chave comum, — na verdade, esse é um princípio fundamental do design e da normalização do banco de dados. Mas isso implica uma maneira melhor de expandir a relação.

considere o exemplo a seguir de uma lista de clientes SharePoint.

lista de clientes de SharePoint primários.

E a seguinte lista de locais à qual se refere.

lista de clientes do SharePoint secundário.

Quando se conecta pela primeira vez à lista, o local é exibido como um registro.

Registros de local primário.

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

Chamada HTTP única no depurador da Web.

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

Campos Unidos da tabela secundária.

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

Chamadas individuais para a segunda 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 em que SharePoint listas atingem centenas de milhares de linhas, isso pode causar uma degradação significativa da experiência.

Quando as consultas atingem esse afunilamento, a melhor mitigação é evitar o comportamento de 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 pode ocorrer na memória usando a chave comum entre as duas tabelas. A diferença de desempenho pode ser maciça em alguns casos.

Primeiro, comece com a tabela original, observando a coluna que você deseja expandir e garantindo que você tem a ID do item para que possa fazer a correspondência. Normalmente, a chave estrangeira é nomeada de forma semelhante ao nome de exibição da coluna com ID anexado. Neste exemplo, é LocationID.

Nome da chave estrangeira.

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

Carregue a tabela secundária com a chave estrangeira de ID.

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

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 isso como Location.ID para tornar o nome da coluna exclusivo. Agora, vamos usar essas informações para mesclar as tabelas.

Clicando com o botão direito do mouse no painel de consulta e selecionando nova consulta > combinar > mesclar consultas como novo, você verá uma interface do usuário amigável para ajudá-lo a combinar essas duas consultas.

Use mesclar consultas como novas para combinar as consultas.

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

Visualizar consultas mescladas.

Depois de selecionar ambas as tabelas, selecione a coluna que une as tabelas logicamente (neste exemplo, é LocationID da tabela primária e da ID da tabela secundária). A caixa de diálogo informará quantas das linhas correspondem usando essa chave estrangeira. Você provavelmente desejará usar o tipo de junção padrão (externa esquerda) para esse tipo de dados.

Tipo de junção externa esquerda de mesclagem.

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

Resultado da junção externa esquerda.

a atualização desses dados resultará em apenas duas chamadas para 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 para SharePoint.

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

Observação

SharePoint listas de usuários e taxonomia 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.