Перейти к содержимому

Глава 7. Импорт больших текстовых файлов в Power Query

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

Одной из самых больших проблем для профессионалов Excel является импорт и очистка неструктурированных текстовых файлов. В них зачастую:

  • отсутствуют символы-разделители,
  • в разных строках поля разделены различным количеством пробелов,
  • присутствуют непечатаемые символы,
  • повторяются строки заголовка.

Power Query автоматически решает большинство из этих проблем. Создайте новую книгу Excel, и новый запрос: Данные –> Из текстового/CSV-файла. Загрузите файл с примерами GL JanMar.txt. В окне предварительного просмотра нажмите Изменить. Power Query помещает данные в один столбец:

Ris. 7.1. Import nestrukturirovannogo tekstovogo fajla

Рис. 7.1. Импорт неструктурированного текстового файла; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в формате архива

Поскольку файл не содержит разделителей, Power Query не сделал никаких предположений о данных. Он предоставил вам возможность обработать данные вручную. На первом этапе основная цель – как можно быстрее представить данные в виде подобия столбцов. Верхние 10 строк, похоже, лишние, а вот 11-я строка напоминает заголовки. Главная –> Удалить строки –> Удаление верхних строк –> 10.

Далее следует избавиться от пробелов. В Excel это стандартная практика при обработке текста. Например, функция СЖПРОБЕЛЫ() удаляет все начальные, конечные и повторяющиеся пробелы, а ПЕЧСИМВ() – непечатные символы. Power Query также умеет это делать. Щелкните правой кнопкой мыши Column1 –> Преобразование –> Очистить. А затем Column1 –> Преобразование –> Усечь. Усечение Power Query работает немного иначе функции СЖПРОБЕЛЫ() в Excel. Усечение удаляет только начальные и конечные пробелы, оставляя внутренние пробелы без изменений.

Разделение столбцов

Следующий шаг – разделение столбцов. Поскольку дата содержит 10 символов, можно попробовать оставить чуть больше, например, 12 для первого столбца. Главная –> Разделить столбец –> По количеству символов –> 12. Обратите внимание на настройку переключателя Разделение:

Ris. 7.2. Vydelenie pervogo stolbtsa

Рис. 7.2. Выделение первого столбца

Повторно усеките два новых столбца:

Ris. 7.3. Promezhutochnyj vid zaprosa

Рис. 7.3. Промежуточный вид запроса

Подберите количество символов для выделения столбца Tran Amount, усеките столбцы после разделения, и т.д. У вас получится приблизительно так:

Ris. 7.4. Vydeleno chetyre stolbtsa

Рис. 7.4. Выделено четыре столбца

Для разделения последнего столбца используйте иной трюк: Главная –> Разделить столбец –> По разделителю –> ˽˽ (два пробела). Усеките два образовавшихся столбца. Обратите внимание, каждый раз, когда вы разделяли столбцы, Power Query автоматически добавлял еще один шаг – изменял тип нового столбца на текст. Это лишнее действие, поэтому удалите все шаги Измененный тип. Далее Главная –> Использовать первую строку в качестве заголовков. С этапом разбиения месива данных на столбцы вы справились:

Ris. 7.5. Nestrukturirovannye dannye razbity na stolbtsy

Рис. 7.5. Неструктурированные данные разбиты на столбцы

Удаление мусора

Если вы прокрутите вниз, то обнаружите, что в данных много строк мусора:

Ris. 7.6. Stroki zagolovka vtoroj stranitsy smeshannye s dannymi

Рис. 7.6. Строки заголовка второй страницы, смешанные с данными

Щелкните правой кнопкой мыши столбец Tran Date –> Тип изменения –> Используя локаль –> Дата –> Английский (США). Подробнее см. Глава 2. Изменение настроек Power Query, действующих по умолчанию. Появится куча ошибок:

Ris. 7.7. CHast yacheek v stolbtse Tran Date soderzhat oshibki ili znachenie Null

Рис. 7.7. Часть ячеек в столбце Tran Date содержат ошибки или значение null

В отличие от многих других программ, ошибки в Power Query очень функциональны. Их можно контролировать, на них можно реагировать. Изучив ошибки на рис. 7.7, вы увидите, что они появились только в строках, которые являются мусором. Значение null также сигнализируют, что эти строки не нужны. Выделите столбец Tran Date, кликните на нем правой кнопкой мыши, выберите Удалить ошибки. Отфильтруйте столбец Tran Date –> снимите флажок null.

На данный момент всё еще осталось несколько нерелевантных строк, чтобы найти их, сначала отсортируйте Tran Date по возрастанию (рис. 7.8), а затем по убыванию, и снимите флажки с ненужных строк.

Ris. 7.8. Neskolko nerelevantnyh strok

Рис. 7.8. Несколько нерелевантных строк

Щелкните правой кнопкой мыши столбец Tran Date –> Переименовать –> Date. Щелкните правой кнопкой мыши столбец сумма Tran Amount –> Переименовать –> Amount. Щелкните правой кнопкой мыши столбец Amount –> Тип изменения –> Используя локаль –> Десятичное число –> Английский (США). Отфильтруйте столбец Amount –> снимите флажок null. Все строки мусора удалены.

