Імпарт дадзеных з OneDrive і SharePoint у Power Query / BI

Калі вы ці ваша кампанія захоўваеце даныя ў воблаку OneDrive або на партале кампаніі SharePoint, непасрэднае падключэнне да іх з дапамогай Power Query у Excel або з Power BI можа быць на здзіўленне складанай задачай.

Калі аднойчы я сутыкнуўся з падобнай праблемай, я са здзіўленнем выявіў, што ніякіх «легальных» спосабаў яе вырашэння не існуе. У спісе даступных крыніц дадзеных у Excel і нават у Power BI (дзе набор канектараў традыцыйна шырэй) чамусьці адсутнічае магчымасць падлучэння да файлаў і тэчак OneDrive.

Так што ўсе прапанаваныя ніжэй варыянты ў той ці іншай ступені з'яўляюцца «мыліцамі», якія патрабуюць невялікі, але ручной «дапрацоўкі напільнікам». Але ў гэтых мыліц ёсць вялікі плюс - яны працуюць 🙂

У чым праблема?

Кароткае ўвядзенне для тых, хто апошнія 20 гадоў правёў у коме не ў тэму.

OneDrive - гэта воблачны сэрвіс захоўвання дадзеных ад Microsoft, які выпускаецца ў некалькіх варыянтах:

• OneDrive Personal – для звычайных (не карпаратыўных) карыстальнікаў. Яны даюць вам 5 ГБ бясплатна + дадатковае месца за невялікую штомесячную плату.
• OneDrive для бізнесу – варыянт для карпаратыўных карыстальнікаў і абанентаў Office 365 са значна большым даступным аб'ёмам (ад 1 ТБ і больш) і дадатковымі функцыямі, такімі як захоўванне версій і г.д.

Асаблівым выпадкам OneDrive for Business з'яўляецца захоўванне даных на карпаратыўным партале SharePoint - у гэтым выпадку OneDrive, па сутнасці, з'яўляецца адной з бібліятэк SharePoint'a.

Доступ да файлаў можна атрымаць альбо праз вэб-інтэрфейс (сайт https://onedrive.live.com або карпаратыўны сайт SharePoint), альбо шляхам сінхранізацыі выбраных тэчак з ПК:

Звычайна гэтыя тэчкі захоўваюцца ў профілі карыстальніка на дыску C - шлях да іх выглядае прыкладна так C: КарыстальнікіІмя карыстальнікаOneDrive). Спецыяльная праграма сочыць за актуальнасцю файлаў і сінхранізацыяй усіх змяненняў – АOneDrive джэнт (сіняе або шэрае воблака ў правым ніжнім куце экрана):

А цяпер галоўнае.

Калі нам трэба загрузіць даныя з OneDrive ў Excel (праз Power Query) або ў Power BI, то, вядома, мы можам вызначыць лакальныя файлы і тэчкі для сінхранізацыі ў якасці крыніцы звычайным спосабам праз Атрымаць дадзеныя – З файла – З кнігі / З папкі (Атрымаць даныя — з файла — з працоўнай кнігі/папкі)Але гэта не будзе прамая спасылка на воблака OneDrive.

Гэта значыць, у далейшым пры змене, напрыклад, файлаў у воблаку іншымі карыстальнікамі, мы трэба спачатку сінхранізаваць (гэта здараецца надоўга і не заўсёды зручна) і толькі затым абнавіце наш запыт Power Query або мадэль у Power BI.

Натуральна, узнікае пытанне: як імпартаваць даныя з OneDrive/SharePoint непасрэдна, каб даныя загружаліся непасрэдна з воблака?

Варыянт 1: падключыцеся да кнігі з OneDrive для бізнесу або SharePoint

1. Адкрываем кнігу ў нашым Excel - лакальную копію з сінхранізаванай папкі OneDrive як звычайны файл. Або адкрыйце сайт спачатку ў Excel Online, а затым націсніце на кнопку Адкрыйце ў Excel (Адкрыць у Excel).
2. Да Файл – Падрабязнасці (Файл — інфармацыя)
3. Скапіруйце воблачны шлях у кнігу кнопкай скапіяваць шлях (Капіраваць шлях) у назве:

   

4. У іншым файле Excel або ў Power BI, дзе вы хочаце запоўніць даныя, выберыце каманды Атрымаць дадзеныя – з Інтэрнэту (Атрымаць дадзеныя — з Інтэрнэту) і ўстаўце скапіяваны шлях у адраснае поле.
5. Выдаліць у канцы шляху ?web=1 і націсніце на кнопку OK:

   

