Импорт данных из OneDrive и SharePoint в Power Query / BI

1336 12.06.2020 Скачать пример

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

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

Так что все предлагаемые ниже варианты - в той или иной степени "костыли", требующие небольшой, но ручной "доводки напильником". Но у этих костылей есть большой плюс - они работают :)

А в чём проблема?

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

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

• OneDrive Personal - для простых (некорпоративных) пользователей. Дают 5Гб бесплатно + дополнительное место за небольшую месячную плату.
• OneDrive for Business - вариант для корпоративных пользователей и подписчиков Office 365 с гораздо большим доступным объемом (от 1Тб и больше) и дополнительными фишками вроде хранения версий и т.п.

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

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

Обычно эти папки хранятся в профиле пользователя на диске С - путь к ним выглядит примерно как C:\Users\ИмяПользователя\OneDrive). За актуальностью файлов и синхронизацией всех изменений следит специальная программа - Агент OneDrive (синее или серое облачко в правом нижнем углу экрана):

А теперь главное.

Если нам нужно загрузить данные из OneDrive в Excel (через Power Query) или в Power BI, то мы, конечно, можем указать в качестве источника локальные синхронизируемые файлы и папки обычным образом через Получить данные - Из файла - Из книги / Из папки (Get Data - From file - From workbook / Folder), но это не будет прямой ссылкой на облако OneDrive.

То есть в будущем, при изменении, например, файлов в облаке другими пользователями, нам потребуется сначала выполнить синхронизацию (это бывает долго и не всегда удобно) и только потом обновлять наш запрос Power Query или модель в Power BI.

Естественно, возникает вопрос: как импортировать данные из OneDrive / SharePoint напрямую, чтобы загрузка данных происходила непосредственно из облака?

Вариант 1. Подключение к книге из OneDrive for Business или SharePoint

1. Открываем книгу в нашем Excel - локальную копию из синхронизированной папки OneDrive как обычный файл. Или открываем на сайт сначала в Excel Online, а потом жмем на кнопку Открыть в Excel (Open in Excel).
2. Идем в Файл - Сведения (File - Info)
3. Копируем облачный путь к книге кнопкой Копировать путь (Copy Path) в заголовке:
   
   
   
4. В другом файле Excel или в Power BI, куда нужно залить данные, выбираем команды Получить данные - Из интернета (Get Data - From web) и вставляем в поле адреса скопированный путь.
5. Удаляем в конце пути ?web=1 и жмем на ОК:
   
   
   
6. В появившемся окне выбираем способ авторизации Учетная запись в организации (Organization Account) и жмём на кнопку Вход (Log in):
   
   
   
   Вводим наш рабочий логин-пароль или выбираем корпоративную учетную запись из появившегося списка. Если все сделаете правильно, то надпись Вход должна поменяться на Войти от имени другого пользователя (Log in with other user account).
7. Жмем на кнопку Подключение (Connect).

Дальше всё как при обычном импорте книги - выбираем нужные листы, умные таблицы для импорта и т.д.

Вариант 2. Подключение к файлу из OneDrive Personal

Для подключения к книге в личном (некорпоративном) облаке OneDrive подход будет уже другим:

1. Открываем на сайте OneDrive содержимое нужной папки и находим импортируемый файл.
2. Щёлкаем по нему правой кнопкой мыши и выбираем команду Внедрение (Embed) или выделяем файл и выбираем аналогичную команду в верхнем меню:
   
   
   
3. В появившейся справа панели жмем кнопку Создать и копируем сформированный код:
   
   
   
4.  Вставляем скопированный код в Блокнот и "дорабатываем напильником":

• Убираем всё, кроме ссылки в кавычках
• Удаляем блок cid=XXXXXXXXXXXX&
• Заменяем слово embed на download

В итоге из исходного кода должно получиться такое:

1. Дальше всё, как и в предыдущем способе. В другом файле Excel или в Power BI, куда нужно залить данные, выбираем команды Получить данные - Из интернета (Get Data - From web), вставляем в поле адреса отредактированный путь и жмём ОК.
2. При появлении окна авторизации выбираем вариант Windows и, при необходимости, вводим логин-пароль от OneDrive.

Вариант 3. Импорт содержимого целой папки из OneDrive for Business

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

1. В Проводнике щёлкаем правой кнопкой мыши по интересущей нас локальной синхронизированной папке в OneDrive и выбираем Просмотреть на сайте (View online).
2. В адресной строке браузера копируем начальную часть адреса - до слова /_layouts:
   
   
   
3. В книге Excel, куда нужно загрузить данные или в отчёте Power BI Desktop выбираем команды Получить данные - Из файла - Из папки SharePoint (Get Data - From file - From SharePoint folder):
   
   
   
   Затем вставляем скопированный фрагмент пути в поле адреса и жмем ОК:
   
   
   
   Если появляется окно авторизации, то выбираем тип Учетная запись Microsoft (Microsoft Account), жмём на кнопку Вход (Log in), а потом, после успешного входа, на кнопку Подключение (Connect):
   
   
   
4. После этого происходит запрос и загрузка всех файлов из SharePoint и появляется окно предварительного просмотра, где можно смело жать на Преобразовать данные (Transform Data).
5. Дальнейшее редактирование списка всех файлов и их объединение происходит уже в Power Query или в Power BI стандартным образом. Чтобы сузить круг поиска только до нужной нам папки, можно воспользоваться фильтрацией по столбцу Folder Path (1) и затем развернуть всё содержимое обнаруженных файлов с помощью кнопки в столбце Content (2):
   
   
   

Примечание: при наличии большого количества файлов на портале SharePoint этот способ будет существенно медленее двух предыдущих.

Ссылки по теме

• Сборка таблиц из разных файлов с помощью Power Query
• Что такое Power Query, Power Pivot, Power BI и чем они могут вам помочь
• Сборка данных со всех листов книги в одну таблицу