Alternativa u optimización de consultas de lista de SharePoint

[hjaf]

¡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! 😄

[Syndicate_Admin]

Soy nuevo en probar conexiones de punto compartido que son necesarias ya que tengo que utilizar una puerta de enlace empresarial.

También trabajo en una gran corporación. Así que el camino del punto de acción es muy profundo.
Parece que la única forma de conectarse al archivo correcto es utilizar filtros.

Actualizar ese conjunto de datos lleva mucho tiempo.

Así que tengo 2 preguntas. ¿Está filtrando el camino a seguir o hay otro enfoque?

¿Funcionaría esta solución propuesta para mi situación?

[Syndicate_Admin]

Hola

El problema que tengo es que nuestros conjuntos de datos tienen una columna llamada FieldValuesAsText, que parece contener registros anidados. Desafortunadamente, estos son los datos a los que necesito llegar.

Creo que este es un tema recurrente. Si bien todas las columnas están disponibles para expandirse con los conectores de SharePoint, este no parece ser el caso con el método api / json.

[Syndicate_Admin]

Me encanta este enfoque para usar la respuesta paginada para obtener fragmentos de 5000 elementos de lista rápidamente, pero me pregunto si alguien ha conseguido filtros trabajando con eso en la misma consulta?

Me doy cuenta de que puedo recuperar todos los elementos y luego filtrar en el editor de consultas, pero me encantaría obtener una salida prefiltrada desde sharepoint.

Intenté agregar $filter=Modificó ge datetime'2021-05-08T09:59:32Z' a mi cadena de URL y al principio pensé que estaba funcionando, pero luego me di cuenta de que mis resultados finales mostraban mucha duplicación (los mismos elementos aparecen muchas veces). Creo que tal vez la combinación de filtrado y paginación está causando el problema, pero no estoy seguro.

[Syndicate_Admin]

Hola @mahoneypat ... Estoy usando su enfoque para obtener los elementos que usan la API de REST y funciona muy rápido. Pero he publicado el archivo en el servicio power bi y no puedo establecer la actualización de programación porque tengo este error: "No se puede programar la actualización para este conjunto de datos porque los siguientes orígenes de datos actualmente no admiten la actualización".

¿Sabes cómo superar este problema?

gracias

[Syndicate_Admin]

Conseguí que esto funcionara.

Cambie Custom1 por lo siguiente:

