Импорт данных из OneDrive и SharePoint в Power Query / BI
Если вы или ваша компания храните данные в облаке 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
- Открываем книгу в нашем Excel - локальную копию из синхронизированной папки OneDrive как обычный файл. Или открываем на сайт сначала в Excel Online, а потом жмем на кнопку Открыть в Excel (Open in Excel).
- Идем в Файл - Сведения (File - Info)
- Копируем облачный путь к книге кнопкой Копировать путь (Copy Path) в заголовке:
- В другом файле Excel или в Power BI, куда нужно залить данные, выбираем команды Получить данные - Из интернета (Get Data - From web) и вставляем в поле адреса скопированный путь.
- Удаляем в конце пути ?web=1 и жмем на ОК:
- В появившемся окне выбираем способ авторизации Учетная запись в организации (Organization Account) и жмём на кнопку Вход (Log in):
Вводим наш рабочий логин-пароль или выбираем корпоративную учетную запись из появившегося списка. Если все сделаете правильно, то надпись Вход должна поменяться на Войти от имени другого пользователя (Log in with other user account).
- Жмем на кнопку Подключение (Connect).
Вариант 2. Подключение к файлу из OneDrive Personal
Для подключения к книге в личном (некорпоративном) облаке OneDrive подход будет уже другим:
- Открываем на сайте OneDrive содержимое нужной папки и находим импортируемый файл.
- Щёлкаем по нему правой кнопкой мыши и выбираем команду Внедрение (Embed) или выделяем файл и выбираем аналогичную команду в верхнем меню:
- В появившейся справа панели жмем кнопку Создать и копируем сформированный код:
- Вставляем скопированный код в Блокнот и "дорабатываем напильником":
- Убираем всё, кроме ссылки в кавычках
- Удаляем блок cid=XXXXXXXXXXXX&
- Заменяем слово embed на download
В итоге из исходного кода должно получиться такое:
- Убираем всё, кроме ссылки в кавычках
- Дальше всё, как и в предыдущем способе. В другом файле Excel или в Power BI, куда нужно залить данные, выбираем команды Получить данные - Из интернета (Get Data - From web), вставляем в поле адреса отредактированный путь и жмём ОК.
- При появлении окна авторизации выбираем вариант Windows и, при необходимости, вводим логин-пароль от OneDrive.
Вариант 3. Импорт содержимого целой папки из OneDrive for Business
Если нужно залить в Power Query или Power BI содержимое не одного файла, а сразу целой папки (например, с отчетами), то подход будет чуть попроще:
- В Проводнике щёлкаем правой кнопкой мыши по интересущей нас локальной синхронизированной папке в OneDrive и выбираем Просмотреть на сайте (View online).
- В адресной строке браузера копируем начальную часть адреса - до слова /_layouts:
- В книге Excel, куда нужно загрузить данные или в отчёте Power BI Desktop выбираем команды Получить данные - Из файла - Из папки SharePoint (Get Data - From file - From SharePoint folder):
Затем вставляем скопированный фрагмент пути в поле адреса и жмем ОК:
Если появляется окно авторизации, то выбираем тип Учетная запись Microsoft (Microsoft Account), жмём на кнопку Вход (Log in), а потом, после успешного входа, на кнопку Подключение (Connect):
- После этого происходит запрос и загрузка всех файлов из SharePoint и появляется окно предварительного просмотра, где можно смело жать на Преобразовать данные (Transform Data).
- Дальнейшее редактирование списка всех файлов и их объединение происходит уже в Power Query или в Power BI стандартным образом. Чтобы сузить круг поиска только до нужной нам папки, можно воспользоваться фильтрацией по столбцу Folder Path (1) и затем развернуть всё содержимое обнаруженных файлов с помощью кнопки в столбце Content (2):
Ссылки по теме
- Сборка таблиц из разных файлов с помощью Power Query
- Что такое Power Query, Power Pivot, Power BI и чем они могут вам помочь
- Сборка данных со всех листов книги в одну таблицу
А есть способ подключения к файлу access, лежащему на SharePoint, а не к его локальной копии?
Большое спасибо за статью .
Простотрел много информации в интернете, но пока не нашёл решения
Как получать данные power query с эксель файлов, если на файлах установлен Restrict. Компания запустила data protection, теперь файлы за пределами организации невозможно посмотреть. При попытке подключится выдаёт ошибку, хотя у меня есть полномочия редактировать файл. Если файл открыт, по power query забирает данные, но вариант открывать больше 10 файлов в папке не очень автоматизирует процесс. Если есть возможность, можете посоветовать куда копать. Заранее благодарен
Есть решение?
Может существуют какие-нибудь внутрисерверные ссылки на файлы?
C:\Users\Den\Desktop\OneDrive\
и все отлично работает и обновляется.
Хотя через sharepoint тоже работает
я замучился внедрять с шарика. пробовал через ODATA.
а нужно было только чуть удалить
и почему это не написано в справках у майков...
Крайне полезная статья!
Корпоративная подписка 365, но отсутствует выбор в меню "Из папки SharePoint". Что не так делаю ?
Когда работал в OneDrive для бизнеса, пользовался первым вариантом.
Но после того, как Microsoft отключил нашей компании Office365, пришлось перейти в OneDrive Personal.
Сейчас опробовал второй вариант, он тоже работает
Для удобства сделал формулу, которая сама выполнит все шаги и на выходе у вас будет готовая формула для вставки в PQ.
1. Вставляем ссылку в A1
2. в B1 вставляем формулу
=ПСТР(K1;ПОИСК("""";A1;1)+1;ПОИСК("rive.live.com";A1;1))&"download?"&ПСТР(A1;ПОИСК("&";A1;1)+1;ДЛСТР(A1)-ПОИСК("width";A1;1)-6)
Очень классный и полезный урок, спасибо!!!
Но так как урок от 2020 года, подскажите пожалуйста что-то улучшилось в плане OnDrive Personal???
Мне нужно подключить папку с файлами расположенную на OnDrive Personal в PowerBI.
Можете пожалуйста подсказать или посоветовать в каком направлении искать, может что-то есть очевидное уже, а я пытаюсь в дебри лезть?)))
Заранее спасибо!
Подскажите, пожалуйста, по
Вариант 3. Импорт содержимого целой папки из OneDrive for Business
Есть пользователь, из расшаренной папки которого нужно загружать данные.
При подключении к его папке описанным Вами способом через Получить данные - Из файла - Из папки SharePoint с копированием адреса до слова layouts и последующим нажатием кнопки "Подключение" не отображаются находящиеся в папке файлы.
Отображается только одна строчка
и все, других файлов нет.
Если далее нажать кнопку преобразовать, то также отображается одна строчка но если в адресной строке удалить api [apiversion = 15 или изменить на apiversion =14,то список файлов начинаети отображаться но в дальнейшем при объединении файлов появляется ошибка [DataSource.Error] SharePoint: Ошибка запроса: Удаленный сервер возвратил ошибку: (500) Внутренняя ошибка сервера. (An error occurred while processing this request.)
В чем может быть причина такого поведения? такая проблема наблюдается при подключении к папкам двух пользователей, у остальных все отображается корректно.
Оба используют one drive business
заранее признателен за ответы и комментари