Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hjaf
Advocate I
Advocate I

Alternativa u optimización de consultas de lista de SharePoint

¡Hola a todos!

Dado que las consultas de lista de puntos compartidos rápidamente tardan demasiado en trabajar en Power BI, he experimentado con la consulta que sharepoint genera para Excel. Esto es mucho más eficaz en Excel en comparación con obtener la misma información mediante la consulta de punto compartido en Power BI. Creo que la consulta de exportación de Excel es básicamente una consulta de la vista de lista, donde se acoplan todos los valores de búsqueda. Parece que no puedo encontrar la manera de volver a crear esta consulta en Power BI, Parece que usa un método OLE DB, pero no está seguro de cómo continuar desde allí, y algunos mensajes sugieren que powerBI no lo admite.
Conexión para Excel: "Proveedor-Microsoft.Office.List.OLEDB.2.0;Origen de datos""; NombreDeAplicación-Excel; Versión 12.0.0.0"
¿Alguien tiene experiencia lidiando con esto?

Creo que la razón de las consultas a sharepoint-resources se vuelven extremadamente lentas, se debe a todas las columnas de búsqueda y elección, para obtener el valor de la columna que tengo para expandirlas. Una alternativa a explorar estas consultas de vista es optimizar las consultas, las sugerencias son muy bienvenidas! 😄

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

@hjaf FYI que finalmente hice un video para describir este enfoque, y lo estoy agregando aquí para otros que pueden encontrar este post. También obtiene el recuento de elementos y realiza el número correcto de llamadas a la API.

Obtenga datos de lista de SharePoint con Power BI ... Rápido - YouTube

Además, un recordatorio para marcar uno de estos como la solución.

saludos

palmadita





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

40 REPLIES 40

Gracias, tu video me ayudó. Después de que me mudé a la versión 2 todo fue más rápido. MicrosoftTeams-image.png

Si cree que la versión 2 es rápida, espere hasta que pruebe el método "bueno" mediante la API de REST. Tengo una lista con más de 150.000 elementos, y se actualiza en unos 8 segundos.

Gracias por un bazillion.

mahoneypat
Employee
Employee

Los orígenes de datos de Sharepoint List pueden ser lentos. Afortunadamente, hay una manera mucho más rápida. Pruebe una consulta en blanco con esta fórmula como origen.

Json.Document(Web.Contents("https://<YourTenantName>.sharepoint.com/sites/<YourSiteName>/_api/web/lists/GetByTitle('<YourListNam...", [Headers-[Aceptar-"application/json"]]))

Sustituya todas las piezas de < >, incluido < >. Obtendrá una respuesta JSON.

Hay otra versión que realiza la paginación si la lista es >5000 elementos. Por favor, avísame si necesitas esa.

Si esta solución funciona para usted, márquela como la solución. Los felicitaciones también son apreciados. Por favor, avísame si no.

saludos

palmadita





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat ¡Impresionante!
Sí, definitivamente necesito usar 🙂 de paginación cerca de 20k en las listas 🙂

Reemplazar después de la ? con lo siguiente

?$skipToken,Paged,TRUE%26p_ID,30&$top,5000", [Encabezados,[Aceptar"Aplicación/json"]]))

Haría una lista con el número 0, 5000, 10000, 15000, 20000 o algo más dinámico para cuando la lista se hace más grande. Conviértalo en una tabla y agregue una columna personalizada que concatena el valor de lista en lugar del 30 en texto rojo anterior. A continuación, expanda la tabla para obtener todos los datos.

Las listas de sharepoint pueden ser lentas. Este enfoque ha ahorrado mucho tiempo de actualización.

Si esto funciona para usted, por favor márquelo como solución. Los felicitaciones también son apreciados. Por favor, avísame si no.

saludos

palmadita





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat eres un verdadero héroe! ¡Gracias por el video y por el comentario! Funcionó para mí como un encanto 😊

@munchkin666 Por favor, vea este artículo que escribí sobre este tema.