Custom1 = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents(baseurl& "/items", [Headers=[Accept="application/json"],Query=[#"$skipToken"="Paged=TRUE",p_ID=[Skip],#"$top"="5000"]]))),

Esto se basó en un blog de Chris Webb - https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

[Syndicate_Admin]

Alguien en este foro dijo que buscara una solución aquí: https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

Lo hice, y después de leer una serie de publicaciones sobre el tema pude conseguir que funcionara en el servicio en línea powerbi. No te aburriré con muchos detalles, aquí hay un simple código M modificado que necesitas usar en su lugar.

Lo que esto hace es usar una dirección URL estática para el primer parámetro de la función Web.Contents() y, a continuación, usa un parámetro 2 poco conocido (aparentemente) para pasar realmente el resto de los pares de dirección URL (ruta relativa) y consulta/valor a la cadena de consulta.

Usted debe ser capaz de modificar las partes en negrita de esto y estar listo para ir. Modé esto desde el original en la entrada del blog e incluso tiene los bits comentados originales. Puedes quitarlos si quieres.

En realidad, he codificado de forma dura la mayoría de los trazados en lugar de usar basilea u otras variables. Probablemente podría haberlos usado todavía, pero quería éxito inmediato 🙂

Esto ha sido un salvavidas aboslute para mí. Ahora mi lista con elementos de lista de 120K solo tarda 15 segundos en actualizarse.

dejar
basilea = "https://TENANT.sharepoint.com/sites/SITE/_api/web/lists/GetByTitle('LIST')/",
itemcount = Json.Document(Web.Contents("https://TENANT.sharepoint.com/sites/SITE/_api/web/lists/GetByTitle('LIST')/"&"ItemCount", [Headers=[Accept="application/json"]))[value],
skiplist = List.Numbers(0, Number.RoundUp(itemcount/5000), 5000),
#"Convertido a tabla" = Table.FromList(skiplist, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Columnas renombradas" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Skip"}}),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Columnas renombradas",{{"Omitir", escriba text}}),
fieldselect = "&$top=5000", // todos los campos sin expansión
fieldselect = "&$top=5000&$select = Id,Title,Person,Date", // lista de campos deseados (sin expansión)
fieldselect = "&$top=5000&$select=Id,Title,Choice,LookupColumn/Title,LookupColumn/Project,LookupColumn/ProjectStatus,Date,Person/LastName,Person/FirstName,Person/EMail&$expand=LookupColumn,Person",
Custom1 = Table.AddColumn(#"Tipo cambiado", "Items", cada Json.Document(Web.Contents("baseurl" y "/items?$skipToken=Paged=TRUE%26p_ID=" & [Skip] & fieldselect, [Headers=[Accept="application/json"]]))),

Custom1 = Table.AddColumn(#"Tipo modificado", "Items", cada Json.Document(Web.Contents(
"https://TENANT.sharepoint.com/sites/SITIO/_api/web/lists/GetByTitle('LIST')/",
[
RelativePath="/items?$skipToken=Paged=TRUE%26p_ID=" y [Omitir] y selección de campos,
Encabezados=[Accept="application/json"]
]
)
)
),
#"Elementos expandidos" = Table.ExpandRecordColumn(Custom1, "Items", {"value"}, {"value"}),
#"Valor expandido" = Table.ExpandListColumn(#"Elementos expandidos", "valor")
en
#"Valor expandido"

[Syndicate_Admin]

@CmdrKeene ,

Seguí el proceso de descripción y lo publiqué en el servicio. Establezca la actualización. ¡No hay problemas!

Solo para descubrir que no se envían nuevos datos al informe, ni siquiera en el escritorio de PBI los datos se actualizaron.
Solo obtengo datos nuevos si creo un nuevo informe.

Lo reviso dos veces y no tengo filtros. El recuento de artículos es preciso, pero ese no es el número que obtengo en las imágenes.
He borrado el caché, pero no hizo ninguna diferencia.

¿Tiene alguna sugerencia para solucionar este problema?

¡Gracias por su ayuda!

[Syndicate_Admin]

¡Gracias @CmdrKeene por responder!
En caso de que esto ayude a alguien más.
Pude resolver el problema cambiando de top 5000 a 2000. Estoy recibiendo todos los discos nuevos sin problemas ahora.

Esta es una gran solución y no puedo agradecerles lo suficiente por publicar 👍

[Syndicate_Admin]

Dado que el recuento de artículos es preciso, ese no es el número que obtengo en las imágenes ... Creo que debe haber un problema simple, como un filtro de página o de todo el informe, o tal vez la agregación está configurada para contar en lugar de sumar (algo así como esa naturaleza).

Sin embargo, también quiero mencionar algo que consideré que podría ser: esto usa el número de identificación de los registros de SharePoint. Si alguna vez se eliminan las entradas de la lista de SharePoint, los números de identificación podrían ser el problema.

[Syndicate_Admin]

Hola CmdrKeene, en primer lugar, gracias por tu código, funcionó en Power Bi Service, sin embargo tengo una pregunta, tu código obtiene todos los valores de la Lista de SharePoint pero quiero obtener también los registros (ver mi imagen), ¿sabes cómo podría hacer eso?

[Syndicate_Admin]

Hola CmdrKeene,

¿Dónde pego este código en Power BI? ¿Lo pego encima de una consulta en blanco?

Gracias

[Syndicate_Admin]

Hola lcasey,

Sí, ese es básicamente el proceso. Cree una nueva consulta en blanco, vaya al Editor avanzado desde la barra de herramientas de vista, elimine todo y pegue mi código en su lugar.

[Syndicate_Admin]

Me alegra que hayas encontrado ese enfoque. Estaba a punto de publicar un enlace al mismo artículo.

palmadita

[mahoneypat · Accepted solution]

@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

[Syndicate_Admin]

Hola
Me enfrento a algún problema al usar este método. Mi lista de SP tiene alrededor de 50000 filas con un rango de fechas del 01/06/2022 al 15/12/2022. Sin embargo, cuando reviso la columna de fecha, solo puedo ver los datos de junio y julio, para otros meses no muestra nada. ¿Podría decirme qué podría estar haciendo mal aquí?

[Syndicate_Admin]

¿Solo está buscando en la vista previa o en algún otro filtro aplicado? ¿Puedes compartir tu consulta?

[Syndicate_Admin]

Registré tanto el Editor de consultas como la vista Datos. Es lo mismo. No se aplican filtros. Estoy usando la consulta pegada a continuación:

dejar
baseurl = "SHAREPOINT LIST LINK_api/web/lists/getbytitle('NOMBRE DE LISTA')/",
itemcount = Json.Document(Web.Contents("SHAREPOINT LIST LINK_api/web/lists/getbytitle('LIST NAME')/"&"ItemCount", [Headers=[Accept="application/json"]]))[value],
skiplist = List.Numbers(0, Number.RoundUp(itemcount/5000), 5000),
#"Convertido en tabla" = Table.FromList(skiplist, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"columnas renombradas" = Table.RenameColumns(#"Convertido en tabla",{{"Column1", "Omitir"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Skip", type text}}),
fieldselect = "&$top=2000", // todos los campos sin expansión
fieldselect = "&$top=5000&$select = Id,Título,Persona,Fecha", // lista de campos deseados (sin expansión)
fieldselect = "&$top=5000&$select=Id,Title,Choice,LookupColumn/Title,LookupColumn/Project,LookupColumn/ProjectStatus,Date,Person/LastName,Person/FirstName,Person/EMail&$expand=LookupColumn,Person",
Custom1 = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents("baseurl" & "/items?$skipToken=Paged=TRUE%26p_ID=" & [Skip] & fieldselect, [Headers=[Accept="application/json"]]))),

Custom1 = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents(
"SHAREPOINT LIST LINK_api/web/lists/getbytitle('LIST NAME')/",
[
RelativePath="/items?$skipToken=Paged=TRUE%26p_ID=" & [Skip] & fieldselect,
Headers=[Accept="application/json"]
]
)
)
),
#"Elementos expandidos" = Table.ExpandRecordColumn(Custom1, "Items", {"value"}, {"value"}),
#"Valor expandido" = Table.ExpandListColumn(#"Elementos expandidos", "valor"),
#"Eliminado otras columnas" = Table.SelectColumns(#"Valor expandido",{"value"}),
#"Valor expandido1" = Table.ExpandRecordColumn(#"Eliminado otras columnas", "valor", {"EmployeeEmailID", "WeekStart", "WeekStartNum", "DayOfWeek", "DayOfWeekNum", "EmployeeReportingToEmailID", "ActualWorkHours", "ApprovalStatus", "MasterID_Weekly", "TotalHr", "Modified", "Created", "ID"}, {"EmployeeEmailID", "WeekStart", "WeekStartNum", "DayOfWeek", "DayOfWeekNum", "EmployeeReportingToEmailID", "ActualWorkHours", "ApprovalStatus", "MasterID_Weekly", "TotalHr", "Modified", "Created ", "ID"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded value1",{{"WeekStart", type datetime}, {"DayOfWeek", type datetime}, {"Created", type datetime}, {"Modified", type datetime}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type2",{{"ID", type number}, {"WeekStart", type date}, {"WeekStartNum", type number}, {"DayOfWeek", type date}, {"DayOfWeekNum", type number}, {"ActualWorkHours", type number}, {"Modified", type date}, {"Created", type date}})
en
#"Tipo cambiado1"