Присмотревшись к данным в двух последних столбцах, вы понимаете, что разбиение на столбцы было выполнено неверно:

Ris. 7.9. Dva poslednih stolbtsa razdeleny neverno

Рис. 7.9. Два последних столбца разделены неверно

Объедините столбцы: выделите столбец Reference Information, удерживая нажатой клавишу Ctrl, выделите столбец Vendor Name. Важно помнить, что порядок выделения столбцов определяет, какой из них будет первым при объединении. Пройдите по меню Преобразование –> Объединить столбцы –> Разделитель –> Пробел. Снова разделите столбцы, используя разделитель дефис. Дайте разделенным столбцам разумные названия: Category и Vendor.

Чистовая обработка

Возможно в названии некоторых поставщиков (Vendor), используется дефис, поэтому в окне Разделить столбец по разделителю установите переключатель в положение Самый левый разделитель:

Ris. 7.10. Nastrojka razdeleniya stolbtsa

Рис. 7.10. Настройка разделения столбца

Помните, что при настройке этой операции, вы не ограничены разделителями, состоящими из одного символа. Если требуется, вы можете ввести в это поле хоть целое слово.

Возможно, в столбце Vendor остались сдвоенные пробелы. Вы не можете использовать усечение, так как оно не работает между словами в столбце. Щелкните правой кнопкой мыши Vendor –> Замена значений –> Значение для поиска – два пробела подряд –> Заменить на – один пробел –> Ok. Если вы подозреваете, что два пробела подряд могут встречаться несколько раз, повторите шаг с заменой. Дайте запросу говорящее имя, например, Transactions.

Цель достигнута – у вас чистый набор данных (рис. 7.11), который можно загрузить в таблицу: Главная –> Закрыть и загрузить.

Ris. 7.11. Ochishhennye dannye

Рис. 7.11. Очищенные данные

Данные загрузятся на лист Excel и будут отформатированы как Таблица:

Ris. 7.12. Dannye zagruzheny v Tablitsu

Рис. 7.12. Данные загружены в Таблицу

Проверка импорта

Чтобы проверить качество данных, щелкните в любой ячейке Таблицы, Вставить –> Сводная таблица –> На существующий лист –> Диапазон G2. Настройте сводную таблицу:

Ris. 7.13. Svodnaya tablitsa dlya proverki kachestva dannyh

Рис. 7.13. Сводная таблица для проверки качества данных

О качестве данных говорят следующие признаки:

  • Даты в строках, сгруппированы по месяцам (если бы хотя бы одно значение в столбце не было датой, группировка не выполнилась)
  • Три поставщика в строках (а не их разнобой)
  • Три категории по столбцам
  • Суммы в ячейках (если бы хотя бы одно значение не было числом, здесь отражалось бы количество ячеек)

Автоматическая обработка подобных файлов

Всё, что было сделано до сих пор, было возможно выполнить и без Power Query. Преимущество описанной методики вы ощутите, когда через квартал к вам поступит новый текстовый файл. В мире Excel это означает еще один утомительный час импорта, очистки и форматирования. В мире Power Query вам нужно выполнить всего лишь несколько простых операций:

  • Щелкните правой кнопкой мыши запрос Transactions –> Изменить
  • Перейдите к первому шагу и щелкните значок шестеренки
  • Измените имя файла GL Apr-Jun.txt
  • Главная –> Закрыть и загрузить

Ris. 7.14. Svodnaya tablitsa na dannyh vtorogo kvartala

Рис. 7.14. Сводная таблица на данных второго квартала

Появились новые поставщики, новые транзакции и новые даты. Если вам нужны данные, как за первый, так и за второй кварталы, воспользуйтесь импортом всех файлов из папки, как описано в главе 4.

3 комментария для “Глава 7. Импорт больших текстовых файлов в Power Query”

  1. Здравствуйте.
    Спасибо большое за ваш блог и за эту книгу!
    Я внимательно всё читаю, разбираю примеры и заметил одну странность, которую так и не смог объяснить. В этой главе, если в рассмотренном примере сначала отфильтровать пустые строки, то пропадёт большая часть строк (причём не пустых).
    А в последней строке появится ошибка, которую невозможно будет удалить!
    Подскажите пожалуйста, почему если в начале мы удаляем ошибки, а потом фильтруем пустые строки, то всё в порядке, а если в начале фильтруем пустые строки, то теряем часть данных и получаем неустранимую ошибку? (прилагаю скриншот к вопросу)

    Вложение

  2. Сергей Багузин

    VLAD, я тоже сталкивался с такой особенностью (не в этом примере). Не понимал в чем проблема. Задал вопрос на форуме. Максим Зеленский пояснил. Порядок важен, потому что перед этим идет шаг определения типа. На этом шаге возникает ошибка из-за того, что данные (слова "Tran Amount") не распознались как дата. Если не избавиться от ошибки, то при включении фильтра по null происходит явное вычисление значений столбца для сравнения с null. Оно длится до первой ошибки, далее операция прерывается, поэтому всё, что после первой ошибки, не показывается. Удалить ее не получается, потому что это такая «вложенная» ошибка. Обрабатывать ошибки желательно сразу после их возникновения.

  3. Спасибо большое за ответ!
    Очень интересная информация.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *