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

Язык М Power Query. Выражения и let

В Интернете довольно много примеров использования языка М Power Query для решения той или иной проблемы. Если можно просто скопировать и вставить код образца в ваш запрос, всё Ok. Но что делать, если требуется правка. Для этого нужно хоть немного разбираться в основах языка и его синтаксисе. Умники отсылают к официальной спецификации языка, но, поверьте, это не то, что нужно начинающим. Так что в этой и последующих заметках попробую объяснить азы доступным языком.[1]

Следующая заметка

Если вы впервые сталкиваетесь с Power Query, рекомендую начать с Марк Мур. Power Query.

Самая простая запись

Откройте имеющийся или пустой запрос в расширенном редакторе, и вы увидите выражение let. На самом деле, вы видели let так часто, что могли подумать, что для языка M это обязательно.  Совсем нет! Допускаются и более простые выражения.

Рис. 1. Самая простая запись на языке М

Подробнее »Язык М Power Query. Выражения и let

Николай Павлов. Скульптор данных в Excel с Power Query

Power Query – мощный инструмент для работы с данными в Microsoft Excel. В версии Excel 2010 года Power Query позиционировалась как надстройка. Сейчас – это элемент стандартного интерфейса вкладки Данные. И в самой ленте Excel не найти упоминания имени Power Query. Хотя за функционалом, связанным с импортом данных это имя сохранилось. С помощью Power Query можно легко решать множество задач, для которых раньше требовались сложные формулы или макросы. В книге подробно разбираются вопросы импорта данных в Excel из внешних источников (файлов разных форматов, баз данных, интернета и т.д.) и трансформации полученных таблиц. Книга рассчитана на средних и продвинутых пользователей. Ко всем описанным в книге задачам в комплекте идут файлы-примеры, которые можно использовать в работе.

Поскольку я уже прочитал по теме две книги (Кен Пульс и Мигель Эскобар. Язык М для Power Query и Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных), в настоящей заметке я рассказал на том, что было для меня новым (или что я уже успел забыть).

Николай Павлов. Скульптор данных в Excel с Power Query. – М.: Де’Либри, 2019. – 332 с.

Подробнее »Николай Павлов. Скульптор данных в Excel с Power Query

Алексей Васильев. Числовые расчеты в Excel

Книга посвящена методам решения вычислительных задач с помощью Excel: алгебраические уравнения и системы, интерполирование и аппроксимация функциональных зависимостей, дифференцирование и интегрирование, решение дифференциальных и интегральных уравнений. В книге также описываются основные приемы работы с Excel, обсуждаются способы организации документов, анализируются методы ввода и редактирования данных, изучаются возможности применения форматов и стилей, иллюстрируются принципы использования встроенных вычислительных утилит, а также даются основы программирования на VBA. Ранее по теме я опубликовал Вильям Дж. Орвис. Excel для ученых, инженеров и студентов.

Алексей Васильев. Числовые расчеты в Excel. — СПб.: Издательство «Лань», 2022. — 600 с.

Подробнее »Алексей Васильев. Числовые расчеты в Excel

Интерполяционный полином Лагранжа в Excel

В рамках подготовки курса для бакалавров МФТИ я понял, что в моем блоге не так много заметок по использованию Excel в математике и физике. Каково же было мое удивление, когда я обнаружил, что книг по этой теме на русском языке буквально единицы. Ранее я опубликовал Вильям Дж. Орвис. Excel для ученых, инженеров и студентов. В заметке представлены три варианта нахождения интерполяционного полинома Лагранжа: таблица на листе Excel, функция VBA, функция листа Excel на основе REDUCE и LAMBDA. В заметке использованы материалы книги Алексея Васильева Числовые расчеты в Excel. Бумажная и электронная версии книги доступны на сайте издательства.

Рис. 1. Вычисление интерполяционного полинома по методу Лагранжа; график табулируемой функции (штрихованная линия) и интерполяционного полинома (сплошная линия); чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Интерполяционный полином Лагранжа в Excel

Проверка кода на наличие русских букв с помощью функции LAMBDA

В декабре 2020 года Microsoft анонсировал функцию LAMBDA, которая позволяет определять новые функции, написанные на языке формул Excel. На сегодня функция доступна только для Office 365. Функция LAMBDA поддерживает рекурсию, т.е. может вызывать сама себя. Это позволяет организовать циклы. Ранее я опубликовал заметку, демонстрирующую мощь LAMBDA. А недавно возникла практическая задача, для решения которой LAMBDA подошла, как нельзя лучше! Я готовил к публикации конспект книги Алексея Васильева Числовые расчеты в Excel. Код VBA, полученный с помощью программы распознавания текста, не работал. Я предполагал, что при обработке pdf файла некоторые символы были неверно распознаны. Например, где-то «с» английское, а где-то – русское. Для обычных формул Excel эта задача – не из простых. A LAMBDA справилась!

Рис. 1. Формула ЧРУС() на основе LAMBDA

Подробнее »Проверка кода на наличие русских букв с помощью функции LAMBDA

Решение алгебраических уравнений методом половинного деления в Excel

