Excel power query округление

Обновлено: 05.07.2024

В Excel часто используют округление чисел. В данной заметке рассмотрим разные типы округления, которые встречаются на практике.

Некоторые новички допускают ошибку, пытаясь произвести округление с помощью формата числа.

Формат никак не влияет на точность числа в ячейке. Поэтому не стоит надеяться на формат, когда нужно реальное округление.

Функция ОКРУГЛ в Excel

Для обычного округления числа по математическим правилам существует функция ОКРУГЛ, которая имеет следующий синтаксис:

ОКРУГЛ(число или ссылка на округляемое число; количество оставляемых знаков).

число или ссылка на округляемое число – исходное число для округления;

количество оставляемых знаков – количество знаков после запятой.

Например, формула округления в Excel до двух знаков после запятой выглядит так.

Чтобы получить формулу округления в Excel до целого числа, во втором аргументе нужно поставить 0.

Встречаются и более сложные ситуации. Как, например, округлить до тысяч (три последние цифры являются нулями)? Многие поступают так. Делят число на 1 000, округляют до целого и затем снова умножают на 1 000. Это не лучший способ.

Функция ОКРУГЛ умеет округлять не только после запятой, но и до запятой, т.е. до тысяч, миллионов и т.д. Например, чтобы число 123456789 округлить до тысяч и получить 123457000, точность указывается с минусом.

На этом формулы округления в Эксель не заканчиваются.

ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ

В Excel есть формулы округления в большую и меньшую сторону независимо от последних цифр числа. Например, расчетные цены округляют вверх, чтобы не уменьшить прибыль; возраст человека округляют вниз до целого, чтобы узнать полное количество лет. Короче, для этих целей придуманы функции ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ.

Данные функции имеют такие же параметры, как и ОКРУГЛ (ссылка на число и количество знаков до или после запятой).

ОКРУГЛТ

Довольно полезная функция ОКРУГЛТ. С ее помощью можно добиться округления не только до нужного разряда (слева или справа от запятой), но и вообще до нужной точности (кратности). К примеру, нужно рассчитать заказ изделий в штуках, но так, чтобы он был равен целому количеству ящиков. Если в ящике 6 шт, то заказываемое количество должно быть кратно шести. Или другой пример. Нужно, чтобы расчетная цена заканчивалась либо на 00, либо на 50, то есть имела кратность 50 рублей. Функция ОКРУГЛТ легко решает такие задачи.

Очень, очень правильная функция. Позволяет избежать 3-х промежуточных расчетов (разделить на кратность, округлить до целого и снова умножить на кратность, как я сам делал, пока не узнал про эту формулу).

ОКРВВЕРХ.МАТ и ОКРВНИЗ.МАТ

Округление с заданной точностью также можно производить принудительно вверх или вниз с помощью ОКРВВЕРХ.МАТ и ОКРВНИЗ.МАТ. Например, при расчете заказа, когда никак нельзя заказать меньше расчетного объема, но при этом нужно и кратность сохранить. То есть одна «лишняя» штука требует заказа дополнительно целого ящика. Аргументы функции те же.

Это были основные формулы округления в Excel. Но есть еще парочку, которые используются реже.

Функция ЦЕЛОЕ в Excel отбрасывает дробную часть и таким образом округляет вниз до ближайшего целого. Будет полезна при расчете целого количество лет между датами (возраст человека).

Функции ЧЁТН и НЕЧЁТ округляют числа до ближайшего четного или нечетного значения соответственно.

Видеоуроки ниже показываю основные приемы округления, а также некоторые трюки.

Power Query – это инструмент MS Excel, предназначенный для импорта из самых различных источников и обработки данных. Впервые появился в 2013 году и был доступен в виде специальной надстройки, которую и сейчас можно скачать с официального сайта Microsoft и установить на Excel 2010-2013. После установки и подключения на ленте Excel появится соответствующая вкладка.

Далее будем использовать привычное название Power Query.

На самом деле в Excel и раньше можно было импортировать данные. Для этого в той же вкладке Данные была и есть целая группа команд Получение внешних данных.

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

