Optimizar Power Query al expandir columnas de tabla

La simplicidad y facilidad de uso que permite a los usuarios de Power BI recopilar datos rápidamente y generar informes interesantes y eficaces para tomar decisiones empresariales inteligentes también permite a los usuarios generar fácilmente consultas con un rendimiento deficiente. Esto suele ocurrir cuando hay dos tablas relacionadas con la forma en que una clave externa relaciona tablas SQL o listas de SharePoint. (Para el registro, este problema no es específico de SQL o SharePoint y se produce en muchos escenarios de extracción de datos back-end, especialmente cuando el esquema es fluido y personalizable). Tampoco hay nada inherentemente incorrecto al almacenar datos en tablas independientes que comparten una clave común; de hecho, se trata de una tenet fundamental del diseño y la normalización de la base de datos. Pero implica una mejor manera de expandir la relación.

Considere el ejemplo siguiente de una lista de clientes de SharePoint.

Y la siguiente lista de ubicaciones a la que hace referencia.

Cuando se conecta por primera vez a la lista, la ubicación se muestra como un registro.

Estos datos de nivel superior se recopilan a través de una sola llamada HTTP a la API de SharePoint (ignorando la llamada de metadatos), que puede ver en cualquier depurador web.

Al expandir el registro, verá los campos unidos desde la tabla secundaria.

Al expandir filas relacionadas de una tabla a otra, el comportamiento predeterminado de Power BI es generar una llamada a Table.ExpandTableColumn. Puede verlo en el campo de fórmula generado. Desafortunadamente, este método genera una llamada individual a la segunda tabla para cada fila de la primera tabla.

Esto aumenta el número de llamadas HTTP en una por cada fila de la lista principal. Esto puede no parecer mucho en el ejemplo anterior de cinco o seis filas, pero en sistemas de producción donde las listas de SharePoint alcanzan cientos de miles de filas, esto puede provocar una degradación significativa de la experiencia.

Cuando las consultas alcanzan este cuello de botella, la mejor mitigación es evitar el comportamiento de llamada por fila mediante una combinación de tabla clásica. Esto garantiza que solo habrá una llamada para recuperar la segunda tabla y el resto de la expansión puede producirse en la memoria mediante la clave común entre las dos tablas. La diferencia de rendimiento puede ser masiva en algunos casos.

En primer lugar, comience con la tabla original, tomando nota de la columna que desea expandir y asegurándose de que tiene el identificador del elemento para que pueda cotejarlo. Normalmente, la clave externa se denomina similar al nombre para mostrar de la columna con el Id. anexado. En este ejemplo, es LocationId.

En segundo lugar, cargue la tabla secundaria, asegurándose de incluir el Id., que es la clave externa. Haga clic con el botón derecho en el panel Consultas para crear una nueva consulta.

Por último, una las dos tablas con los nombres de columna correspondientes que coinciden. Normalmente, puede encontrar este campo expandiendo primero la columna y buscando las columnas coincidentes en la vista previa.

En este ejemplo, puede ver que LocationId en la lista principal coincide con el Id. en la lista secundaria. La interfaz de usuario cambia este nombre a Location.Id para que el nombre de columna sea único. Ahora vamos a usar esta información para combinar las tablas.

Al hacer clic con el botón derecho en el panel de consulta y seleccionar Nueva consulta>Combinar>Combinar consultas como nueva, verá una interfaz de usuario fácil para ayudarle a combinar estas dos consultas.

Seleccione cada tabla en la lista desplegable para ver una vista previa de la consulta.

Una vez que haya seleccionado ambas tablas, seleccione la columna que combina las tablas de forma lógica (en este ejemplo, es LocationId de la tabla principal e Id. de la tabla secundaria). El cuadro de diálogo le indicará cuántas filas coinciden con esa clave externa. Es probable que quiera usar el tipo de combinación predeterminado (exterior izquierdo) para este tipo de datos.

Seleccione Aceptar y verá una nueva consulta, que es el resultado de la combinación. La expansión del registro ahora no implica llamadas adicionales al back-end.

La actualización de estos datos dará como resultado solo dos llamadas a SharePoint: una para la lista principal y otra para la lista secundaria. La combinación se realizará en memoria, lo que reduce significativamente el número de llamadas a SharePoint.

Este enfoque se puede usar para dos tablas de PowerQuery que tengan una clave externa coincidente.

Nota:

Las listas de usuarios de SharePoint y la taxonomía también son accesibles como tablas y se pueden unir exactamente de la manera descrita anteriormente, siempre que el usuario tenga privilegios adecuados para acceder a estas listas.