В рамках подготовки курса для бакалавров МФТИ я понял, что в моем блоге не так много заметок по использованию Excel в математике и физике. Каково же было мое удивление, когда я обнаружил, что книг по этой теме на русском языке буквально единицы. Ранее я опубликовал Вильям Дж. Орвис. Excel для ученых, инженеров и студентов. Представлено четыре варианта решения алгебраических уравнений методом половинного деления в Excel: итерационная таблица на листе Excel, процедура VBA, функция VBA, пользовательская функция Excel на основе LAMBDA. В настоящей заметке использованы материалы книги Алексея Васильева Числовые расчеты в Excel. Бумажная и электронная версии книги доступны на сайте издательства.

Рис. 1. Метод половинного деления: цифрами и штрихованными линиями со стрелкой показана последовательность перемещения границ интервала поиска решения

Подробнее »Решение алгебраических уравнений методом половинного деления в Excel

Новые функции Excel, основанные на LAMBDA

В декабре 2020 года Microsoft анонсировал функцию LAMBDA, которая позволяет определять пользовательские функции, написанные на языке формул Excel. Я недавно привел примеры того, как работает LAMBDA. Но Microsoft на этом не остановился, и в июле 2021 г. объявил о создании новых функций, основанных на LAMBDA:

MAP: принимает в качестве аргумента массив, и возвращает массив того же размера, обработав каждый элемент с помощью функции LAMBDA.

REDUCE: реализует цикл обработки элементов массива, применяя функцию LAMBDA к каждому значению и возвращает одно накопленное значение.

SCAN: реализует цикл обработки элементов массива, применяя функцию LAMBDA к каждому значению и возвращает массив того же размера, содержащий все промежуточные значения.

MAKEARRAY: возвращает массив с заданным числом строк и столбцов, применяя функцию LAMBDA для вычисления элементов массива.

BYCOL: возвращает массив в виде строки, один результат для каждого столбца массива.

BYROW: возвращает массив в виде столбца, один результат для каждой строки массива.

ISOMITTED: проверяет, отсутствует ли значение, и возвращает ИСТИНА, в противном случае – ЛОЖЬ.

Подробнее »Новые функции Excel, основанные на LAMBDA

Лебединая песня Microsoft Excel – функция LAMBDA

Формулы Excel используют на порядок больше пользователей, чем все программисты C, C++, C#, Java и Python в мире вместе взятые. Несмотря на свой успех, Excel как язык программирования, имеет фундаментальные недостатки: (1) формулы Excel поддерживают только скалярные значения – числа, строки и логические значения; (2) Excel (без кода VBA) не позволяет определять новые функции. Ситуация изменилась с появлением в 2019 г. динамических массивов. С их помощью обычные формулы обрабатывают диапазон ячеек, а результаты также разливаются в диапазон ячеек. В декабре 2020 года Microsoft анонсировал функцию LAMBDA, которая позволяет определять новые функции, написанные на языке формул Excel. Функция LAMBDA поддерживает рекурсию, т.е. может вызывать сама себя. Это позволяет организовать циклы. В течение 2021 г. постепенно у всех пользователей Excel после плановых обновлений Office 365 появилась функция LAMBDA.

Рис. 1. Механизм создания пользовательских функций Excel без кода VBA

Подробнее »Лебединая песня Microsoft Excel – функция LAMBDA

Заключительный проект в Power Query: объединяем все вместе

Это фрагмент книги Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных.

Предыдущий раздел                   К содержанию

Примите поздравления. Вы приступаете к последней главе. Пришло время подтвердить ваши навыки по обработке данных и проверить возможности в заключительном проекте.

Отчет о доходах компании Wide World Importers

Вы – специалист по данным в компании Wide World Importers. После атаки хакеров вас попросили восстановить отчет о доходах компании на основе старых данных. Ваш предшественник вел отчеты в трех файлах Excel за 2015-2017 годы. В каждом из них содержалось 12 листов – по одному на каждый месяц. А выручка 2018 года была экспортирована в формате csv. Нужно импортировать данные с 36 листов, объединить их с данными 2018 года и создать единый отчет.

Загрузите папку C14E01 и целевую таблицу в файле C14E01 — Goal.xlsx. Изучите файлы 2015.xlsx, 2016.xlsx и 2017.xlsx. Каждый лист содержит сводную таблицу формата 2*3:

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

Подробнее »Заключительный проект в Power Query: объединяем все вместе

Основы текстовой аналитики в Power Query

Это фрагмент книги Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных.

Предыдущий раздел                   К содержанию                 Следующий раздел

Текстовые столбцы позволяют извлекать дополнительный контекст. Описанные ниже методы полезны при анализе отзывов клиентов, комментариев или социальных сетей.

Поиск ключевых слов в текстовых столбцах

В следующем упражнении мы будем искать ключевые слова в сообщениях, которые Microsoft Press использует в постах на своей официальной странице в Facebook. Допустим, вам нужно оценить, какие из тем наиболее популярны в обсуждениях на Facebook: Microsoft Excel, Visual Studio, Azure, Windows.

Загрузите файл C11E01.xlsx и сохранить его в папке C:\Data\C11\. Откройте новую книгу Excel. Пройдите Данные –> Получить данные –> Из файла –> Из книги. Выберите файл C11E01.xslx и щелкните Импорт. В окне Навигатор выберите Sheet1 и щелкните Преобразовать данные. Обратите внимание, что посты Microsoft Press Facebook сохраняются в столбце Message. Ваша задача – выполнить поиск ключевых слов в этом столбце.

Рис. 1. Сообщения Microsoft Press на Facebook в редакторе Power Query; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Основы текстовой аналитики в Power Query