После появления Power Query в среде пользователей Excel произошло потрясение, сравнимое с появлением сводных таблиц. Это не шаг, а прыжок вперед, благодаря которому любой аналитик (и обычный пользователь Excel), имеющий дело с большими и обновляемыми данными из разных источников, может ускорить свою работу в десятки раз. Да, в десятки, если не в сотни. Ведь как раньше делался, скажем, отчет? Импортируются данные (из разных источников), очищаются, связываются вместе с помощью формул типа ВПР, затем делаются необходимые расчеты, все агрегируется с помощью сводных таблиц в краткий отчет. Периодически эти действия нужно повторять, т.к. традиционными методами (без VBA) очень трудно автоматизировать все шаги. Сегодня этому кошмару пришел конец. В Power Query достаточно один раз все настроить и далее все операции импорта, обработки и выгрузки данных повторяются нажатием одной кнопкой обновления.

Power Query работает на специальном языке программирования под названием M, с помощью которого записываются последовательные шаги обработки данных. Однако есть и пользовательский редактор с кнопками, поэтому быть программистом не обязательно. Здесь уместна аналогия с записью обычных макросов. Включили запись, произвели действия, закончили запись. В любой момент запустили выбранный макрос.

Вкратце алгоритм работы Power Query таков:

1. импорт данных из выбранных источников данных

2. обработка полученных данных

Список возможных источников довольно разнообразный: от текстовых файлов до внешних баз данных и интернета. Также можно легко присоединиться к данным внутри самого MS Excel.

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

На последнем этапе запроса обработанные данные выгружаются в указанное место либо создается только соединение (часто запросы – это только промежуточный этап обработки данных). Но об этом в другой раз.

В качестве наглядного примера рассмотрим следующую задачу. Имеются данные, которые нужно транспонировать, то есть строки сделать столбцами, а столбцы строками.

В целом это не проблема, т.к. в Excel существует минимум два способа транспонирования.

Первый и самый быстрый способ – воспользоваться Специальной вставкой, поставив галочку напротив опции транспонировать.

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

Второй способ транспонирования – воспользоваться функций ТРАНСП. Это формула массива, поэтому для ее вставки нужно вначале указать точный диапазон и ввести с помощью комбинации Ctrl + Shift + Enter.

Теперь при изменении данных в источнике автоматически обновится и транспонированный диапазон. Но здесь также есть серьезные недостатки. Во-первых, для вставки формулы ТРАНСП нужно заранее подсчитать, сколько строк и столбцов занимает диапазон, что, мягко говоря, не всегда удобно. Во-вторых, при изменении размера диапазона механизм перестает работать, т.к. транспонированный диапазон зафиксирован и не будет расширяться вслед за источником.

Итого получается, что мы не можем сделать динамическое транспонирование данных в изменяющемся диапазоне. Так да не так. С появлением Power Query задача решается быстро, без шума и пыли.

Транспонирование таблицы средствами Power Query

Первым делом нужно сделать запрос на источник данных. Нас интересуют данные из этой же книги Excel. Power Query не видит адреса обычных ячеек, а только именованные диапазоны и Таблицы Excel. Как правило, используют Таблицы Excel. Для преобразования обычного диапазона в таблицу рекомендую горячую комбинацию клавиш Ctrl + T.

Открывается окно редактирования Power Query.

Выглядит, как другая программа, но это только отдельное окно внутри Excel. Интерфейс состоит из пяти частей:

1. Инструменты редактирования – лента, на которой находятся команды Power Query.

2. Строка формул – здесь записывается код языка М для выделенного в данный момент шага обработки.

3. Запросы – скрываемая панель для навигации между запросами текущей книги.

4. Панель результата – место, где отображается результат обработки данных на этапе выделенного шага.

5. Параметры запроса – панель с названием запроса (можно изменять) и перечнем созданных шагов, которые также можно редактировать.

Выделив любой из шагов, мы увидим состояние данных на соответствующем этапе.

Название запроса лучше всего изменить на более говорящее. Довольно часто в книге используют сразу несколько запросов, поэтому в них нужно ориентироваться. Назовем «Транспонирование».

Из предыдущего рисунка видно, что мы еще ничего не сделали, а два шага уже записаны. Как так? Все просто. Первый шаг – это обращение к источнику, а второй автоматическое определение типа данных у каждого столбца. Поэтому все в порядке.

Вернемся к условию задачи. Нужно транспонировать вот эту таблицу.

Обратим внимание, что заголовки перенеслись из заголовков Таблицы Excel, которую мы использовали в качестве источника. Однако транспонирование происходит без заголовков. Поэтому, чтобы избежать потери названий столбцов, «опустим» их названия в первую строку таблицы Преобразование – Таблица – Использовать первую строку в качестве заголовков – Использовать заголовки как первую строку.

Таблица с данными получит такой вид.

Теперь можно транспонировать. Используем команду Преобразование – Транспонировать.

Таблица мгновенно изменяется.

Получим конечный результат обработки.

Задача решена. Все шаги преобразования данных записаны и видны справа.

Осталось измененные данные вернуть в Excel с помощью команды Главная – Закрыть – Закрыть и загрузить.

Если ее нажать, то результат загрузится на новый лист эксель и будет представлять из себя Таблицу Excel с названием, как у запроса. Но давайте пока зайдем в раскрывающийся список, чтобы посмотреть опции выгрузки. В раскрывающемся списке выберем Закрыть и загрузить в… Откроется следующее окно.

Если выбрать Только создать соединение, выгрузки не произойдет. Такой вариант применяют, если требуется дальнейшая обработка или использование этого запроса. Для выгрузки в Excel можно выбрать Новый лист либо указать конкретный диапазон. Если установить галочку Добавить эти сведения в модель данных, то результат запроса даже без выгрузки в Excel можно будет использовать в модели данных или Power Pivot. Этот вариант позволяет обрабатывать миллионы (миллионы!) строк, т.к. на обработку данных в памяти требуется гораздо меньше ресурсов. Оставляем все по умолчанию и жмем Загрузить. В процессе выгрузки таблица имеет серенький цвет, а когда выгрузка завершена, становится зелененькой.

Вот и все, дело сделано, мы получили транспонированную таблицу исходных данных.

Самое интересное происходит далее. Если добавить новые данные, то для повторения всех действий достаточно обновить запрос через правую кнопку в панели запросов (см. чуть ниже), либо во вкладке Данные – Подключения – Обновить все.

Добавим в исходную таблицу данные о продажах во втором квартале.

А теперь обновим запрос.

Это просто праздник какой-то! (с).

Обратим внимание, что справа в окне Excel появляется панель для управления существующими запросами.

Их может быть много, но у нас только один. Сразу под названием видно, сколько загружено строк. Здесь же указываются ошибки, если они есть. Это важно для контроля. Если подвести курсор мыши к названию, то откроется окно с кратким описанием запроса и командами управления снизу.

Можно вновь войти в редактирование запроса, удалить его и т.д. Эти же и некоторые другие команды появятся в контекстном меню после кликания по названию запроса правой кнопкой мыши.

Перечислим наиболее часто используемые среди них.

Изменить – команда открытия окна редактирования. Эквивалентно двойному нажатию левой кнопки мыши по самому запросу.

Обновить – обновление выбранного запроса (если нужно обновить только один запрос, а не все).

Загрузить в… – изменение места загрузки (в таблицу, модель или создания только соединения)

Дублировать – сделать копию выбранного запроса.

Другие команды не менее важны, но их рассмотрим в другой раз.

Итак, мы узнали, что такое Power Query. На примере транспонирования данных увидели, насколько он облегчает и ускоряет работу в Excel.

Power Query умеет подключаться к разным источникам. Далее рассмотрим, как получить данные из книги Excel.

Таблицы Excel

Лучше всего данные хранить в таблице Excel, это самый удобный и распространенный источник для Power Query. На ленте даже есть специальная кнопка.

Чтобы загрузить таблицу в редактор Power Query, достаточно выделить любую ее ячейку и нажать Данные → Получить и преобразовать данные → Из таблицы/диапазона.

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

Если то же самое проделать с обычным диапазоном, то Excel вначале преобразует диапазон в таблицу Excel, а потом запустит Power Query.

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

Данные находятся в Power Query. Новые значения, внесенные в исходную таблицу, автоматически попадут в запрос после его обновления. Далее в редакторе Power Query делают обработку данных и выгружают либо в виде таблицы Excel, либо оставляют в памяти Excel в виде подключения.

Именованный диапазон Excel

Источником для Power Query может быть не только таблица Excel. Например, вы получили красивый отформатированный отчет и не хотите вносить в него изменения. Тогда нужно использовать именованный диапазон. Самый простой способ создать именованный диапазон – это выделить область на листе и ввести название в поле Имя.

Либо выполнить команду Формулы → Определенные имена → Присвоить имя. В Excel будет создан новый объект, к которому можно обращаться, например, в формулах. Диапазон виден в Диспетчере имен.

