Gegevens importeren uit OneDrive en SharePoint naar Power Query / BI

Als u of uw bedrijf gegevens opslaat in de OneDrive-cloud of in een SharePoint-bedrijfsportal, kan het verrassend uitdagend zijn om er rechtstreeks verbinding mee te maken met Power Query in Excel of vanuit Power BI.

Toen ik ooit met een soortgelijk probleem werd geconfronteerd, was ik verrast te ontdekken dat er geen "legale" manieren zijn om het op te lossen. Om de een of andere reden bevat de lijst met beschikbare gegevensbronnen in Excel en zelfs in Power BI (waar de set connectors traditioneel breder is) om de een of andere reden niet de mogelijkheid om verbinding te maken met OneDrive-bestanden en -mappen.

Dus alle onderstaande opties zijn tot op zekere hoogte "krukken" die een kleine maar handmatige "afwerking met een vijl" vereisen. Maar deze krukken hebben een groot pluspunt: ze werken

Wat is het probleem?

Een korte introductie voor degenen die bracht de laatste 20 jaar in een coma door niet in het onderwerp.

OneDrive is een cloudopslagservice van Microsoft die in verschillende smaken verkrijgbaar is:

• OneDrive persoonlijk – voor gewone (niet-zakelijke) gebruikers. Ze geven je 5 GB gratis + extra ruimte voor een klein maandelijks bedrag.
• Microsoft Onedrive for Business – een optie voor zakelijke gebruikers en Office 365-abonnees met een veel groter beschikbaar volume (vanaf 1 TB of meer) en extra functies zoals versieopslag, enz.

Een speciaal geval van OneDrive for Business is het opslaan van gegevens op een SharePoint-bedrijfsportal - in dit scenario is OneDrive in feite een van de bibliotheken van SharePoint'a.

Bestanden zijn toegankelijk via de webinterface (https://onedrive.live.com-site of zakelijke SharePoint-site) of door geselecteerde mappen met uw pc te synchroniseren:

Meestal worden deze mappen opgeslagen in het gebruikersprofiel op station C - het pad ernaartoe ziet er ongeveer zo uit: C: GebruikersGebruikersnaamMicrosoft Onedrive). Een speciaal programma bewaakt de relevantie van bestanden en synchronisatie van alle wijzigingen – АOneDrive heren (blauwe of grijze wolk in de rechter benedenhoek van het scherm):

En nu het belangrijkste.

Als we gegevens van OneDrive naar Excel (via Power Query) of naar Power BI moeten laden, dan kunnen we natuurlijk op de gebruikelijke manier lokale bestanden en mappen specificeren die als bron moeten worden gesynchroniseerd via Gegevens ophalen – Uit bestand – Uit boek / Uit map (Gegevens ophalen — Uit bestand — Uit werkmap / map)Maar het zal geen directe link zijn naar de OneDrive-cloud.

Dat wil zeggen dat we in de toekomst, wanneer we bijvoorbeeld bestanden in de cloud wijzigen door andere gebruikers, moet eerst synchroniseren (dit gebeurt voor een lange tijd en is niet altijd handig) en alleen update dan onze vraag Power Query of Model in Power BI.

Natuurlijk rijst de vraag: hoe gegevens rechtstreeks uit OneDrive/SharePoint importeren, zodat de gegevens rechtstreeks vanuit de cloud worden geladen?

Optie 1: verbinding maken met een boek vanuit OneDrive voor Bedrijven of SharePoint

1. We openen het boek in onze Excel - een lokale kopie uit de gesynchroniseerde OneDrive-map als een gewoon bestand. Of open eerst de site in Excel Online en klik vervolgens op de knop Openen in Excel (Openen in Excel).
2. Ga naar Bestand – Details (Bestandsinformatie)
3. Kopieer het cloudpad naar het boek met de knop kopieer pad (Pad kopiëren) in de titel:

   

4. In een ander Excel-bestand of in Power BI, waar u de gegevens wilt invullen, selecteert u de opdrachten Gegevens ophalen – Van internet (Gegevens ophalen - van internet) en plak het gekopieerde pad in het adresveld.
5. Verwijderen aan het einde van het pad ?web=1 en klik op OK:

   

6. Selecteer in het venster dat verschijnt de autorisatiemethode Organisatie account (Organisatie-account) en klik op de knop Inloggen (Log in):

   

   Voer ons werkende login-wachtwoord in of selecteer een bedrijfsaccount uit de lijst die verschijnt. Als je alles goed doet, dan is de inscriptie Inloggen zou moeten veranderen in Log in als een andere gebruiker (Log in met een ander gebruikersaccount).
   

7. Klik op de knop versterken (Aansluiten).
   

Dan is alles hetzelfde als bij de gebruikelijke import van een boek - we selecteren de benodigde bladen, slimme tabellen voor import, enz.

Optie 2: verbinding maken met een bestand vanuit OneDrive Personal

Om verbinding te maken met een boek in een persoonlijke (niet-zakelijke) OneDrive-cloud, is de aanpak anders:

1. We openen de inhoud van de gewenste map op de OneDrive-website en vinden het geïmporteerde bestand.
2. Klik er met de rechtermuisknop op en selecteer opdracht Introductie (Integreren) of selecteer het bestand en selecteer een soortgelijke opdracht in het bovenste menu:

   

3. Klik in het paneel dat aan de rechterkant verschijnt op de knop creëren en kopieer de gegenereerde code:

   

4.  Plak de gekopieerde code in Kladblok en "eindig met een bestand":
   
   • Verwijder alles behalve de link tussen aanhalingstekens
     
   • Verwijder het blok cid=XXXXXXXXXXXX&
   • Vervangbaar woord insluiten on Download
   Als gevolg hiervan zou de broncode er als volgt uit moeten zien:

   

5. Dan is alles hetzelfde als bij de vorige methode. In een ander Excel-bestand of in Power BI, waar u de gegevens wilt invullen, selecteert u de opdrachten Gegevens ophalen – Van internet (Gegevens ophalen - van internet), plak het bewerkte pad in het adresveld en klik op OK.
6. Wanneer het autorisatievenster verschijnt, selecteert u de optie: Dakramen en raamkozijnen en voer indien nodig het inlogwachtwoord van OneDrive in.
   

Optie 3: Importeer de inhoud van een hele map uit OneDrive voor Bedrijven

Als je in Power Query of Power BI de inhoud van niet één bestand, maar een hele map tegelijk moet invullen (bijvoorbeeld met rapporten), dan is de aanpak iets eenvoudiger:

1. Klik in Verkenner met de rechtermuisknop op de lokale gesynchroniseerde map die voor ons van belang is in OneDrive en selecteer Bekijk ter plaatse (Bekijk online).
2. Kopieer in de adresbalk van de browser het eerste deel van het adres - tot aan het woord / _lay-outs:

   

3. Selecteer de opdrachten in de Excel-werkmap waarin u de gegevens wilt laden of in het Power BI Desktop-rapport: Gegevens ophalen - uit bestand - uit SharePoint-map (Gegevens ophalen — Uit bestand — Uit SharePoint-map):

   

   Plak vervolgens het gekopieerde padfragment in het adresveld en klik op OK:

   

   Als er een autorisatievenster verschijnt, selecteer dan het type Microsoft-account (Microsoft account), klik op de knop Inloggen (Log in), en dan, na een succesvolle login, op de knop versterken (Aansluiten):

   

4. Daarna worden alle bestanden uit SharePoint opgevraagd en gedownload en verschijnt er een voorbeeldvenster, waar je veilig op kunt klikken Gegevens converteren (Gegevens transformeren).
   
5. Verdere bewerking van de lijst van alle bestanden en hun samenvoeging vindt standaard al plaats in Power Query of in Power BI. Om de zoekcirkel alleen te beperken tot de map die we nodig hebben, kunt u het filter op kolom gebruiken folder Path (1) en vouw vervolgens de volledige inhoud van de gevonden bestanden uit met de knop in de kolom Content (2):

   

• Tabellen samenstellen uit verschillende bestanden met Power Query
• Wat is Power Query, Power Pivot, Power BI en hoe kunnen ze u helpen?
• Gegevens van alle bladen van het boek in één tabel verzamelen