Иногда возникают ситуации, когда заранее неизвестно, сколько и каких строк нужно импортировать из исходных данных. Предположим, нам нужно загрузить данные из текстового файла в Power Query, что, на первый взгляд, не представляет большой проблемы. Сложность в том, что файл регулярно обновляется, и завтра в нем может быть другое количество строк с данными, заголовок из трех, а не из двух строк и т.д.:
То есть мы не можем заранее с уверенностью сказать, начиная с какой строки и сколько именно строк нужно импортировать. И это проблема, потому что эти параметры жестко запрограммированы в М-коде запроса. И если сделать запрос к первому файлу (импортировать 5 строк, начиная с 4-й), то со вторым он уже не будет корректно работать.
Было бы здорово, если бы наш запрос мог сам определять начало и конец «плавающего» текстового блока для импорта.
Решение, которое я хочу предложить, основано на идее, что наши данные содержат некие ключевые слова или значения, которые можно использовать в качестве маркеров (признаков) начала и конца нужного нам блока данных. В нашем примере началом будет строка, начинающаяся со слова SKU, а конец — это строка со словом Всего. Эту проверку строк легко реализовать в Power Query с помощью условного столбца — аналога функции IF (ЕСЛИ) в Microsoft Excel.
Посмотрим, как это сделать.
Для начала загрузим содержимое нашего текстового файла в Power Query стандартным способом – через команду Данные – Получить данные – Из файла – Из текстового/CSV-файла. (Данные – Получить данные – Из файла – Из текстового/CSV-файла). Если у вас установлен Power Query как отдельная надстройка, то соответствующие команды будут на вкладке Power Query:
Как всегда, при импорте можно выбрать символ-разделитель столбцов (в нашем случае это табуляция), а после импорта — удалить автоматически добавленный шаг модифицированный тип (Измененный тип), потому что нам еще рано назначать столбцам типы данных:
Теперь с командой Добавление столбца – Условный столбец (Добавить столбец — условный столбец)добавим столбец с проверкой двух условий – в начале и конце блока – и выводом в каждом случае любых разных значений (например, чисел 1 и 2). Если ни одно из условий не выполнено, то выведите нуль:
После нажатия на OK получаем следующую картину:
Теперь перейдем на вкладку. трансформация и выбери команду Заполнить – вниз (Трансформировать – Заполнить – Вниз) – наши единички и двойки потянутся вниз по колонне:
Ну а дальше, как нетрудно догадаться, можно просто отфильтровать единицы по условному столбцу — и вот наш заветный кусочек данных:
Осталось только поднять первую строку в заголовок командой Используйте первую строку в качестве заголовков таб Главная (Главная страница – использование первой строки в качестве заголовков) и удалите ненужный более условный столбец, щелкнув правой кнопкой мыши по его заголовку и выбрав команду Удалить столбец (Удалить столбец):
Задача решена. Теперь при изменении данных в исходном текстовом файле запрос теперь будет самостоятельно определять начало и конец «плавающего» фрагмента нужных нам данных и каждый раз импортировать правильное количество строк. Конечно, этот подход работает и в случае импорта файлов XLSX, а не TXT, а также при импорте сразу всех файлов из папки командой Данные – Получить данные – Из файла – Из папки (Данные — Получить данные — Из файла — Из папки).
- Сборка таблиц из разных файлов с помощью Power Query
- Преобразование кросс-таблицы в плоскую структуру с помощью макросов и Power Query
- Создание диаграммы Ганта проекта в Power Query