Uvoz podataka iz OneDrive i SharePointa u Power Query / BI

Ako vi ili vaša tvrtka pohranjujete podatke u OneDrive oblaku ili na portalu tvrtke SharePoint, izravno povezivanje s njima pomoću Power Queryja u Excelu ili iz Power BI-ja može biti iznenađujuće izazovno.

Kad sam se jednom suočio sa sličnim problemom, iznenadio sam se kada sam otkrio da ne postoje "legalni" načini za njegovo rješavanje. Iz nekog razloga, popis dostupnih izvora podataka u Excelu, pa čak iu Power BI (gdje je skup konektora tradicionalno širi) iz nekog razloga ne uključuje mogućnost povezivanja s datotekama i mapama OneDrivea.

Dakle, sve dolje ponuđene mogućnosti su u jednoj ili drugoj mjeri "štake" koje zahtijevaju malo, ali ručno "dovršavanje turpijom". Ali ove štake imaju veliki plus – rade 🙂

U čemu je problem?

Kratak uvod za one koji proveo zadnjih 20 godina u komi nije u temi.

OneDrive je Microsoftov servis za pohranu u oblaku koji dolazi u nekoliko verzija:

• OneDrive Personal – za obične (nekorporativne) korisnike. Daju vam 5 GB besplatno + dodatni prostor uz malu mjesečnu naknadu.
• OneDrive za poslovanje – opcija za korporativne korisnike i pretplatnike na Office 365 s mnogo većim dostupnim volumenom (od 1TB ili više) i dodatnim značajkama kao što je pohrana verzija itd.

Poseban slučaj OneDrive for Business je pohranjivanje podataka na korporativni portal SharePoint – u ovom scenariju, OneDrive je, zapravo, jedna od biblioteka SharePoint'a.

Datotekama se može pristupiti putem web sučelja (https://onedrive.live.com stranica ili korporativna SharePoint stranica) ili sinkronizacijom odabranih mapa s vašim računalom:

Obično su te mape pohranjene u korisničkom profilu na pogonu C - put do njih izgleda otprilike ovako C: KorisniciKorisničko imeOneDrive). Poseban program prati relevantnost datoteka i sinkronizaciju svih promjena – АOneDrive gospodine (plavi ili sivi oblak u donjem desnom kutu ekrana):

A sad ono glavno.

Ako trebamo učitati podatke s OneDrivea u Excel (putem Power Queryja) ili u Power BI, onda naravno možemo odrediti lokalne datoteke i mape koje će se sinkronizirati kao izvor na uobičajeni način putem Dohvati podatke – Iz datoteke – Iz knjige / Iz mape (Dohvati podatke — Iz datoteke — Iz radne knjige/mape)Ali to neće biti izravna poveznica na oblak OneDrive.

Odnosno, u budućnosti, kada drugi korisnici mijenjaju npr. datoteke u oblaku, mi prvo treba sinkronizirati (ovo se događa dugo i nije uvijek zgodno) i samo zatim ažurirajte naš upit Power Query ili model u Power BI-ju.

Naravno, postavlja se pitanje: kako uvesti podatke iz OneDrive/SharePointa izravno tako da se podaci učitavaju izravno iz oblaka?

Opcija 1: Povežite se s knjigom iz OneDrive for Business ili SharePoint

1. Knjigu otvaramo u našem Excelu – lokalnu kopiju iz sinkronizirane OneDrive mape kao običnu datoteku. Ili prvo otvorite stranicu u programu Excel Online, a zatim kliknite na gumb Otvori u Excelu (Otvori u Excelu).
2. Idi na Datoteka – Detalji (Datoteka — Info)
3. Kopirajte putanju oblaka u knjigu pomoću gumba put kopiranja (Kopiraj put) u naslovu:

   

4. U drugoj Excel datoteci ili u Power BI-ju, gdje želite ispuniti podatke, odaberite naredbe Dohvatite podatke – s interneta (Dohvati podatke — s weba) i zalijepite kopirani put u adresno polje.
5. Izbriši na kraju staze ?web=1 i kliknite na OK:

   

6. U prozoru koji se pojavi odaberite način autorizacije Račun organizacije (Račun organizacije) i kliknite na gumb Prijaviti se (Prijaviti se):

   

   Unesite našu radnu lozinku za prijavu ili odaberite korporativni račun s popisa koji se pojavi. Ako sve učinite kako treba, onda natpis Prijaviti se treba promijeniti u Prijavite se kao drugi korisnik (Prijava s drugim korisničkim računom).
   

7. Kliknite na gumb veza (Spojiti).
   

Tada je sve isto kao i kod uobičajenog uvoza knjige – odabiremo potrebne listove, pametne tablice za uvoz itd.

Opcija 2: Povežite se s datotekom iz OneDrive Personal

Za povezivanje s knjigom u osobnom (nekorporativnom) OneDrive oblaku pristup će biti drugačiji:

1. Otvorimo sadržaj željene mape na web stranici OneDrive i pronađemo uvezenu datoteku.
2. Kliknite desnom tipkom miša na njega i odaberite naredbu Uvod (Ugraditi) ili odaberite datoteku i odaberite sličnu naredbu u gornjem izborniku:

   

3. Na ploči koja se pojavi s desne strane kliknite gumb stvoriti i kopirajte generirani kod:

   

4.  Zalijepite kopirani kod u Notepad i "završite s datotekom":
   
   • Uklonite sve osim veze pod navodnicima
     
   • Izbriši blok cid=XXXXXXXXXXXX&
   • Zamjenjiva riječ Ugraditi on preuzimanje
   Kao rezultat, izvorni kod bi trebao izgledati ovako:

   

5. Tada je sve isto kao u prethodnoj metodi. U drugoj Excel datoteci ili u Power BI-ju, gdje želite ispuniti podatke, odaberite naredbe Dohvatite podatke – s interneta (Dohvati podatke — s weba), zalijepite uređeni put u polje adrese i kliknite U redu.
6. Kada se pojavi prozor za autorizaciju, odaberite opciju Windows i, ako je potrebno, unesite lozinku za prijavu iz OneDrive.
   

Opcija 3: Uvezite sadržaj cijele mape iz OneDrive for Business

Ako u Power Query ili Power BI trebate ispuniti sadržaj ne jedne datoteke, već cijele mape odjednom (na primjer, s izvješćima), tada će pristup biti malo jednostavniji:

1. U Exploreru desnom tipkom miša kliknite lokalnu sinkroniziranu mapu koja nas zanima u OneDriveu i odaberite Pogledajte na mjestu (Pogledaj online).
2. U adresnu traku preglednika kopirajte početni dio adrese – do riječi / _izgledi:

   

3. U radnoj knjizi programa Excel u koju želite učitati podatke ili u izvješću Power BI Desktop odaberite naredbe Dobivanje podataka – iz datoteke – iz mape SharePoint (Dohvati podatke — Iz datoteke — Iz mape SharePoint):

   

   Zatim zalijepite kopirani fragment staze u adresno polje i kliknite OK:

   

   Ako se pojavi prozor za autorizaciju, odaberite vrstu Microsoftov račun (Microsoftov račun), kliknite na gumb Prijaviti se (Prijaviti se), a zatim, nakon uspješne prijave, na gumb veza (Spojiti):

   

4. Nakon toga, sve datoteke iz SharePointa su zatražene i preuzete te se pojavljuje prozor za pregled, gdje možete sigurno kliknuti na Pretvori podatke (Transformacija podataka).
   
5. Daljnje uređivanje popisa svih datoteka i njihovo spajanje odvija se već u Power Queryju ili Power BI-u na standardni način. Da biste suzili krug pretraživanja samo na mapu koja nam je potrebna, možete koristiti filtar po stupcu Put do mape (1), a zatim proširite cijeli sadržaj pronađenih datoteka pomoću gumba u stupcu Sadržaj (2):

   

• Sastavljanje tablica iz različitih datoteka pomoću Power Queryja
• Što je Power Query, Power Pivot, Power BI i kako vam oni mogu pomoći
• Prikupljanje podataka sa svih listova knjige u jednu tablicu