Здесь перечислены все именованные диапазоны, формулы и таблицы. Среди них есть и только что созданный Отчет.

Теперь можно стать на любую ячейку внутри именованного диапазона (или выбрать его из выпадающего списка в поле Имя) и вызвать ту же команду: Данные → Получить и преобразовать данные → Из таблицы/диапазона. Произойдет загрузка данных в Power Query.

Такой способ позволяет «не портить» исходные данные. Но у него есть и очевидный недостаток: новые строки, которые выйдут за пределы именованного диапазона, не попадут в запрос.

Динамический именованный диапазон Excel

Решить данную проблему можно, создав динамический именованный диапазон. Это такой диапазон, который задается формулой и автоматически расширяется до последней заполненной ячейки.

Внести статичное имя в поле Имя на этот раз не получится. Поэтому заходим в Формулы → Определенные имена → Задать имя (или нажимаем Создать в Диспетчере имен), указываем название будущего динамического диапазона ДинамОтчет и внизу вместо ссылки записываем формулу:

Ко всем ссылкам этой формулы Excel еще автоматически добавит название листа.

Смысл формулы следующий. Верхняя левая ячейка диапазона фиксируется ($A$2), а правая нижняя определяется формулой, которая возвращает адрес последней заполненной строки в столбце B.

Но не все так просто. Excel видит это имя лишь как формулу, а не диапазон. Как же его увидит Power Query? Делаем ход конем.

Создаем пустой запрос Power Query Данные → Получить и преобразовать данные → Получить данные → Из других источников → Пустой запрос. Открывается пустой запрос, где в строке формул нужно ввести:

После ввода формулы (нажатием Enter) Power Query обратится к текущей книге и выведет все объекты, среди которых есть и наш динамический диапазон ДинамОтчет.

Название запроса не подхватывается, поэтому придется изменить самостоятельно.

Чтобы извлечь содержимое объекта, в этой же строке правой кнопкой мыши кликаем по Table, далее выбираем Детализация.

Power Query разворачивает таблицу и даже делает некоторые шаги обработки: повышает заголовки и задает нужный формат для столбцов.

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

Вот такие приемы импорта данных в Power Query из книги Excel. Самый распространенный из них – это импорт из таблицы Excel. Тем не менее, в случае необходимости можно прибегнуть к альтернативам, создав именованный или динамический именованный диапазон.

Консолидация данных из разных таблиц Excel

Одна из насущных задач, с которыми сталкиваются пользователи, – консолидация данных. Под консолидацией понимается объединение нескольких таблиц в одну. До появления Power Query это была довольно трудоемкая операция, особенно, если процесс требовал автоматизации. Хотя в эксель есть специальная команда Данные → Работа с данными → Консолидация, пользоваться ей не удобно. Мне, по крайней мере. Появление Power Query в корне изменило представление о том, как нужно объединять таблицы.

Рассмотрим пример. В некоторый файл каждый месяц вносится отчет о продажах в формате таблицы Excel. Каждая таблица при этом имеет соответствующее название: Январь_2018, Февраль_2018 и т.д. Необходимо объединить все таблицы книги в одну. Как бы скопировать и вставить одну под другой, создав при этом дополнительный столбец, указывающий, к какой таблице принадлежит конкретная строка. Задача не одноразовая, а с заделом на будущее, поэтому нужно предусмотреть появление в этом файле новых таблиц.

Процесс начинается с запуска пустого запроса: Данные → Получить и преобразовать данные → Создать запрос → Из других источников → Пустой запрос

Затем в строке формул вводим знакомую команду

Power Query показывает все таблицы в текущей книге.

Их нужно развернуть кнопкой с двумя стрелками в названии поля Content (на скриншоте ниже выделено красным кружком).

Если есть лишние столбцы, то их можно не выводить, сняв соответствующую галку. Также лучше убрать галку напротив опции Использовать исходное имя столбца как префикс. Нажимаем Ok.

Все таблицы находятся на одном листе, а рядом колонка с названием источника, откуда взята каждая строка.

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

Визуально мы наблюдаем и месяц, и год. Но Power Query такое название воспринимает, как текст. Поэтому делаем следующее.

Удалим нижнее подчеркивание. Правой кнопкой мыши по названию столбца Name → Замена значений.

В следующем окне настроек указываем, что меняем _ на пусто, то есть в нижнем поле ничего не указываем.

Подчеркивание удаляется из названия.

