Importujte data z OneDrivu a SharePointu do Power Query / BI

Pokud vy nebo vaše společnost ukládáte data v cloudu OneDrive nebo na firemním portálu SharePointu, může být připojení přímo k němu pomocí Power Query v Excelu nebo z Power BI překvapivě náročné.

Když jsem jednou čelil podobnému problému, byl jsem překvapen, když jsem zjistil, že neexistují žádné „legální“ způsoby, jak to vyřešit. Z nějakého důvodu seznam dostupných zdrojů dat v Excelu a dokonce i v Power BI (kde je sada konektorů tradičně širší) z nějakého důvodu neobsahuje možnost připojení k souborům a složkám OneDrive.

Všechny níže nabízené možnosti jsou tedy do té či oné míry „berličkami“, které vyžadují malé, ale ruční „dokončení pilníkem“. Tyto berle mají ale velké plus – fungují 🙂

Co je za problém?

Krátký úvod pro ty, kteří strávil posledních 20 let v kómatu ne v předmětu.

OneDrive je služba cloudového úložiště od společnosti Microsoft, která je k dispozici v několika variantách:

• OneDrive Personal – pro běžné (nefiremní) uživatele. Poskytnou vám 5 GB zdarma + prostor navíc za malý měsíční poplatek.
• Onedrive pro podnikání – možnost pro podnikové uživatele a předplatitele Office 365 s mnohem větším dostupným objemem (od 1 TB nebo více) a dalšími funkcemi, jako je úložiště verzí atd.

Zvláštním případem OneDrive pro firmy je ukládání dat na firemním portálu SharePoint – v tomto scénáři je OneDrive ve skutečnosti jednou z knihoven SharePoint'a.

K souborům lze přistupovat buď prostřednictvím webového rozhraní (web https://onedrive.live.com nebo podnikový web SharePoint) nebo synchronizací vybraných složek s počítačem:

Obvykle jsou tyto složky uloženy v uživatelském profilu na jednotce C – cesta k nim vypadá asi takto C: UživateléUživatelské jménoonedrive). Speciální program hlídá relevanci souborů a synchronizaci všech změn – АGene OneDrive (modrý nebo šedý mrak v pravém dolním rohu obrazovky):

A teď to hlavní.

Pokud potřebujeme načíst data z OneDrive do Excelu (přes Power Query) nebo do Power BI, pak samozřejmě můžeme určit lokální soubory a složky, které se mají synchronizovat jako zdroj běžným způsobem přes Získat data – Ze souboru – Z knihy / Ze složky (Získat data — Ze souboru — Ze sešitu / složky)Ale nepůjde o přímé propojení s cloudem OneDrive.

Tedy v budoucnu při změně např. souborů v cloudu jinými uživateli my je třeba nejprve synchronizovat (to se děje po dlouhou dobu a není to vždy vhodné) a pouze pak aktualizujte náš dotaz Power Query nebo model v Power BI.

Přirozeně se nabízí otázka: jak přímo importovat data z OneDrive/SharePoint, aby se data načítala přímo z cloudu?

Možnost 1: Připojte se ke knize z OneDrivu pro firmy nebo SharePointu

1. Knihu otevřeme v našem Excelu – místní kopii ze synchronizované složky OneDrive jako běžný soubor. Nebo otevřete web nejprve v Excel Online a poté klikněte na tlačítko Otevřít v Excelu (Otevřít v Excelu).
2. Jít do Soubor – Podrobnosti (Soubor — Informace)
3. Zkopírujte cestu mraku do knihy pomocí tlačítka kopírovat cestu (Kopírovat cestu) v názvu:

   

4. V jiném souboru Excelu nebo v Power BI, kde chcete vyplnit data, vyberte příkazy Získejte data – z internetu (Získat data – z webu) a vložte zkopírovanou cestu do pole adresy.
5. Smazat na konci cesty ?web=1 a klepněte na tlačítko OK:

   

