Импорт "плавающего" фрагмента в Power Query

Иногда бывают ситуации, когда заранее неизвестно сколько именно и каких строк нужно импортировать из исходных данных. Допустим, мы должны загрузить в Power Query данные из текстового файла, что, на первый взгляд, не представляет большой проблемы. Сложность в том, что файл регулярно обновляется, и завтра в нем может быть другое количество строк с данными, шапка из трех, а не двух строк и т.д.:

Виды исходных данных

То есть мы заранее не можем с определенностью сказать, начиная с какой строки и сколько именно строк нужно импортировать. А это проблема, т.к. эти параметры жестко прописываются в М-коде запроса. И если сделать запрос по первому файлу (импорт 5 строк начиная с 4-ой), то он уже не будет правильно работать со вторым.

Было бы здорово, если бы наш запрос мог сам определять начало и конец «плавающего» текстового блока для импорта.

В основе решения, которое я хочу предложить, лежит идея, что в наших данных есть некие ключевые слова или значения, которые можно использовать как маркеры (признаки) начала и конца нужного нам блока данных. В нашем примере началом станет строка, начинающаяся со слова Артикул, а концом – строка со словом Итого. Такую проверку строк легко реализовать в Power Query с помощью условного столбца – аналога функции ЕСЛИ (IF) в Microsoft Excel.

Давайте посмотрим, как это сделать.

Сначала загрузим содержимое нашего текстового файла в Power Query стандартным способом – через команду Данные – Получить данные – Из файла – Из текстового / CSV-файла (Data – Get Data – From file – From text/CSV file). Если Power Query у вас установлена как отдельная надстройка, то соответствующие команды будут на вкладке Power Query:

Импортируем текстовый файл

Как всегда, при импорте можно выбрать символ-разделитель столбцов (в нашем случае это табуляция), а после импорта можно убрать автоматически добавленный шаг Измененный тип (Changed Type), т.к. нам еще рано назначать типы данных для столбцов:

Удаляем ненужный шаг

Теперь с помощью команды Добавление столбца - Условный столбец (Add Column - Conditional Column) добавим столбец с проверкой двух условий – на начало и конец блока - и выводом любых различных значений в каждом случае (например чисел 1 и 2). Если ни одно из условий не выполняется, то выводим null:

Условный столбец

После нажатия на ОК получим такую картину:

Добавили условный столбец

Теперь идем на вкладку Преобразование и выбираем команду Заполнить – Вниз (Transform – Fill – Down) – наши единички и двойки протянутся вниз по столбцу:

Заполняем вниз

Ну, а дальше, как легко догадаться, можно просто отфильтровать в условном столбце единицы – и вот наш желанный кусок данных:

Плавающий фрагмент выделен

Останется только поднять первую строку в шапку командой Использовать первую строку в качестве заголовков на вкладке Главная (Home – Use First Row as Headers) и удалить ненужный более условный столбец, щелкнув по его заголовку правой кнопкой мыши и выбрав команду Удалить столбец (Delete Column):

Задача решена. Теперь при изменении данных в исходном текстовом файле, запрос теперь будет самостоятельно определять начало и конец "плавающего" фрагмента нужных нам данных и импортировать каждый раз правильное количество строк. Само-собой, подобный подход работает и в случае импорта XLSX, а не TXT-файлов, а также при импорте сразу всех файлов из папки командой Данные - Получить данные - Из файла - Из папки (Data - Get Data - From file - From folder).

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






05.07.2018 11:37:14
Гениально, как всегда!
05.07.2018 15:00:22
Просто и элегантно.

Для более сложных операций, когда и строки разные и колонки убегают, описан способ на английском посложнее здесь.
Там есть и файлы примеры как источник данных и конечный получившийся вариант, но в PowerBI. Шаги легко естественно повторяются в надстройке PQ в Excel.
08.07.2018 00:28:43
Отличная статья! Спасибо, Василий :)
09.07.2018 17:22:35
Николай, большое спасибо!
Замечательный приём. Вы ускорили мне работу минут на 30 каждодневно  :)
10.07.2018 12:29:44
Спасибо. Очень элегантное решение. Этот прием можно использовать и для вытаскивания таблиц из Екселя, если например из Заявки. И этот прием работает в Power BI. Можно взять один файл как образец и этот запрос завернуть в функцию. И потом использовать в запросе из папки путем добавления пользовательского столбца.
07.12.2018 04:36:08
Спасибо за пример!
Наверх