Импортируйте данные из OneDrive и SharePoint в Power Query/BI.

Если вы или ваша компания храните данные в облаке OneDrive или на портале компании SharePoint, прямое подключение к ним с помощью Power Query в Excel или из Power BI может оказаться на удивление сложной задачей.

Когда я однажды столкнулся с подобной проблемой, то с удивлением обнаружил, что «законных» способов ее решения не существует. В список доступных источников данных в Excel и даже в Power BI (где набор коннекторов традиционно шире) почему-то не включена возможность подключения к файлам и папкам OneDrive.

Так что все предложенные ниже варианты являются в той или иной степени «костылями», требующими небольшой, но ручной «доводки напильником». Но у этих костылей есть большой плюс – они работают 🙂

В чем проблема?

Краткое введение для тех, кто провел последние 20 лет в коме не в теме.

OneDrive — это служба облачного хранения данных от Microsoft, которая доступна в нескольких вариантах:

• OneDrive персональный – для обычных (некорпоративных) пользователей. Они дают вам 5 ГБ бесплатно + дополнительное место за небольшую ежемесячную плату.
• OneDrive для бизнеса — опция для корпоративных пользователей и подписчиков Office 365 с гораздо большим доступным объёмом (от 1 ТБ и более) и дополнительными функциями вроде хранения версий и т. д.

Особым случаем OneDrive для бизнеса является хранение данных на корпоративном портале SharePoint — в этом сценарии OneDrive является, по сути, одной из библиотек SharePoint'а.

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

Обычно эти папки хранятся в профиле пользователя на диске С – путь к ним выглядит примерно так С: ПользователиИмя пользователяОдин диск). Специальная программа следит за актуальностью файлов и синхронизацией всех изменений – А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, где вы хотите заполнить данные, выберите команды Получить данные – Из Интернета (Получить данные — из Интернета), вставьте отредактированный путь в поле адреса и нажмите «ОК».
6. Когда появится окно авторизации, выберите опцию Windows и при необходимости введите пароль для входа в OneDrive.
   

Вариант 3. Импортируйте содержимое всей папки из OneDrive для бизнеса.

Если вам нужно заполнить в Power Query или Power BI содержимое не одного файла, а сразу целой папки (например, с отчетами), то подход будет немного проще:

1. В Проводнике кликаем правой кнопкой мыши по интересующей нас локальной синхронизированной папке в OneDrive и выбираем Посмотреть на сайте (Посмотреть онлайн).
2. В адресную строку браузера скопируйте начальную часть адреса – до слова / _макеты:

   

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 и как они могут вам помочь
• Сбор данных со всех листов книги в одну таблицу