Поиск и замена здесь работает так же, как и в обычном Excel.

Далее запускаем команду Преобразование → Столбец «Дата и время» → Дата → Выполнить анализ.

Power Query распознает дату и меняет формат колонки. Мы также переименовываем столбец на Период.

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

Но что-то пошло не так. Во-первых, внизу таблицы пустая строка; во-вторых, при выгрузке произошла одна ошибка. Обновим запрос (справа от названия запроса значок обновления).

Что-то еще больше пошло не так. Даты исчезли, снизу таблицы добавились новые строки, а количество ошибок уже 19. Спокойствие, только спокойствие! Дело вот в чем.

Помните, на первом шаге мы получили все таблицы из файла? Так ведь и выгруженная таблица – это тоже таблица! Получается, Power Query взял 3 исходных таблицы, обработал, выгрузил на лист Excel и на следующем круге видит уже 4 таблицы!

При повторном обновлении запрос захватывает их все, а т.к. таблица выхода имеет другую структуру, то возникают ошибки.

Короче, из запроса нужно исключить таблицу, которая получается на выходе (Запрос1). Есть разные подходы, самый простой – это добавить шаг фильтрации. Выделяем в правой панели первый шаг Источник, открываем фильтр в колонке с названиями, снимаем галку с таблицы Запрос1 → Ok.

Снова выгружаем таблицу в Excel и на этот раз все в порядке.

Сделаем с помощью сводной таблицы маленький отчет по месяцам.

Прошло время, и в файл добавили новую таблицу с продажами за апрель.

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

А вот, как это выглядит при использовании Power Query.

Достаточно два раза нажать кнопку Обновить все (первый раз – для обновления запроса, второй – для сводной таблицы).

На добавление в отчет новых данных вместе с их обработкой потребовалось несколько секунд.

Вот за это мы так любим Power Query.

» Функции округления в DAX: INT, TRUNC, ROUND, ROUNDDOWN, ROUNDUP, MROUND, FLOOR, CEILING, ISO.CEILING в Power BI и Power Pivot

Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы разберем обширную группу функций в DAX, производящих округление чисел. А именно, функции INT, TRUNC, ROUND, ROUNDDOWN, ROUNDUP, MROUND, FLOOR, CEILING, ISO.CEILING в Power BI и Power Pivot.

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

до конца распродажи осталось:

DAX функция INT в Power BI и Power Pivot

Для демонстрации округления дробных чисел функцией INT, рассмотрим примеры формул на основе исходной таблицы в Power BI Desktop, содержащей разные положительные и отрицательные числовые значения:

Создадим в этой таблице вычисляемый столбец на основе DAX функции INT по следующей формуле:

Результатом выполнения этой формулы будет создан столбец с округленными исходными числами:

DAX функция TRUNC в Power BI и Power Pivot

Рассмотрим примеры формул с участием DAX функции TRUNC на основе все той же исходной таблицы в Power BI:

Как мы видим из примера, при значении точности округления 1, отсекается дробная часть и от нее остается 1 цифра, если ТО равно 0, то отсекается полностью вся дробь, и если отрицательная, числа по правую сторону от запятой округляются до нуля.

DAX функция ROUND в Power BI и Power Pivot

Рассмотрим примеры формул с участием DAX функции ROUND на основе все той же исходной таблицы в Power BI:

DAX функция ROUNDDOWN в Power BI и Power Pivot

Также, ROUNDDOWN похожа на функцию INT, так как INT, также, округляет число вниз, но всегда только до целого числа, в то время как, ROUNDDOWN может округлять не только до целого числа, но и до дробного, управляя точностью округления.

Рассмотрим примеры формул с участием DAX функции ROUNDDOWN на основе все той же исходной таблицы в Power BI:

DAX функция ROUNDUP в Power BI и Power Pivot

Рассмотрим примеры формул с участием DAX функции ROUNDUP на основе все той же исходной таблицы в Power BI:

DAX функция MROUND в Power BI и Power Pivot

Рассмотрим примеры формул с участием DAX функции MROUND на основе все той же исходной таблицы в Power BI, но отрицательные и положительные значения которой, мы разделим на две отдельные таблицы, так как исходные числа и кратные числа в параметрах MROUND должны быть с одним знаком:

Как мы можем наблюдать из примеров в Power BI, действительно, округление происходит кратно значению, указанному во втором параметре MROUND (например, -233,786 округляется кратно -5, как итог = -235)

