Importer des données de OneDrive et SharePoint vers Power Query / BI

Si vous ou votre entreprise stockez des données dans le cloud OneDrive ou dans un portail d'entreprise SharePoint, s'y connecter directement à l'aide de Power Query dans Excel ou de Power BI peut être étonnamment difficile.

Lorsque j'ai été confronté à un problème similaire, j'ai été surpris de constater qu'il n'existait aucun moyen «légal» de le résoudre. Pour une raison quelconque, la liste des sources de données disponibles dans Excel et même dans Power BI (où l'ensemble de connecteurs est traditionnellement plus large) n'inclut pas pour une raison quelconque la possibilité de se connecter aux fichiers et dossiers OneDrive.

Ainsi toutes les options proposées ci-dessous sont, à un degré ou à un autre, des « béquilles » qui nécessitent une petite mais manuelle « finition à la lime ». Mais ces béquilles ont un gros plus - elles fonctionnent 🙂

Quel est le problème?

Une petite introduction pour ceux qui passé les 20 dernières années dans le coma pas dans le sujet.

OneDrive est un service de stockage cloud de Microsoft qui se décline en plusieurs versions :

• OneDrive personnel – pour les utilisateurs ordinaires (hors entreprise). Ils vous donnent 5 Go gratuitement + un espace supplémentaire pour un petit abonnement mensuel.
• Onedrive for Business – une option pour les utilisateurs professionnels et les abonnés Office 365 avec un volume disponible beaucoup plus important (à partir de 1 To ou plus) et des fonctionnalités supplémentaires comme le stockage des versions, etc.

Un cas particulier de OneDrive for Business consiste à stocker des données sur un portail d'entreprise SharePoint - dans ce scénario, OneDrive est, en fait, l'une des bibliothèques de SharePoint'a.

Les fichiers sont accessibles via l'interface Web (site https://onedrive.live.com ou site SharePoint de l'entreprise) ou en synchronisant les dossiers sélectionnés avec votre PC :

Habituellement, ces dossiers sont stockés dans le profil utilisateur sur le lecteur C - le chemin d'accès ressemble à quelque chose comme C : UtilisateursNom d'utilisateuronedrive). Un programme spécial surveille la pertinence des fichiers et la synchronisation de tous les changements - АGentilhomme OneDrive (nuage bleu ou gris dans le coin inférieur droit de l'écran) :

Et maintenant l'essentiel.

Si nous devons charger des données de OneDrive vers Excel (via Power Query) ou vers Power BI, nous pouvons bien sûr spécifier les fichiers et dossiers locaux à synchroniser comme source de la manière habituelle via Obtenir des données – À partir d'un fichier – À partir d'un livre / À partir d'un dossier (Obtenir des données - À partir d'un fichier - À partir d'un classeur / dossier)Mais ce ne sera pas un lien direct vers le cloud OneDrive.

Autrement dit, à l'avenir, lors de la modification, par exemple, de fichiers dans le cloud par d'autres utilisateurs, nous besoin de synchroniser d'abord (cela se produit depuis longtemps et n'est pas toujours pratique) et seulement puis mettre à jour notre requête Power Query ou Modèle dans Power BI.

Naturellement, la question se pose: comment importer des données depuis OneDrive/SharePoint directement pour que les données soient chargées directement depuis le cloud ?

Option 1 : Se connecter à un livre depuis OneDrive Entreprise ou SharePoint

1. Nous ouvrons le livre dans notre Excel - une copie locale du dossier OneDrive synchronisé en tant que fichier normal. Ou ouvrez d'abord le site dans Excel Online, puis cliquez sur le bouton Ouvrir dans Excel (Ouvrir dans Excel).
2. Cliquez sur Fichier – Détails (Fichier — Infos)
3. Copiez le chemin du nuage vers le livre avec le bouton copier le chemin (Copier le chemin) dans le titre :

   

4. Dans un autre fichier Excel ou dans Power BI, où vous souhaitez remplir les données, sélectionnez les commandes Obtenir des données - À partir d'Internet (Obtenir des données - à partir du Web) et collez le chemin copié dans le champ d'adresse.
5. Supprimer à la fin du chemin ?web=1 et cliquez sur OK:

   