Actualizado: obtenga datos de lista de SharePoint ... Rápido – Hoosier BI

Palmadita

@mahoneypat eres increíble!

He intentado responder y pedir orientación sólo para tener mi publicación marcada como spam. Pero finalmente se me ocurrió una solución. ¿Estás de acuerdo con esto? Citar o corregirlo, entonces marcaré marcar su respuesta como una solución para que otras personas puedan obtener toda la imagen 🙂

La consulta en la que terminé que parece estar funcionando (valores RED reemplazados):

  • Los valores de Column1 se crean con: List.Generate(() á> 0, each _ < 120000, each _ + 5000)
  • SPItems: Json.Document(Web.Contents("https://TennantShortName.sharepoint.com/sites/NombreSitio/_api/web/lists(guid'ListGUID')/items?$skipToken,Paged,TRUE%26p_ID"&Text.From([Columna1])&"&$top 5000", [Encabezados[Aceptar?"application/json"]])))


Consulta comentada completa:

let
    Source = List.Generate(() => 0, each _ < 120000, each _ + 5000),  // Generate a list that increments 5000 up to max value 120 000
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), // Convert the list into a table
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "SPItems", each Json.Document(Web.Contents("https://<TennantShortName>.sharepoint.com/sites/<SiteName>/_api/web/lists(guid'<ListGUID>')/items?$skipToken=Paged=TRUE%26p_ID="&Text.From([Column1])&"&$top=5000", [Headers=[Accept="application/json"]]))), //custom column that does the actual query to sharepoint 
    //Note: replace <TennantShortName>, <SiteName> and <ListGUID> 
    //Instead of using list guid, you can use list names with GetByTitle(): "https://<TennantShortName>.sharepoint.com/sites/<SiteName>/_api/Web/Lists/GetByTitle('<List Title>')/items?$skipToken=Paged=TRUE%26p_ID="&Text.From([Column1])&"&$top=5000"
    #"Expanded SPItems" = Table.ExpandRecordColumn(#"Added Custom", "SPItems", {"odata.metadata", "odata.nextLink", "value"}, {"odata.metadata", "odata.nextLink", "value"}), //expand the results 
    #"Removed Duplicates1" = Table.Distinct(#"Expanded SPItems", {"odata.nextLink"}), // Remove the duplicated nextLink items to get unique items
    #"Expanded value" = Table.ExpandListColumn(#"Removed Duplicates1", "value"), // Expand the results from queries into new rows
    #"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"Id", "Title"}), // Expand wanted columns in the sharepoint list
    #"Removed Columns" = Table.RemoveColumns(#"Expanded value1",{"Column1", "odata.metadata", "odata.nextLink"}),  // Remove columns from the initial query.
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Id", Int64.Type}, {"Title", type text}}) // Type setting
in
    #"Changed Type"




Me gusta mucho este método, es súper rápido y súper fácil de configurar. Sin embargo, he descubierto que mientras usa este método, no recoge todos los campos. He intentado expandir todas las filas y columnas, seleccionando todos los valores pero si es simple no recojo el único campo que necesito. Realmente no quiero usar un conector v2, ya que es lento y terrible.

Cualquier ayuda sería apreciada.

Puedo tratar de hurgar y ver si puedo encontrar una solución. ¿Qué tipo de campo falta?

Mencionaré que los campos de persona no venían en un formato que fuera genial para mí, por lo que en mi consulta de poder también saqué la tabla de información del usuario y luego usé el ID de usuario para relacionarme con ella.

El campo en questrion se llama Nombre, que parece ser un campo de texto. Este campo es obligatorio ya que se vincula a un archivo adjunto de SharePoint, por lo que deseo usar los valores contenidos para conectarse a los datos adjuntos correspondientes a través de su dirección URL.

El campo en cuestión ESTÁ expuesto si uso un conector v2, pero no quería seguir esta ruta y preferiría usar su solución.

Eso es realmente extraño, para mí todos los campos de texto simplemente aparecen. ¿Es tal vez un campo de búsqueda en el front-end de SharePoint? ¿O está seguro de que es "una sola línea de texto" o "varias líneas de texto" en la lista en sí?

Hola CmdrKeene,

Espero que estés bien?

Gracias por investigar esto. He hablado con el desarrollador y me han informado que el nombre es de hecho una búsqueda. Aparentemente no aparece en el conjunto de datos subyacente, lo cual entiendo. Es una pena ya que el v2, aunque extremadamente lento, recoge el campo. ¿Tal vez porque está interogando el UL en lugar de los datos en sí?

¿Hay alguna manera de GetByForm o GetByURL?

No puedo pensar en una gran solución para el campo de búsqueda realmente. ¿Es posible recuperar los datos y, a continuación, utilizar una combinación o relación para realizar la búsqueda en la consulta?

No estoy seguro, pero ciertamente volveré a los desarrolladores.

Gracias por la información Cmdr, necesitas una promoción a CAPT.

Eso se ve bien para mí. Me alegro de que funcione para ti. Tengo curiosidad, ¿cuánto de una mejora de tiempo de actualización viste?

He querido escribir un blog con este enfoque en hoosierbi.com (mi blog sobre hacer Power BI pro bono con beneficios sin fines de lucro). Su pregunta exactamente sobre este tema. Terminé haciendo una versión de consulta y función de la misma que hace que sea más fácil de modificar / usar. La función podría usarse si hubiera varias listas que tuvieran las mismas columnas y una tuviera una tabla de inquilino, sitio, lista (inquilino, por supuesto, no cambiaría dentro de una empresa).

Aquí están:

Como función -

Dejar

Origen (nombre de inquilino, nombrededeadete, nombrededeadepues de) >

Dejar

sitio : nombre del sitio,

inquilino - nombre del inquilino,

lista : nombrede lista,

getdata á Json.Document(Web.Contents("https://" & tenant & ".sharepoint.com/sites/" & site & "/_api/web/lists/GetByTitle('" & list & "')/items?$top-5000", [Headers-[Accept"application/json"]]))

En

Getdata

En

Fuente

Como consulta

Dejar
Fuente ?

Dejar

sitio web " NameOfMySite",

inquilino : "NameOfMyTenant",

lista de nombres "NameOfMyList",

getdata á Json.Document(Web.Contents("https://" & tenant & ".sharepoint.com/sites/" & site & "/_api/web/lists/GetByTitle('" & list & "')/items?$top-5000", [Headers-[Accept"application/json"]]))

En

Getdata
En
Fuente

Si esto funciona para usted, márquelo como solución. Los elogios también son apreciados. Por favor, avísame si no.

saludos

palmadita





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Probablemente hice muchos pasos sub-óptimos en el método estándar utilizado anteriormente, así que pasé de literalmente 3-4 horas, a menos de 3 minutos! Pero incluso simplemente obtener los datos sin procesar con el método de consulta tradicional todavía toma 1+ hora, creo que es debido a una gran cantidad de columnas de elección y búsquedas en la lista.

Sería interesante crear una función que determine el ID máximo y obtener automáticamente todos los elementos. Creo que hay algo de espacio para optimizar aún más, con respecto a las consultas superpuestas que este método produce. porque los primeros 5k elementos tienen ideos que van desde 5k a 80k significa que las primeras consultas de 16-ish básicamente devolverán los mismos datos, pero de todos modos. bajando de horas a pocos minutos es un gran salto estoy muy satisfecho con 😄 Gracias de nuevo @mahoneypat!

Actualizaciones de PS a la consulta anterior: me di cuenta de que no borraba todos los duplicados, así que agregué una eliminación de duplicación adicional en los identificadores. También pongo tennantId, sitename y list en parámetros que hicieron que sea un poco más fácil de configurar:)

Ok, ¡esto es prometedor!