DAX функция FLOOR в Power BI и Power Pivot

Рассмотрим примеры формул с участием DAX функции FLOOR на основе все той же исходной таблицы в Power BI, но, как и в случае с примером функции MROUND, отрицательные и положительные значения исходной таблицы мы разделим на две отдельные таблицы:

И примеры с отрицательными исходными значениями:

DAX функция CEILING в Power BI и Power Pivot

Рассмотрим примеры формул с участием DAX функции CEILING на основе все той же исходной таблицы в Power BI, но, отрицательные и положительные значения исходной таблицы мы разделим на две отдельные таблицы:

Как мы можем наблюдать из примера, действительно, в обычной ситуации CEILING округляет число в большую сторону до ближайшего целого кратно значимости второго параметра. Но, когда исходное значение отрицательное и кратное тоже отрицательное, округление происходит в обратную (меньшую) сторону.

DAX функция ISO.CEILING в Power BI и Power Pivot

Рассмотрим примеры формул с участием DAX функции ISO.CEILING на основе все той же исходной таблицы в Power BI:

На этом, с разбором функций округления в языке DAX: INT, TRUNC, ROUND, ROUNDDOWN, ROUNDUP, MROUND, FLOOR, CEILING, ISO.CEILING в Power BI и Power Pivot, все.

До конца распродажи осталось:

Пожалуйста, оцените статью:

Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.

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

Понравился материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

до конца распродажи осталось:

до конца распродажи осталось:

Что еще посмотреть / почитать?

Функции объединения таблиц в DAX: UNION, INTERSECT и EXCEPT в Power BI и Power Pivot

Таблицы итогов и топ значений в DAX: функции ROW и TOPN (для Power BI и Power Pivot)

DAX функции MAX, MAXA, MAXX и MIN, MINA, MINX (для Power BI и Power Pivot)

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

Подписывайтесь на наши социальные сети

Именно в них оперативно и каждый день Вам будут доступны наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel. )

Наша группа в VK

Мы в Инстаграме

Наш YouTube канал

Последние видео на нашем YouTube канале:

до конца распродажи осталось:

до конца распродажи осталось:

Оставляя персональные данные (email, имя, логин) в формах на страницах данного сайта "BI - это просто", Вы автоматически подтверждаете свое согласие на обработку своих персональных данных

Данный сайт "BI - это просто" при своей работе использует файлы cookie. Продолжая использовать сайт, Вы даете свое согласие на работу с этими файлами.

Справочник DAX функций для Power BI и Power Pivot

на русском языке с подробными примерами формул на практике

ищете подробное описание DAX функций для Power BI или Power Pivot на русском языке
нуждаетесь в примерах формул и их демонстрации на практике
устали разбираться с функциями самостоятельно
тратите огромное количество времени на создание формул методом "тыка"

то, справочник DAX функций для Power BI и Power Pivot - это то, что Вам нужно!

+ БОНУС (видеокурс по DAX)

Справочник DAX функций для Power BI и Power Pivot

на русском языке с подробными примерами формул на практике

+ БОНУС: [экспресс-видеокурс] Быстрый старт в языке формул DAX для Power BI и Power Pivot

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

Пользовательский интерфейс Power Query позволяет выполнять огромное число операций. Но наверняка возникнут моменты, когда вам потребуется что-то сделать, что не встроено в интерфейс. Вот мы и добрались до языка программирования Power Query: M.

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

Рис. 17.1. Интерфейс создания пользовательского столбца

Создание пользовательских столбцов

Окно создания настраиваемого столбца содержит три важные части (см. 17.1):

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

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

К сожалению, Power Query не имеет подсказок, чтобы выяснить, какие функции можно использовать. Но… в окне Настраиваемый столбец есть гиперссылка Сведения о формулах Power Query (см. рис. 17.1). Щелкнув на нее, вы попадете на страницу с подробным каталогом функций, правда, на английском языке. В отличие от Excel, но аналогично Power Pivot, функции Power Query не русифицированы.

Подводные камни формул на языке М

Power Query и Excel существенно различаются в том, как они обрабатывают входные данные.

Рис. 17.2. Различия Power Query и Excel в обработке данных

Чувствительность к регистру. Запомните, что в 99% случаев первая буква каждого слова в формуле на языке М – заглавная, а остальные – строчные. В то время как Excel не заботится, какие буквы вы используете и преобразует формулы в верхний регистр по умолчанию, Power Query просто возвращает ошибку.

