Importar datos de OneDrive y SharePoint a Power Query/BI

Si usted o su empresa almacenan datos en la nube de OneDrive o en un portal de empresa de SharePoint, conectarse directamente a ellos mediante Power Query en Excel o desde Power BI puede ser un desafío sorprendente.

Cuando una vez enfrenté un problema similar, me sorprendió descubrir que no hay formas "legales" de resolverlo. Por alguna razón, la lista de fuentes de datos disponibles en Excel e incluso en Power BI (donde el conjunto de conectores es tradicionalmente más amplio) por alguna razón no incluye la capacidad de conectarse a archivos y carpetas de OneDrive.

Así que todas las opciones que se ofrecen a continuación son, en mayor o menor grado, “muletas” que requieren un pequeño pero manual “acabado con lima”. Pero estas muletas tienen una gran ventaja: funcionan 🙂

¿Cuál es el problema?

Una breve introducción para aquellos que pasó los últimos 20 años en coma no en el tema.

OneDrive es un servicio de almacenamiento en la nube de Microsoft que viene en varios sabores:

• Personal de OneDrive – para usuarios ordinarios (no corporativos). Te dan 5 GB gratis + espacio extra por una pequeña tarifa mensual.
• Onedrive para su Negocio – una opción para usuarios corporativos y suscriptores de Office 365 con un volumen disponible mucho mayor (desde 1 TB o más) y funciones adicionales como almacenamiento de versiones, etc.

Un caso especial de OneDrive for Business es el almacenamiento de datos en un portal corporativo de SharePoint; en este escenario, OneDrive es, de hecho, una de las bibliotecas de SharePoint'a.

Se puede acceder a los archivos a través de la interfaz web (sitio https://onedrive.live.com o sitio corporativo de SharePoint) o sincronizando las carpetas seleccionadas con su PC:

Por lo general, estas carpetas se almacenan en el perfil de usuario en la unidad C; la ruta a ellas se parece a C: UsuariosUsuarioonedrive). Un programa especial supervisa la relevancia de los archivos y la sincronización de todos los cambios: АCaballero de OneDrive (nube azul o gris en la esquina inferior derecha de la pantalla):

Y ahora lo principal.

Si necesitamos cargar datos de OneDrive a Excel (a través de Power Query) o a Power BI, entonces, por supuesto, podemos especificar archivos y carpetas locales para sincronizarlos como fuente de la manera habitual a través de Obtener datos – Desde archivo – Desde libro / Desde carpeta (Obtener datos — Desde archivo — Desde libro de trabajo / Carpeta)Pero no será un enlace directo a la nube de OneDrive.

Es decir, en el futuro, al cambiar, por ejemplo, archivos en la nube por parte de otros usuarios, necesito sincronizar primero (esto sucede durante mucho tiempo y no siempre es conveniente) y solo luego actualice nuestra consulta Power Query o Modelo en Power BI.

Naturalmente, surge la pregunta: ¿Cómo importar datos de OneDrive/SharePoint directamente para que los datos se carguen directamente desde la nube?

Opción 1: conectarse a un libro desde OneDrive para la Empresa o SharePoint

1. Abrimos el libro en nuestro Excel: una copia local de la carpeta OneDrive sincronizada como un archivo normal. O abra el sitio primero en Excel Online y luego haga clic en el botón Abrir en Excel (Abrir en Excel).
2. Ve a Detalles del archivo (Información del archivo)
3. Copie la ruta de la nube al libro con el botón copiar ruta (Copiar ruta) En el título:

   

4. En otro archivo de Excel o en Power BI, donde desea completar los datos, seleccione los comandos Obtener datos – De Internet (Obtener datos: de la web) y pegue la ruta copiada en el campo de dirección.
5. Eliminar al final de la ruta ?web=1 y haga clic en OK:

   

6. En la ventana que aparece, seleccione el método de autorización cuenta de la organización (Cuenta de la organización) y haga clic en el botón Iniciar (Iniciar sesión):

   

   Ingrese nuestra contraseña de inicio de sesión de trabajo o seleccione una cuenta corporativa de la lista que aparece. Si haces todo bien, entonces la inscripción. Iniciar debería cambiar a Registrate como un usuario diferente (Iniciar sesión con otra cuenta de usuario).
   

7. Haga clic en el botón conexión (Conectar).
   

Luego, todo es igual que con la importación habitual de un libro: seleccionamos las hojas necesarias, las tablas inteligentes para la importación, etc.

Opción 2: conectarse a un archivo desde OneDrive Personal

Para conectarse a un libro en una nube OneDrive personal (no corporativa), el enfoque será diferente:

1. Abrimos el contenido de la carpeta deseada en el sitio web de OneDrive y encontramos el archivo importado.
2. Haga clic derecho sobre él y seleccione el comando Introducción (Empotrar) o seleccione el archivo y seleccione un comando similar en el menú superior:

   

3. En el panel que aparece a la derecha, haga clic en el botón Crear y copiar el código generado:

   

4.  Pegue el código copiado en el Bloc de notas y "terminar con un archivo":
   
   • Eliminar todo excepto el enlace entre comillas
     
   • Eliminar el bloque cid=XXXXXXXXXXXX&
   • Palabra reemplazable incrustar on descargar
   Como resultado, el código fuente debería verse así:

   

5. Entonces todo es igual que en el método anterior. En otro archivo de Excel o en Power BI, donde desea completar los datos, seleccione los comandos Obtener datos – De Internet (Obtener datos: de la web), pegue la ruta editada en el campo de dirección y haga clic en Aceptar.
6. Cuando aparezca la ventana de autorización, seleccione la opción Windows y, si es necesario, ingrese la contraseña de inicio de sesión de OneDrive.
   

Opción 3: importar el contenido de una carpeta completa desde OneDrive para empresas

Si necesita completar Power Query o Power BI el contenido de no un archivo, sino una carpeta completa a la vez (por ejemplo, con informes), entonces el enfoque será un poco más simple:

1. En Explorer, haga clic con el botón derecho en la carpeta local sincronizada que nos interese en OneDrive y seleccione ver en el sitio (Ver online).
2. En la barra de direcciones del navegador, copie la parte inicial de la dirección, hasta la palabra / _diseños:

   

3. En el libro de trabajo de Excel donde desea cargar los datos o en el informe de Power BI Desktop, seleccione los comandos Obtener datos – Desde archivo – Desde carpeta de SharePoint (Obtener datos — Desde archivo — Desde carpeta de SharePoint):

   

   Luego pegue el fragmento de ruta copiado en el campo de dirección y haga clic en OK:

   

   Si aparece una ventana de autorización, seleccione el tipo Cuenta de Microsoft (Cuenta de Microsoft), haga clic en el botón Iniciar (Iniciar sesión), y luego, después de un inicio de sesión exitoso, en el botón conexión (Conectar):

   

4. Después de eso, todos los archivos de SharePoint se solicitan y descargan y aparece una ventana de vista previa, donde puede hacer clic con seguridad en Convertir datos (Transformar datos).
   
5. La edición adicional de la lista de todos los archivos y su combinación ya se realiza en Power Query o en Power BI de forma estándar. Para restringir el círculo de búsqueda solo a la carpeta que necesitamos, puede usar el filtro por columna Ruta de la carpeta (1) y luego expanda todo el contenido de los archivos encontrados usando el botón en la columna Contenido (2):

   

• Ensamblar tablas de diferentes archivos usando Power Query
• Qué es Power Query, Power Pivot, Power BI y cómo pueden ayudarte
• Recopilación de datos de todas las hojas del libro en una tabla