6. У якое з'явілася акне абярыце спосаб аўтарызацыі Рахунак арганізацыі (Уліковы запіс арганізацыі) і націсніце на кнопку Увайсці (Увайсці):

   

   Увядзіце наш працоўны лагін-пароль або абярыце карпаратыўны рахунак са спісу, які з'явіцца. Калі вы ўсё зробіце правільна, то надпіс Увайсці павінна змяніцца на Увайдзіце як іншы карыстальнік (Увайдзіце пад іншым уліковым запісам).
   

7. Націсніце на кнопку сувязі (Падключыць).
   

Далей усё як пры звычайным імпарце кнігі – выбіраем патрэбныя для імпарту аркушы, разумныя табліцы і г.д.

Варыянт 2: падключыцеся да файла з OneDrive Personal

Каб падключыцца да кнігі ў асабістым (некарпаратыўным) воблаку OneDrive, падыход будзе іншым:

1. Адкрываем змесціва патрэбнай тэчкі на сайце OneDrive і знаходзім імпартаваны файл.
2. Пстрыкніце па ім правай кнопкай мышы і абярыце каманду Увядзенне (Убудавана) або абярыце файл і абярыце падобную каманду ў верхнім меню:

   

3. На панэлі, якая з'явіцца справа, націсніце кнопку Ствараць і скапіруйце згенераваны код:

   

4.  Устаўце скапіяваны код у нататнік і «скончыце з файлам»:
   
   • Выдаліце ​​ўсё, акрамя спасылкі ў двукоссі
     
   • Выдаліць блок cid=XXXXXXXXXXXX&
   • Замяняльнае слова ўстаўляць on спампаваць
   У выніку зыходны код павінен выглядаць так:

   

5. Далей усё гэтак жа, як і ў папярэднім спосабе. У іншым файле Excel або ў Power BI, дзе вы хочаце запоўніць даныя, выберыце каманды Атрымаць дадзеныя – з Інтэрнэту (Атрымаць дадзеныя — з Інтэрнэту), устаўце адрэдагаваны шлях у адраснае поле і націсніце OK.
6. Калі з'явіцца акно аўтарызацыі, абярыце опцыю Windows і пры неабходнасці ўвядзіце пароль для ўваходу з OneDrive.
   

Варыянт 3: імпартуйце змесціва ўсёй папкі з OneDrive для бізнесу

Калі вам трэба запоўніць у Power Query або Power BI змесціва не аднаго файла, а адразу цэлай тэчкі (напрыклад, са справаздачамі), то падыход будзе крыху прасцей:

1. У правадыру пстрыкніце правай кнопкай мышы на цікавую для нас лакальную сінхранізаваную тэчку ў OneDrive і абярыце Прагляд на сайце (Глядзець онлайн).
2. У адраснай радку браўзера скапіруйце пачатковую частку адраса - да слова / _layouts:

   

3. У кнізе Excel, куды вы хочаце загрузіць даныя, або ў справаздачы Power BI Desktop выберыце каманды Атрымаць даныя – з файла – з папкі SharePoint (Атрымаць дадзеныя — З файла — З папкі SharePoint):

   

   Затым устаўце скапіяваны фрагмент шляху ў адраснае поле і націсніце OK:

   

   Калі з'явіцца акно аўтарызацыі, абярыце тып кошт Microsoft (Уліковы запіс Microsoft), націсніце на кнопку Увайсці (Увайсці), а затым, пасля паспяховага ўваходу, на кнопку сувязі (Падключыць):

   

4. Пасля гэтага ўсе файлы з SharePoint запытваюцца і спампоўваюцца і з'яўляецца акно папярэдняга прагляду, у якім можна смела націснуць Пераўтварэнне дадзеных (Пераўтварэнне дадзеных).
   
5. Далейшае рэдагаванне спісу ўсіх файлаў і іх аб'яднанне адбываецца ўжо ў Power Query або ў Power BI стандартным спосабам. Каб звузіць круг пошуку толькі да патрэбнай нам тэчцы, можна скарыстацца фільтрам па слупках Шлях да тэчцы (1), а затым разгарніце ўвесь змест знойдзеных файлаў, выкарыстоўваючы кнопку ў слупку змест (2):

   

• Зборка табліц з розных файлаў з дапамогай Power Query
• Што такое Power Query, Power Pivot, Power BI і чым яны могуць вам дапамагчы
• Збор дадзеных з усіх аркушаў кнігі ў адну табліцу