Miguel Escobar Publicada abril 4, 2019

Conectar a Archivos en SharePoint y OneDrive con Power BI / Power Query

Power BIPower Query

Durante las últimas semanas he estado tratando de unirme a varias comunidades de Facebook que giran en torno a los temas de Power BI.

Pude unirme a un par de comunidades que son completamente neutrales en el sentido de que no son administradas por una empresa con fines de lucro, sino que son miembros de la comunidad que facilitan las cosas lo que me dice que hay pocas posibilidades de conflicto de intereses con los administradores de el grupo.

Uno de esos grupos se llama “Power BI Latinoamerica” que es una comunidad que habla principalmente el idioma español y dentro de ese grupo, uno de los administradores publicó un video que me llamó la atención:

Es básicamente un video que muestra una forma de conectarse a un archivo de Excel alojado en OneDrive y aunque ese método es completamente válido, estaba tratando de hacer referencia al autor de ese video a uno de mis artículos sobre la conexión a archivos alojados en SharePoint y OneDrive y luego me di cuenta de que no había escrito formalmente sobre ese tema en mi blog … nunca.

¡Es hora de cambiar eso! Averigüemos cuál es la forma más fácil y óptima de conectarse a CUALQUIER archivo alojado en OneDrive o SharePoint.

¿Qué es SharePoint? ¿Qué es OneDrive?

image

En este post nos centraremos en solo 2 fuentes de datos:

  • SharePoint –  aPlataforma colaborativa basada en web que se integra con Microsoft Office. Puede crear sitios de grupo o subsitios donde puede compartir documentos con sus colegas. Conoce más sobre SharePoint aquí.
  • OneDrive for Business – puedes imaginar esto como tu carpeta personal dentro de una empresa. Proporciona la misma experiencia de usuario final que obtienes de SharePoint, pero con restricciones, por lo que solo es accesible para ti y, en este sentido, es privado o personal para ti. Es parte de la experiencia de Office 365, y puedes aprender más sobre OneDrive aquí.

Ambos pueden usar el mismo conector dentro de Power BI / Power Query y el mismo método de autenticación, lo que facilita las cosas, ya que el conector proporciona una experiencia realmente genial para el usuario final, así como la seguridad necesaria.

¿Cómo conectarse a SharePoint con Power BI / Power Query?

El objetivo:  Connect to a specific Excel file stored in a SharePoint Teams / Groups site

En nuestro caso, tenemos el siguiente sitio:

image

Como puedes ver, este es un sitio de grupo de Office 365 que podría transformarse o convertirse en un sitio de Teams sin ningún problema.

El siguiente método de conexión a esta carpeta se aplica a los subsitios, grupos y sitios de equipos (Teams).

Lo único que necesitamos para hacer que todo funcione es simplemente ir a la url del sitio y simplemente seleccionar la parte que tiene el formato como este:

https://<<sharepoint site root url>>/sites/<<group name, sub site name or teams site name>>/

que en mi caso es este:

image

Ahora puedo ir a Power BI y seleccionar el conector que dice «Carpeta de SharePoint»:

SNAGHTML4f833f5

y esto requerirá que ingreses una url y, como se describió anteriormente, tiene que tener el formato que mostré arriba:

image

Nota:Si deseas conectarte al directorio raíz de tu sitio de SharePoint, solo necesitas ingresar el URL de la raíz de tu sitio de Sharepoint para este conector.

y una vez que hayas pulsado Aceptar, aparecerá la ventana de autenticación en la que deberás seleccionar la cuenta de Microsoft, luego haz clic en el botón Iniciar sesión e iniciar sesión:

image

Después de presionar Conectar, el resultado será una ventana similar a la que obtienes cuando usas el conector «Desde la carpeta»:

image

y en realidad, es casi la misma experiencia donde la única diferencia es que sus archivos están alojados en SharePoint y no en una carpeta local.

Puedes hacer clic en Editar para ver los datos en la ventana del Power Query Editor y notarás que incluso los nombres de las columnas son exactamente los mismos que ves cuando te conectas a una carpeta local con Power BI / Power Query:

image

y si deseas conectarte a un solo archivo, todo lo que tienes que hacer es simplemente hacer clic en el Binario que desees y listo. En mi caso, seguí adelante y seleccioné el archivo “03-March.xlsx” y este fue el resultado después de hacer clic en ese binario y seleccionar los datos que quería de ese archivo:

image

Así es como se ve ese código:

let
Source = SharePoint.Files(«
https://powerbipanama.sharepoint.com/sites/externalsales/», [ApiVersion = 15]),
#»03-March xlsx_https://powerbipanama sharepoint com/sites/externalsales/Shared Documents/Sales Report/» = Source{[Name=»03-March.xlsx»,#»Folder Path»=»
https://powerbipanama.sharepoint.com/sites/externalsales/Shared Documents/Sales Report/»]}[Content],
#»Imported Excel» = Excel.Workbook(#»03-March xlsx_https://powerbipanama sharepoint com/sites/externalsales/Shared Documents/Sales Report/»),
#»03-March_Sheet» = #»Imported Excel»{[Item=»03-March»,Kind=»Sheet»]}[Data],
#»Promoted Headers» = Table.PromoteHeaders(#»03-March_Sheet», [PromoteAllScalars=true]),
#»Changed Type» = Table.TransformColumnTypes(#»Promoted Headers»,{{«Month», type text}, {«Name», type text}, {«Australia», type number}, {«Canada», type number}, {«Central», type number}, {«France», type number}, {«Germany», type number}, {«Northeast», type number}, {«Northwest», type number}, {«Southeast», type number}, {«Southwest», type number}, {«United Kingdom», type number}})
in
#»Changed Type»

El elemento clave aquí es la función SharePoint.Files, ¡pero incluso puedes usar otra función!

Puedes usar una función con el nombre de SharePoint.Contents que te brindarán una experiencia completamente nueva que se parece más a una forma de navegar a través de tus carpetas en caso de que tengas demasiados archivos.

Esta será le experiencia que te brinda la función SharePoint.Contents:

image

  1. Te conectas a tu sitio
  2. Navegas a la carpeta de documentos compartidos (Shared Documents)
  3. Navegas a la carpeta específica donde se almacenan tus datos
  4. Verás todos tus archivos dentro de la carpeta que querías

¿Cómo conectarse a OneDrive con Power BI / Power Query?

El objetivo:  Conectarse a un archivo alojado dentro de OneDrive for Business

En nuestro caso, tenemos el siguiente OneDrive en Office 365:

image

Similar a lo que hicimos antes, tenemos que mirar la url en nuestro navegador:

image

El formato en este caso es casi el mismo, donde tenemos la url raíz, luego «/ personal /» y por último, pero no menos importante, el nombre del usuario, que sería diferente en cada caso.

En mi caso, sería este:

https://powerbipanama-my.sharepoint.com/personal/miguel_escobar_poweredsolutionsonline_com

Y puedo usar ese valor (la url de mi sitio) dentro de SharePoint.Contents o la función SharePoint.Files para obtener los datos que necesito:

image

El código:

let
Source = SharePoint.Files(«
https://powerbipanama-my.sharepoint.com/personal/miguel_escobar_poweredsolutionsonline_com», [ApiVersion = 15]),
#»01-January csv_https://powerbipanama-my sharepoint com/personal/miguel_escobar_poweredsolutionsonline_com/Documents/Sales Data/» = Source{[Name=»01-January.csv»,#»Folder Path»=»
https://powerbipanama-my.sharepoint.com/personal/miguel_escobar_poweredsolutionsonline_com/Documents/Sales Data/»]}[Content],
#»Imported CSV» = Csv.Document(#»01-January csv_https://powerbipanama-my sharepoint com/personal/miguel_escobar_poweredsolutionsonline_com/Documents/Sales Data/»,[Delimiter=»,», Columns=12, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#»Promoted Headers» = Table.PromoteHeaders(#»Imported CSV», [PromoteAllScalars=true]),
#»Changed Type» = Table.TransformColumnTypes(#»Promoted Headers»,{{«Month», type text}, {«Name», type text}, {«Australia», type number}, {«Canada», type number}, {«Central», type number}, {«France», type number}, {«Germany», type number}, {«Northeast», type number}, {«Northwest», type number}, {«Southeast», type number}, {«Southwest», type number}, {«United Kingdom», type number}})
in
#»Changed Type»

¿Cuáles son los beneficios de estos métodos?

Hay 4 beneficios principales por los cuales usar ste método sobre cualquier otro:

  1. El conector ya es una parte nativa de Power BI / Power Query, por lo que cualquier problema que puedas encontrar está incluido en el Soporte de Microsoft.
  2. Puedes conectarte a varios archivos en lugar de solo uno al conectarte a tus sitios de esta manera.
  3. Reduces el riesgo de golpear un error Formula.Firewall, ya que todos los archivos dentro de esa carpeta se configurarán con el mismo nivel de privacidad y estarán dentro de la misma fuente de datos.
  4. El conector de SharePoint se creó de una manera en la que utiliza la API de SharePoint para obtener los datos. El conector también permite, hasta cierto punto, el plegado de consultas (query folding), de modo que cuando ejecuta su consulta solo obtiene los archivos en los que estás realmente interesado y no todos los archivos de su sitio de SharePoint. Acá puedes leer más información sobre Query Folding.

Existen otros beneficios que pueden ser específicos para ciertos casos, pero estos deberían brindarte una descripción general de las razones principales por las que debes de intentar usar este método en lugar de cualquier otro.

¿Alguna vez has usado la función SharePoint.Contents? ¿Sabías que esta misma experiencia también está disponible para carpetas locales con Folder.Contents?

Power BIPower Query
Subscribe
Notify of
guest
39 Comentario
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Yesheri

Hola,
Dentro de Sharepoint se crean los usuarios y grupos y estos a la vez se les da acceso a ciertas carpetas.
Utilizando Power Bi, es posible sacar un reporte de los sitios más visitados por cada usuario?

Joan P

Entiendo, que para acceder a usos debería ser desde el CDS (Custom Data Services) como origen de datos.
En ese origen de datos, son las tablas de sistema del 365.

Un saludo

Manel M.

Hola Miguel,
Ante todo agradecerte la oportunidad que nos das de aprender cada vez que compartes tus conocimientos en tu blog y en tu curso de Excel Moderno!!
Referente a este artículo y a OneDrive, indicas:

«El formato en este caso es casi el mismo, donde tenemos la url raíz, luego «/ personal /» y por último, pero no menos importante, el nombre del usuario, que sería diferente en cada caso.

¿Entiendo que cada usuario que tenga acceso a la carpeta compartida de OneDrive podrá acceder la carpeta con los datos desde su PC? En cada PC la ruta de acceso es diferente ya que incluye en la misma el nombre del usuario conectado en cada PC…
Gracias!!

Manel M.

Gracias por la pronta respuesta!!
Entiendo entonces que en caso de que uno de los usuarios no disponga (circunstancialmente) de conexión a internet no podrán usar el archivo?.

David Renderos

Hola Miguel, soy David de El Salvador, yo conecto una lista de Sharepoint en Power BI y lo hace perfectamente solo que cuando veo las columnas de creado y modificado power BI pone 5 horas adelante los registros, ya revise configuracion regional y todo se ve bien tanto en sharepoint como en Power BI. tienes algun caso similiar o alguna forma de como tratar esto? Saludos

ALE

EN SHAREPOINT TENGO UNA LISTA MAESTRA CON LOS NOMBRES DE LOS DOCUMENTOS Y EN OTRA CARPETA TENGO VARIOS DOCUMENTOS. ¿CÓMO PUEDO VINCULAR LOS NOMBRES DE LOS DOC. DE LA LISTA CON LOS DOCUENTOS ALMACENADOS EN ESA OTRA CARPETA?

Julian Olarte

Miguel buenos días, estoy conectando un archivo de onedrive empresarial, pero una vez doy conexión desde web, me esta generando el siguiente aviso:

No se puede Conectar

Se encontró un error al intentar conectarse

Detalles»El acceso al recurso se prohibe»

Julian Olarte

Hola, al conectar un archivo de excel que esta alojado en onedrive bussines, debo abrir el archivo desde onedrive, luego abrilo en excel de escritorio y desde info copiar la url para conectar en powerbi desde tipo de datos web, quito los ultimos caracteres para dejarlo con la extención xlsx y alli doy obtener datos, pero powerbi al leer los datos me genera ese error, sabras como se puede solucionar?

Ricardo

Hola Juan, solucionaste el problema? a mi me pasa lo mismo.

Jaime

Hola Buenas,Pudieron solucionar el problema que indica «Detalles: «El acceso al recurso se prohíbe.»»

Juan Carlos Sotero

Gracias 05/2020 y sigue funcionando

Leo Alderete (Arg)

Hola Miguel buenos días tengo una List en SharePoint y es bastante grande me gustaria tomar los registros creado solamente en el mes en curso, ya que cuando quiero actualizar demora bastante ya que trae los registros históricos.

Lisseth

Hola Buenas,Pudieron solucionar el problema que indica «Detalles: «El acceso al recurso se prohíbe.

Jacinto

¿Existe la posibilidad de leer una carpeta desde powerbi en onedrive para la empresa?
Para un archivo me funciona la conexión pero para leer una carpeta no me funciona.

Jenifer

Buenos días Miguel,

Gracias por tu explicación pero me surge una duda respecto a One Drive. Aquí explicas cómo establecer que el origen de datos sea un link a un solo archivo excel dentro de One Drive, pero, ¿es posible establecer como origen de datos el link a una carpeta (con varios archivos excel) dentro de One Drive? Estoy intentándolo y no me funciona.

Mil gracias,

Oscar

Hola Miguel, gracias por los contenidos
Pregunta: Tengo en SharePoint un txt con unos datos tabulados, el archivo pesa 2Gb… Desde Power BI no he logrado conectarlo, alguna sugerencia? Tiene alguna limitante? Gracias

Alvaro Galvis

te felicito… Me sirvió mucho tu explicación. tienes canal de Youtube? Saludos desde Bogotá Colombia.

Francisco

Hola, tengo una situación:
1. La persona A en su cuenta de OneDrive elaboró plantillas de excel, y compartió estas plantillas que colocó en una CARPETA, al usuario B
2. El usuario B, visualiza esta información en OneDrive, en la pestaña de «compartidos»..allí visualiza LA CARPETA y los archivos que le compartió A
3. El usuario B desea usar power BI, y como origen de datos usar los archivos de esa CARPETA que le compartió A
4. Ent power BI al darle conectar a ese origen (usando esa URL) no le permite conectar con esos datos

Hay alguna manera de hacerlo?
Nota: No se puede hacer que B cree su propia carpeta por política de la empresa

Pablo

hola Miguel, me conecto sin nungun problema a sharepoint 365 desde power bi desktop y se actualiza bien, pero cuando está pubicado en power bi service y le doy actualizar desde ahí me da este error:
Error de procesamientoExcel Workbook: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. The 64-bit version of the Access Database Engine 2010 Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

Instalé Acces Database engine OLEDB pero el problema persiste…que podria estar pasando?, muchas gracias

Pablo

muchas gracias por tu respuesta, el problema solo ocurre en el servicio de power bi, en desktop no hay nigun problema…ese articulo lo he leido varias veces….gracias nuevamente

Damian

Alguna idea de como conectar un parquet alojado en sharepoint al PowerBI

Hector Vera

Hola,

Cree un reporte con unos origenes de datos locales, pero tengo el problema que para que se actualice, debo actualizar las plantillas de excel y volver a publicar el reporte.
Entiendo que cambiando los origenes de datos y que el reporte los lea directamente de una carpeta en un sharepoint haria que no sea necesario publicar el reporte cada vez que preciso actualizar, sino solo reemplazar los archivos en excel de origen de datos para que el reporte pueda actualizarse.

Tengo los archivos en una carpeta ya en un sharepoint de mi empresa, lo que no se es como hacer que mi reporte use estos en vez de los que usa actualmente que los tengo alojado en mi disco.

Agradeceria la ayuda.