6. Dans la fenêtre qui apparaît, sélectionnez la méthode d'autorisation Compte d'organisation (Compte de l'organisation) et cliquez sur le bouton Se connecter (Connexion):

   

   Entrez notre mot de passe de connexion de travail ou sélectionnez un compte d'entreprise dans la liste qui apparaît. Si vous faites tout correctement, alors l'inscription Se connecter devrait changer en Connectez-vous en tant qu'utilisateur différent (Se connecter avec un autre compte utilisateur).
   

7. Cliquez sur le bouton connexion (Relier).
   

Ensuite, tout est comme avec l'importation habituelle d'un livre - nous sélectionnons les feuilles nécessaires, les tableaux intelligents pour l'importation, etc.

Option 2 : se connecter à un fichier à partir de OneDrive personnel

Pour se connecter à un livre dans un cloud OneDrive personnel (hors entreprise), la démarche sera différente :

1. Nous ouvrons le contenu du dossier souhaité sur le site Web OneDrive et trouvons le fichier importé.
2. Faites un clic droit dessus et sélectionnez la commande Introduction (Intégrer) ou sélectionnez le fichier et sélectionnez une commande similaire dans le menu du haut :

   

3. Dans le panneau qui apparaît à droite, cliquez sur le bouton Création et copiez le code généré :

   

4.  Collez le code copié dans le Bloc-notes et "terminez avec un fichier":
   
   • Supprimer tout sauf le lien entre guillemets
     
   • Supprimer le bloc cid=XXXXXXXXXXXX&
   • Mot remplaçable enchâsser on download
   En conséquence, le code source devrait ressembler à ceci :

   

5. Ensuite, tout est comme dans la méthode précédente. Dans un autre fichier Excel ou dans Power BI, où vous souhaitez remplir les données, sélectionnez les commandes Obtenir des données - À partir d'Internet (Obtenir des données - à partir du Web), collez le chemin modifié dans le champ d'adresse et cliquez sur OK.
6. Lorsque la fenêtre d'autorisation apparaît, sélectionnez l'option Windows et, si nécessaire, entrez le mot de passe de connexion de OneDrive.
   

Option 3 : Importer le contenu d'un dossier entier à partir de OneDrive Entreprise

Si vous devez remplir dans Power Query ou Power BI le contenu non pas d'un fichier, mais d'un dossier entier à la fois (par exemple, avec des rapports), alors l'approche sera un peu plus simple :

1. Dans l'explorateur, cliquez avec le bouton droit sur le dossier synchronisé local qui nous intéresse dans OneDrive et sélectionnez Voir sur place (Voir en ligne).
2. Dans la barre d'adresse du navigateur, copiez la partie initiale de l'adresse - jusqu'au mot / _layouts:

   

3. Dans le classeur Excel où vous souhaitez charger les données ou dans le rapport Power BI Desktop, sélectionnez les commandes Obtenir des données - à partir d'un fichier - à partir d'un dossier SharePoint (Obtenir des données — À partir du fichier — À partir du dossier SharePoint):

   

   Collez ensuite le fragment de chemin copié dans le champ d'adresse et cliquez sur OK:

   

   Si une fenêtre d'autorisation apparaît, sélectionnez le type Compte Microsoft (Compte microsoft), cliquez sur le bouton Se connecter (Connexion), puis, après une connexion réussie, sur le bouton connexion (Relier):

   

4. Après cela, tous les fichiers de SharePoint sont demandés et téléchargés et une fenêtre d'aperçu apparaît, où vous pouvez cliquer en toute sécurité sur Convertir des données (Transformer les données).
   
5. La modification ultérieure de la liste de tous les fichiers et leur fusion ont déjà lieu dans Power Query ou dans Power BI de manière standard. Pour restreindre le cercle de recherche uniquement au dossier dont nous avons besoin, vous pouvez utiliser le filtre par colonne Chemin du dossier (1) puis développez tout le contenu des fichiers trouvés à l'aide du bouton dans la colonne Contenu (2):

   

• Assemblage de tables à partir de différents fichiers à l'aide de Power Query
• Qu'est-ce que Power Query, Power Pivot, Power BI et comment ils peuvent vous aider
• Collecter les données de toutes les feuilles du livre dans un seul tableau