Importer data fra OneDrive og SharePoint til Power Query / BI

Hvis du eller din virksomhed gemmer data i OneDrive-skyen eller i en SharePoint-virksomhedsportal, kan det være overraskende udfordrende at oprette forbindelse direkte til det ved hjælp af Power Query i Excel eller fra Power BI.

Da jeg engang stod over for et lignende problem, blev jeg overrasket over at opdage, at der ikke er nogen "lovlige" måder at løse det på. Af en eller anden grund inkluderer listen over tilgængelige datakilder i Excel og endda i Power BI (hvor sættet af stik traditionelt er bredere) af en eller anden grund ikke muligheden for at oprette forbindelse til OneDrive-filer og -mapper.

Så alle mulighederne nedenfor er i en eller anden grad "krykker", der kræver en lille, men manuel "afslutning med en fil". Men disse krykker har et stort plus – de virker 🙂

Hvad er problemet?

En kort introduktion til dem, der tilbragt de sidste 20 år i koma ikke i faget.

OneDrive er en cloud storage-tjeneste fra Microsoft, der kommer i flere varianter:

• OneDrive Personal – for almindelige (ikke-virksomheds)brugere. De giver dig 5 GB gratis + ekstra plads for et lille månedligt gebyr.
• OneDrive for Business – en mulighed for virksomhedsbrugere og Office 365-abonnenter med en meget større tilgængelig volumen (fra 1 TB eller mere) og yderligere funktioner som versionslagring osv.

Et særligt tilfælde af OneDrive for Business er lagring af data på en SharePoint-virksomhedsportal – i dette scenarie er OneDrive i virkeligheden et af SharePoint'a-bibliotekerne.

Filer kan tilgås enten via webgrænsefladen (https://onedrive.live.com-webstedet eller virksomhedens SharePoint-websted) eller ved at synkronisere udvalgte mapper med din pc:

Normalt er disse mapper gemt i brugerprofilen på drev C - stien til dem ser nogenlunde sådan ud C: BrugereBrugernavnOneDrive). Et særligt program overvåger relevansen af ​​filer og synkronisering af alle ændringer – АOneDrive herre (blå eller grå sky i nederste højre hjørne af skærmen):

Og nu det vigtigste.

Hvis vi skal indlæse data fra OneDrive til Excel (via Power Query) eller til Power BI, så kan vi naturligvis angive lokale filer og mapper, der skal synkroniseres som kilde på sædvanlig vis via Hent data – Fra fil – Fra bog / Fra mappe (Hent data — fra fil — fra projektmappe/mappe)Men det vil ikke være et direkte link til OneDrive-skyen.

Det vil sige, at i fremtiden, når vi ændrer for eksempel filer i skyen af ​​andre brugere skal synkroniseres først (dette sker i lang tid og er ikke altid praktisk) og kun opdater derefter vores forespørgsel Power Query eller Model i Power BI.

Spørgsmålet opstår naturligvis: hvordan importeres data fra OneDrive/SharePoint direkte, så dataene indlæses direkte fra skyen?

Mulighed 1: Opret forbindelse til en bog fra OneDrive for Business eller SharePoint

1. Vi åbner bogen i vores Excel – en lokal kopi fra den synkroniserede OneDrive-mappe som en almindelig fil. Eller åbn siden først i Excel Online, og klik derefter på knappen Åbn i Excel (Åbn i Excel).
2. Gå til Fil – Detaljer (Fil — Info)
3. Kopier skystien til bogen med knappen kopisti (Kopiér sti) i titlen:

   

4. I en anden Excel-fil eller i Power BI, hvor du vil udfylde dataene, skal du vælge kommandoerne Hent data – fra internettet (Hent data - fra nettet) og indsæt den kopierede sti i adressefeltet.
5. Slet i slutningen af ​​stien ?web=1 og klik på OK:

   

6. I det vindue, der vises, skal du vælge godkendelsesmetoden Organisationskonto (Organisationskonto) og klik på knappen Log på (Log på):

   

   Indtast vores fungerende login-adgangskode, eller vælg en virksomhedskonto fra listen, der vises. Hvis du gør alt rigtigt, så inskriptionen Log på skulle ændre sig til Log ind som en anden bruger (Log ind med en anden brugerkonto).
   

7. Klik på knappen tilslutning (Opret forbindelse).
   

Så er alt det samme som ved den sædvanlige import af en bog – vi udvælger de nødvendige ark, smarte borde til import mv.

Mulighed 2: Opret forbindelse til en fil fra OneDrive Personal

For at oprette forbindelse til en bog i en personlig (ikke-virksomheds) OneDrive-sky vil fremgangsmåden være anderledes:

1. Vi åbner indholdet af den ønskede mappe på OneDrive-webstedet og finder den importerede fil.
2. Højreklik på den og vælg kommando Introduktion (Indlejre) eller vælg filen og vælg en lignende kommando i topmenuen:

   

3. Klik på knappen i panelet, der vises til højre Opret og kopier den genererede kode:

   

4.  Indsæt den kopierede kode i Notesblok og "afslut med en fil":
   
   • Fjern alt undtagen linket i anførselstegn
     
   • Slet blokken cid=XXXXXXXXXXXX&
   • Udskifteligt ord Integrer on downloade
   Som et resultat skulle kildekoden se sådan ud:

   

5. Så er alt det samme som i den foregående metode. I en anden Excel-fil eller i Power BI, hvor du vil udfylde dataene, skal du vælge kommandoerne Hent data – fra internettet (Hent data - fra nettet), indsæt den redigerede sti i adressefeltet og klik på OK.
6. Når autorisationsvinduet vises, skal du vælge indstillingen Windows og indtast om nødvendigt login-adgangskoden fra OneDrive.
   

Mulighed 3: Importer indholdet af en hel mappe fra OneDrive for Business

Hvis du har brug for at udfylde Power Query eller Power BI indholdet af ikke én fil, men en hel mappe på én gang (for eksempel med rapporter), så vil fremgangsmåden være lidt enklere:

1. I Stifinder skal du højreklikke på den lokale synkroniserede mappe af interesse for os i OneDrive og vælge Se på stedet (Se online).
2. Kopier den indledende del af adressen i browserens adresselinje – op til ordet / _layouts:

   

3. Vælg kommandoerne i Excel-projektmappen, hvor du vil indlæse dataene, eller i Power BI Desktop-rapporten Hent data – fra fil – fra SharePoint-mappe (Hent data - fra fil - fra SharePoint-mappe):

   

   Indsæt derefter det kopierede stifragment i adressefeltet og klik OK:

   

   Hvis et autorisationsvindue vises, skal du vælge typen Microsoft-konto (Microsoft-konto), klik på knappen Log på (Log på), og derefter, efter et vellykket login, på knappen tilslutning (Opret forbindelse):

   

4. Derefter bliver alle filer fra SharePoint bedt om og downloadet, og et forhåndsvisningsvindue kommer frem, hvor du trygt kan klikke på Konverter data (Transformer data).
   
5. Yderligere redigering af listen over alle filer og deres sammenfletning sker allerede i Power Query eller i Power BI på standardmåden. For kun at indsnævre søgecirklen til den mappe, vi har brug for, kan du bruge filteret efter kolonne Folder Path (1) og udvid derefter hele indholdet af de fundne filer ved hjælp af knappen i kolonnen Indhold (2):

   

• Samling af tabeller fra forskellige filer ved hjælp af Power Query
• Hvad er Power Query, Power Pivot, Power BI, og hvordan de kan hjælpe dig
• Indsamling af data fra alle ark i bogen i én tabel