База 0 против базы 1. Если бы вас спросили о номере позиции буквы x в слове Excel, вы сказали бы 2. Это логично, и так считает программа MS Excel. Но Power Query скажет, что буква x в слове Excel занимает позицию 1.

Преобразование типов данных. В Excel вы можете добавить единицу к дате, что изменит дату на один день. В Power Query, если дата отформатирована с типом Даты, необходимо использовать специальную формулу чтобы добавить к ней день. Если вы попытаетесь использовать ту же формулу для добавления дней к числу, Power Query вернет ошибку. Это означает, что перед использованием столбцов в формулах необходимо явно задавать в них тип данных.

В Excel вы можете объединить две ячейки вместе с помощью функции конкатенации &. Содержат ли ячейки текст или числа, не важно. Excel неявно преобразует их в текст, а затем объединяет вместе:

Рис. 17.3. Неявное преобразование данных в Excel: число и текст, преобразованные в текст

Создайте на основе двух первых столбцов Таблицы Excel запрос, а затем внутри Power Query создаете пользовательский столбец, используя формулу: =[Column1]&[Column2]:

Рис. 17.4. Power Query не может соединить число и текст вместе

Чтобы устранить эту проблему, необходимо сначала преобразовать тип данных Столбец1 в текст, а уже затем создать пользовательский столбец:

Рис. 17.5. Два текстовых столбца объединить можно

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

На самом деле существует два способа работы с типами данных в Power Query:

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

Функции преобразования типов данных

Существует несколько функций преобразования типов данных.

Преобразование в текст. Если вам нужно преобразовать значения в столбце в текст, можно использовать универсальную функцию Text.From(). Если же вы хотите подчеркнуть тип преобразуемых данных, также в вашем распоряжении есть: Date.ToText(), Time.ToText(), Number.ToText(). Имейте в виду, что Text.From() преобразует любой тип данных в текст, в то время как Date.ToText() не преобразует число в текст.

Даты. Данные, похожие на даты, могут поступать в формате чисел или текста. Для их преобразования есть две функции: Date.From() и Date.FromText(). Опять же, Date.From() справится с преобразованием в формат даты, как чисел, так и текста.

Время. Значения времени могут поступать как в виде чисел, так и в виде текста. Опять же, есть две функции для них: Time.From() и Time.FromText().

Длительность – это разница между двумя значениями даты/времени: Duration.From() и Duration.FromText().

Числа. Имеется универсальная функция Number.From() и несколько специальных. Для чисел из текста Number.FromText(), для десятичных чисел Decimal.From(), целых чисел Int64.From(), валюты Currency.From().

Сравнение текстовых функций Excel и Power Query

Если вы работали с текстовыми функциями Excel, то привыкли использовать их для извлечения элементов текста из данных. В Power Query текстовые функции работают иначе. Рассмотрим пять наиболее часто используемых текстовых функций Excel, и их аналоги в Power Query. Откройте файл 5 Useful Text Functions.xlsx. Каждый из примеров в этом разделе начинается с набора данных:

Рис. 17.6. Пример данных

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

Итак, что поместить данные, представленные на рис. 17.6, в Power Query, кликните на любой ячейке в диапазоне А1:В8 –> Данные –> Из таблицы/диапазоне. Подтвердите создание Таблицы с заголовком. В окне редактора Power Query переименуйте запрос pqLeft. Перейдите на вкладку Добавление столбца –> Настраиваемый столбец. Назовите новый столбец pqLeft(x,4). Введите формулу: =LEFT([Слово],4). Вроде бы, это должно сработать:

Рис. 17.7. Power Query не находит синтаксических ошибок

Однако, после нажатия Ok, появляется ошибка:

Рис. 17.8. Формула =LEFT([Слово],4) не работает

В Power Query используется иной синтаксис =Text.Start(text,num_chars). Отредактируйте формулу. В области ПРИМЕНЕННЫЕ ШАГИ кликните на шестеренку справа от строки Добавлен пользовательский столбец, и в окне Настраиваемый столбец введите формулу: =Text.Start([Слово],4). Не забывайте, что формулы в Power Query чувствительны к регистру: Text.start и TEXT.START вернут ошибку. Нажмите Ok:

Рис. 17.9. Функции ЛЕВСИМВ() в Excel соответствует Text.Start() в Power Query

Никогда не используйте имя функции Excel в качестве имени запроса Power Query. Если бы вы назвали запрос ЛЕВСИМВ, вы бы получили ошибки в формулах Excel исходной Таблицы. Имена таблиц обрабатываются перед функциями.

Рис. 17.10. Не давайте запросам имена, совпадающие с именами функций Excel; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Рис. 17.11. Соответствие текстовых функций Excel и Power Query

Первые две функции из таблицы аналогичны только что рассмотренной Text.Start(). Использование двух последних функций требует небольшой коррекции в связи с тем, что Power Query за точку начала отсчета берет ноль. Также обратите внимание на следующее различие: аргумент искомый текст является первым в функции НАЙТИ(), и – вторым в функции Text.PositionOf().

Аналог функции НАЙТИ

В файле 5 Useful Text Functions.xlsx перейдите на лист FIND. Кликните на одной из ячеек таблицы, пройдите по меню Данные –> Из таблицы/диапазона. В окне редактора Power Query переименуйте запрос pqFind. Перейдите на вкладку Добавление столбца –> Настраиваемый столбец. Назовите новый столбец pqFind(x, " o " ). Введите формулу: =Text.PositionOf([Word], " o " ). Нажмите Ok.

Рис. 17.12. Результат не вполне согласуются с Excel

Возвращаемые значения, следуют базовому правилу. В первой строке буква F идет под номером 0. Измените формулу, добавив 1: =Text.PositionOf([Word], " o " )+1.

Рис. 17.13. Есть совпадение с Excel, а вместо ошибок выводится значение ноль

Аналог функции ПСТР

В файле 5 Useful Text Functions.xlsx перейдите на лист MID. Кликните на одной из ячеек таблицы, пройдите по меню Данные –> Из таблицы/диапазона. В окне редактора Power Query переименуйте запрос pqMid. Перейдите на вкладку Добавление столбца –> Настраиваемый столбец. Назовите новый столбец pqMid(x,5,4). Введите формулу: =Text.Range([Word],5,4). Нажмите Ok. Результат не соответствует ожиданиям:

Рис. 17.14. Несколько результатов адекватно, но почему не все?

Рис. 17.15. Уже лучше, но всё еще ошибки в двух последних строках

Одна из замечательных особенностей функции Mid (ПСТР) Excel заключается в том, что вас не волнует, сколько символов осталось в текстовой строке. Если конечный параметр больше, чем количество оставшихся символов, он просто вернет все оставшиеся символы. Не таков Power Query. Вам нужно дополнить формулу проверкой: вы хотите вернуть четыре символа или меньше, до конца текстовой строки. Для этих целей подойдет функция List.Min (подробнее о ней вы узнаете из главы 20). Вместо того, чтобы пытаться встроить эту функцию в формулу столбца pqMid(x,5,4), создайте еще один пользовательский столбец с формулой =List.Min().

Рис. 17.16. В отдельном столбце определено количество оставшихся символов

Несколько слов о том, как работает формула:

Text.Length([Word])-(5-1) подсчитывает длину слова в столбце Word и вычитает начальную позицию. Вы использовали выражение (5-1), чтобы подчеркнуть, что хотели взять пятый символ, но исправили формулу для базы 0 (можно использовать и 4).
Последняя четверка в формуле – максимальное количество символов, которые вы хотите вернуть
Для того, чтобы использовать их в функции List.Min() они должны быть окружены фигурными скобками и разделены запятыми.

Теперь вы можете отредактировать формулу в столбце pqMid(x,5,4) =Text.Range([Word],5-1, List.Min())

Рис. 17.17. Всё верно, кроме последней строки

Теперь вы можете удалить вспомогательный столбце Пользовательская и загрузить запрос в Таблицу на лист Excel. А как же ошибка в последней строке. Не страшно. Потому что ошибки в Power Query будут показываться в Excel, как пустые ячейки:

Рис. 17.18. Ошибки исчезают при загрузке в Таблицу

Очень жаль, что не все функции в Power Query эквивалентны функциям в Excel. Особенно учитывая, что Power Query – это инструмент для пользователей Excel. Надеемся, что это изменится в будущих версиях. Мы хотели бы видеть новую библиотеку функций Power Query, которые позволяют переносить существующие знания из Excel в Power Query без необходимости изучать новые функции и новый синтаксис.

Читайте также: