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

7a. Power Query и язык М

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

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

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

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

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

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

Ris. 7.1. Import nestrukturirovannogo tekstovogo fajla

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

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

Глава 6. Обратный разбор сводной таблицы в Power Query

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

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

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

Ris. 6.1. Otchet v formate svodnyj tablitsy

Рис. 6.1. Отчет в формате сводный таблицы (см. файл UnPivot.xlsx); чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Глава 6. Обратный разбор сводной таблицы в Power Query

Глава 5. Объединение листов Excel в Power Query

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

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

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

Ris. 5.1. Dostupnye Tablitsy v okne redaktora Power Query

Рис. 5.1. Доступные Таблицы в окне редактора Power Query

Подробнее »Глава 5. Объединение листов Excel в Power Query

Глава 4. Импорт всех файлов из папки

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

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

Power Query имеет специальную опцию для импорта всех файлов из папки. Эта глава посвящена импорту двоичных файлов, таких как TXT и CSV. Следующая глава посвящена импорту данных из нескольких книг Excel. Примеры файлов для этой главы разбиты на четыре подпапки: Begin, 2008 – More, 2009, 2010. Большинство скриншотов получено в Excel 365 (июль 2019). Упоминаются отличия, замеченные в Excel 2019.

Создайте новую книгу, пройдите на вкладку Данные, кликните Получить данные –> Из файла –> Из папки:

Ris. 4.1. Menyu importa fajlov iz papki

Рис. 4.1. Меню импорта файлов из папки

Подробнее »Глава 4. Импорт всех файлов из папки

Глава 3. Простое объединение таблиц в Power Query

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

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

Перед профессионалами Excel часто встают задачи объединения данных из нескольких однотипных таблиц.[1] Power Query может делать это автоматически.

В папке примеров есть три CSV-файла: Jan 2008.csv, Feb 2008.csv и Mar 2008.csv. Начните с импорта первого файла:

  • Создайте новую книгу Excel
  • Создайте запрос Данные –> Из текстового/CSV-файла
  • Выберите файл Jancsv

Ris. 3.1. Importirovannyj CSV fajl Jan 2008.csv soderzhit odnu oshibku

Рис. 3.1. Импортированный CSV-файл Jan 2008.csv содержит одну ошибку

Подробнее »Глава 3. Простое объединение таблиц в Power Query

Глава 2. Изменение настроек Power Query, действующих по умолчанию

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

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

Иногда файл имеет расширение, которое не соответствует его содержимому. Предположим, вы работаете с набором данных CSV, который выглядит так при просмотре в блокноте:

Ris. 2.1. CSV fajl kotoryj imeet dopolnitelnuyu stroku zagolovka

Рис. 2.1. CSV-файл, который имеет дополнительную строку заголовка

Подробнее »Глава 2. Изменение настроек Power Query, действующих по умолчанию

Кен Пульс и Мигель Эскобар. Язык М для Power Query

Power Query – относительно новый компонент Excel, используемый для извлечения данных из внешних источников. Перед помещением на лист Excel в редакторе Power Query данные можно обработать. M – имя языка программирования, используемого в Power Query. Книга является полным профессиональным руководством по языку М. Если вы ранее не использовали этот компонент Excel, рекомендую начать с Марк Мур. Power Query и Power Query в помощь.

Ken Puls and Miguel Escobar. M is for (Data) Monkey.[1] The Excel’s Pro’s Definitive Guide to Power Query. – Holy Macro! Books, 2015. – 308 p.

Ken Puls Miguel Escobar. M is for Data Monkey. Oblozhka

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

Подробнее »Кен Пульс и Мигель Эскобар. Язык М для Power Query