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