Импортирайте данни от OneDrive и SharePoint към Power Query / BI

Ако вие или вашата компания съхранявате данни в облака на OneDrive или във фирмен портал на SharePoint, директното свързване с тях чрез Power Query в Excel или от Power BI може да бъде изненадващо предизвикателство.

Когато веднъж се сблъсках с подобен проблем, с изненада открих, че няма „законни“ начини за разрешаването му. По някаква причина списъкът с налични източници на данни в Excel и дори в Power BI (където наборът от конектори традиционно е по-широк) по някаква причина не включва възможността за свързване към файлове и папки на OneDrive.

Така че всички опции, предлагани по-долу, са в една или друга степен „патерици“, които изискват малко, но ръчно „довършване с пила“. Но тези патерици имат голям плюс – работят 🙂

Какъв е проблема?

Кратко въведение за тези, които прекара последните 20 години в кома не е в темата.

OneDrive е услуга за съхранение в облак от Microsoft, която се предлага в няколко варианта:

• OneDrive Personal – за обикновени (некорпоративни) потребители. Те ви дават 5 GB безплатно + допълнително място срещу малка месечна такса.
• OneDrive за бизнес – опция за корпоративни потребители и абонати на Office 365 с много по-голям наличен обем (от 1TB или повече) и допълнителни функции като съхранение на версии и др.

Специален случай на 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.  Поставете копирания код в Notepad и „завършете с файл“:
   
   • Премахнете всичко освен връзката в кавички
     
   • Изтрийте блока cid=XXXXXXXXXXXX&
   • Заменяема дума Закрепвам on Изтегли
   В резултат на това изходният код трябва да изглежда така:

   

5. Тогава всичко е същото като в предишния метод. В друг файл на Excel или в Power BI, където искате да попълните данните, изберете командите Вземете данни – от интернет (Получаване на данни — от мрежата), поставете редактирания път в адресното поле и щракнете върху OK.
6. Когато се появи прозорецът за оторизация, изберете опцията Windows и, ако е необходимо, въведете паролата за влизане от OneDrive.
   

Опция 3: Импортирайте съдържанието на цяла папка от OneDrive за бизнеса

Ако трябва да попълните в Power Query или Power BI съдържанието не на един файл, а на цяла папка наведнъж (например с отчети), тогава подходът ще бъде малко по-прост:

1. В Explorer щракнете с десния бутон върху локалната синхронизирана папка, която ни интересува в OneDrive, и изберете Преглед на сайта (Преглед онлайн).
2. В адресната лента на браузъра копирайте началната част на адреса – до думата / _layouts:

   

3. В работната книга на Excel, където искате да заредите данните, или в отчета на Power BI Desktop изберете командите Получаване на данни – От файл – От папка на SharePoint (Получаване на данни — От файл — От папка на SharePoint):

   

   След това поставете копирания фрагмент от пътя в адресното поле и щракнете OK:

   

   Ако се появи прозорец за оторизация, изберете типа Майкрософт акаунт (Майкрософт акаунт), щракнете върху бутона Впиши се (Влизам), а след това, след успешно влизане, на бутона връзка (Свързване):

   

4. След това всички файлове от SharePoint се изискват и изтеглят и се появява прозорец за предварителен преглед, където можете спокойно да кликнете върху Конвертиране на данни (Трансформиране на данни).
   
5. По-нататъшното редактиране на списъка с всички файлове и тяхното обединяване се извършва вече в Power Query или в Power BI по стандартния начин. За да стесните кръга за търсене само до папката, от която се нуждаем, можете да използвате филтъра по колона Path Folder (1) и след това разгънете цялото съдържание на намерените файлове, като използвате бутона в колоната съдържание (2):

   

• Сглобяване на таблици от различни файлове с помощта на Power Query
• Какво е Power Query, Power Pivot, Power BI и как те могат да ви помогнат
• Събиране на данни от всички листове на книгата в една таблица