Імпортуйте дані з 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:

   

   Якщо з’явиться вікно авторизації, виберіть тип Аккаунт Майкрософт (Акаунт Майкрософт), натисніть на кнопку Увійти (Увійти), а потім, після успішного входу, на кнопку зв'язку (Підключити):

   

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

   

• Складання таблиць із різних файлів за допомогою Power Query
• Що таке Power Query, Power Pivot, Power BI і як вони можуть вам допомогти
• Збір даних з усіх аркушів книги в одну таблицю