[Syndicate_Admin]

en primer lugar eres un crack, mi consulta es la siguiente, estoy aplicando tu código y tengo una lista sp muy grande.... y me devuelve 150000 elementos pero son duplicados por favor, ¿podrías ayudarme?

dejar
sitename ="", // si un subsitio utiliza "Sitio/Subsitio"
listname = "BigList",
baseurl = "https:///sites/" & sitename & "/_api/web/lists/GetByTitle('" & listname & "')/",
itemcount = Json.Document(Web.Contents(baseurl&"ItemCount", [Headers=[Accept="application/json"]]))[value],
skiplist = List.Numbers(0, Number.RoundUp(itemcount/5000), 5000),
#"Convertido a tabla" = Table.FromList(skiplist, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Columnas renombradas" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Skip"}}),
#"Tipo modificado" = Table.TransformColumnTypes(#"Columnas renombradas",{{"Omitir", escriba texto}}),
fieldselect = "&$top=5000", // todos los campos sin expansión
fieldselect = "&$top=5000&$select = Id,Title,Person,Date", // enumerar los campos deseados (sin expansión)
fieldselect = "&$top=5000&$select=Id,Title,Choice,LookupColumn/Title,LookupColumn/Project,LookupColumn/ProjectStatus,Date,Person/LastName,Person/FirstName,Person/EMail&$expand=LookupColumn,Person",
Custom1 = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents(baseurl& "/items?$skipToken=Paged=TRUE%26p_ID=" & [Skip] & fieldselect, [Headers=[Accept="application/json"]]))),
#"Elementos expandidos" = Table.ExpandRecordColumn(Custom1, "Items", {"value"}, {"value"}),
#"Valor expandido" = Table.ExpandListColumn(#"Elementos expandidos", "valor")
en
#"Valor expandido"

[Syndicate_Admin]

Agregue un paso "Eliminar duplicados" al final de la consulta, utilizando el ID del elemento de lista como campo para desduplicar.

[Syndicate_Admin]

Hola @CmdrKeene sé que esta es una publicación antigua, pero estoy buscando una solución a una carga muy lenta de SPlist y me encontré con esta publicación, ¡que es realmente útil!
Seguí los pasos, pero no recibo respuesta. Recibo un mensaje "algo salió mal, el objeto no admite 'encabezados'"

Mi consulta en blanco es

= Json.Document(Web.Contents("https://pinchin.sharepoint.com/sites/Honeycomb/_api/web/lists/GetByTitle('Employee%20Proficiencies')..."application/json"]]))

El error está a continuación

-------Se ha producido un error en la consulta ''. DataSource.Error: Web.Contents no pudo obtener el contenido de 'https://pinchin.sharepoint.com/sites/Honeycomb/_api/web/lists/GetByTitle('Employee%20Proficiencies')/items?$top=2000&$select=ID,title' (400😞 solicitud incorrecta
Detalles:
DataSourceKind=Web
DataSourcePath=https://pinchin.sharepoint.com/sites/Honeycomb/_api/web/lists/GetByTitle('Employee%20Proficiencies')...
url=https://pinchin.sharepoint.com/sites/Honeycomb/_api/web/lists/GetByTitle('Employee%20Proficiencies')...

He revisado y comparado con los códigos publicados aquí y no sé cómo corregir el error.
¿Puede proporcionar alguna sugerencia?
Gracias