Conseguí con éxito el primer artículo 5000 en una brisa, sin embargo, la forma en que sugiere iterar / paginar la consulta es algo poco claro para mí.
En mi caso, el ID más bajo comienza en 5495, el artículo 5000 tiene un ID alrededor de 80k. La "densidad" del intervalo de identificadores varía debido a las creaciones y eliminaciones a lo largo del tiempo, y ese punto compartido no reutiliza inmediatamente los identificadores. También noté que sharepoint proporciona un valor odata.nextLink para los siguientes elementos 5k, tal vez de alguna manera puedo crear una iteración que continúa hasta que esta propiedad no aparece. En este momento esto parece suceder en alrededor de 120 000 (aunque la lista de elementos contiene sólo alrededor de 20k elementos).

Probablemente puedo usar una lista como usted sugirió y hacer que la lista se detenga en 200 000 en 5000 incrementos, pero no exactamente está seguro de cómo hacer una consulta que itera alrededor de esta lista. ¿puede dar un ejemplo?

Ps:
Tuve que añadir "/items?" en el uri para que el uri actual para web.contents() en mi caso sea el siguiente, ¿podrías usar estos uris en tu respuesta para que cuando etiquete la publicación como una solución, otras personas tengan una mejor comprensión 🙂


Json.Document(Web.Contents("https://<TennantShortName>.sharepoint.com/sites/<SiteShortName>/_api/Web/Lists/GetByTitle('<List Title>')/items? %24skiptoken-Paged%3dTRUE%26p_ID%3d<StartAtId>&%24top-5000" , [Headers-[Aceptar-"aplicación/json"]]))

o utilizando la lista guid la url se ve así:
https://<TennantShortName>.sharepoint.com/sites/<SiteShortName>/_api/web/lists(guid'<guíade la lista de elementos> ')/items?%24skiptoken-Paged%3dTRUE%26p_ID%3d<StartAtId>&%24top-5000500005000050000500000

Ok, ¡esto es prometedor!

Conseguí con éxito el primer artículo 5000 en una brisa, sin embargo, la forma en que sugiere iterar / paginar la consulta es algo poco claro para mí.
En mi caso, el ID más bajo comienza en 5495, el artículo 5000 tiene un ID alrededor de 80k. La "densidad" del intervalo de identificadores varía debido a las creaciones y eliminaciones a lo largo del tiempo, y ese punto compartido no reutiliza inmediatamente los identificadores. También noté que sharepoint proporciona un valor odata.nextLink para los siguientes elementos 5k, tal vez de alguna manera puedo crear una iteración que continúa hasta que esta propiedad no aparece. En este momento esto parece suceder en alrededor de 120 000 (aunque la lista de elementos contiene sólo alrededor de 20k elementos).

Probablemente puedo usar una lista como usted sugirió y hacer que la lista se detenga en 200 000 en 5000 incrementos, pero no exactamente está seguro de cómo hacer una consulta que itera alrededor de esta lista. ¿puede dar un ejemplo?

Ps:
Tuve que añadir "/items?" en el uri para que el uri actual para web.contents() en mi caso sea el siguiente, ¿podrías usar estos uris en tu respuesta para que cuando etiquete la publicación como una solución, otras personas tengan una mejor comprensión 🙂


Json.Document(Web.Contents("https://<TennantShortName>.sharepoint.com/sites/<SiteShortName>/_api/Web/Lists/GetByTitle('<List Title>')/items? %24skiptoken-Paged%3dTRUE%26p_ID%3d<StartAtId>&%24top-5000" , [Headers-[Aceptar-"aplicación/json"]]))

o utilizando la lista guid la url se ve así:
https://<TennantShortName>.sharepoint.com/sites/<SiteShortName>/_api/web/lists(guid'<guíade la lista de elementos> ')/items?%24skiptoken-Paged%3dTRUE%26p_ID%3d<StartAtId>&%24top-5000500005000050000500000

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors