Импорт "плавающего" фрагмента в 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).
Ссылки по теме
- Сборка таблиц из разных файлов с помощью Power Query
- Редизайн кросс-таблицы в плоскую с помощью макросов и Power Query
- Построение проектной диаграммы Ганта в Power Query
Для более сложных операций, когда и строки разные и колонки убегают, описан способ на английском посложнее
Там есть и файлы примеры как источник данных и конечный получившийся вариант, но в PowerBI. Шаги легко естественно повторяются в надстройке PQ в Excel.
Замечательный приём. Вы ускорили мне работу минут на 30 каждодневно
Сведения:
Value=
Type=[Type]
Почему так, и как это можно исправить? У меня Excel 365 если это поможет )