6. V zobrazeném okně vyberte způsob autorizace Účet organizace (Účet organizace) a klikněte na tlačítko Přihlásit se (Přihlásit se):

   

   Zadejte naše funkční přihlašovací heslo nebo vyberte firemní účet ze seznamu, který se zobrazí. Pokud uděláte vše správně, pak nápis Přihlásit se by se měl změnit na Přihlaste se jako jiný uživatel (Přihlaste se pomocí jiného uživatelského účtu).
   

7. Klikněte na tlačítko spojení (Připojit).
   

Pak už je vše jako u běžného importu knihy – vybereme potřebné listy, chytré tabulky pro import atp.

Možnost 2: Připojte se k souboru z OneDrive Personal

Chcete-li se připojit ke knize v osobním (nepodnikovém) cloudu OneDrive, bude postup odlišný:

1. Otevřeme obsah požadované složky na webu OneDrive a najdeme importovaný soubor.
2. Klikněte na něj pravým tlačítkem a vyberte příkaz Úvod (Vložit) nebo vyberte soubor a vyberte podobný příkaz v horní nabídce:

   

3. Na panelu, který se zobrazí vpravo, klikněte na tlačítko Vytvořit a zkopírujte vygenerovaný kód:

   

4.  Vložte zkopírovaný kód do poznámkového bloku a „dokončete souborem“:
   
   • Odstraňte vše kromě odkazu v uvozovkách
     
   • Smazat blok cid=XXXXXXXXXXXX&
   • Nahraditelné slovo vložit on download
   V důsledku toho by zdrojový kód měl vypadat takto:

   

5. Pak je vše stejné jako v předchozí metodě. V jiném souboru Excelu nebo v Power BI, kde chcete vyplnit data, vyberte příkazy Získejte data – z internetu (Získat data – z webu), vložte upravenou cestu do pole adresy a klikněte na OK.
6. Když se zobrazí okno autorizace, vyberte možnost Windows a v případě potřeby zadejte přihlašovací heslo z OneDrive.
   

Možnost 3: Importujte obsah celé složky z OneDrive pro firmy

Pokud potřebujete v Power Query nebo Power BI vyplnit obsah ne jednoho souboru, ale celé složky najednou (například sestavami), bude postup o něco jednodušší:

1. V Průzkumníkovi klikněte pravým tlačítkem na místní synchronizovanou složku, která nás zajímá ve OneDrive, a vyberte Zobrazit na místě (Zobrazit online).
2. Do adresního řádku prohlížeče zkopírujte počáteční část adresy – až po slovo / _rozvržení:

   

3. V sešitu Excelu, kam chcete načíst data, nebo v sestavě Power BI Desktopu vyberte příkazy Získat data – ze souboru – ze složky SharePoint (Získat data — Ze souboru — Ze složky SharePoint):

   

   Poté vložte zkopírovaný fragment cesty do pole adresy a klikněte OK:

   

   Pokud se zobrazí okno autorizace, vyberte typ Účet Microsoft (Účet Microsoft), klikněte na tlačítko Přihlásit se (Přihlásit se)a poté po úspěšném přihlášení na tlačítko spojení (Připojit):

   

4. Poté jsou všechny soubory ze SharePointu vyžádány a staženy a zobrazí se okno náhledu, kam můžete bezpečně kliknout Převést data (Transformace dat).
   
5. Další úpravy seznamu všech souborů a jejich slučování probíhá již v Power Query nebo v Power BI standardním způsobem. Chcete-li zúžit okruh hledání pouze na složku, kterou potřebujeme, můžete použít filtr podle sloupce Cesta složky (1) a poté rozbalte celý obsah nalezených souborů pomocí tlačítka ve sloupci Obsah (2):

   

• Sestavení tabulek z různých souborů pomocí Power Query
• Co je Power Query, Power Pivot, Power BI a jak vám mohou pomoci
• Sběr dat ze všech listů knihy do jedné tabulky