Power query как посчитать счетесли

SUMIF in Power Query

If you tried to find or write SUMIF in Power Query, you won’t be able to because there isn’t one! But that doesn’t mean that we can’t do a SUMIF in PowerQuery. There is something known as a “Group By” feature in Power Query which offers the same (and a lot more) functionality as the SUMIF function in Excel.

Let’s Take an Example

Here is some random Sales Data

and I would have to write a SUMIF formula (or may be create a pivot) to be able to summarize Total Sales and Total Units as per Year and Region. The result would look something like this..

Doing a SUMIF in Power Query

In Power Query the equivalent of SUMIF is the “Group By” Feature in the Transform Tab. By using this feature you can not only do a SUMIF but also other IF based aggregations like COUNTIF, MINIF, MAXIF, AVERAGEIF, DISTINCTIF

Let’s load the Sales Data in Power Query and get started

Now since I would like to summarize the data by Year and Region. I would have to extract the Year from the Dates. To do that

1. Right click on the Dates column
2. Go to the Transform Option
3. Pick the Year
4. You’ll see that the Dates have been transformed into Years

Now that we have both the fields (years and regions) we can use the Group By Feature a.k.a SUMIF

1. In the Transform Tab go to Group By
2. In the group by box, group it by Year and Region
3. Underneath you’ll have the options to create 2 new columns (calculations). For Total Sales and Total Units
   • Provide the new column a name, pick the operation (sum, count etc..) and select the column
   • We’ll have to do this twice since we need 2 columns (Total Sales and Total Units)
4. That’s it SUMIF is done. Close and Load the data in excel

The power query result that you see (in gif above) is that same that we calculated using SUMIF

Doing COUNTIF other aggregations in Power Query

If you noticed carefully, while selecting calculations in Group By, it allows you to choose between aggregations like COUNTIF, AVERAGEIF, MAXIF, MINIF and even DISTINCTIF.

This is so cool!

Using Group By to calculate Percentage % of Total

Until now all of what I have shared with you is the standard application of Group By Feature. Using the same I can also calculate % of total by using “All Rows” (which we din’t speak about)

But before I proceed I want to give you a glimpses of what I am trying to achieve. I would like to calculate % contribution of each region in the entire year for both Total Sales and Total Units

If it were excel, I would have done something like this..

Let’s start again from where we left our last query. I am going to duplicate the query (right click on the query and choose duplicate) and work further on it

1. Now that we have an All Rows (expandable) Column, let’s expand that and get the figures for Units, Sales and Regions
2. We can now simply create 2 Columns by dividing each Unit and Sales by their Totals
3. In case you would cleanse this further, you may now choose to remove the Total Columns (for units and sales)
4. And boom we have the % of Total Calculation Ready!

Wanna watch a Video Instead ?

More Power Query Tutorials

Automate repetitive data cleaning tasks using Power Query

A comprehensive course to learn Power Query to automate all your mundane and repetitive data cleaning tasks in Excel or in Power BI

Topics that I write about.

Download Smart Ebooks on
Excel and Power BI

Chandeep

Welcome to Goodly! My name is Chandeep. On this blog I actively share my learning on practical use of Excel and Power BI. There is a ton of stuff that I have written in the last few years. I am sure you'll like browsing around. Please drop me a comment, in case you are interested in my training / consulting services. Thanks for being around Chandeep

Online Courses

I teach Excel and Power BI to people around the world through my courses. If you are planning to upgrade your skills to the next level, you’ll find my courses incredibly useful.

Onsite Training & Consulting

I offer world class training interventions for companies on Excel & Power BI

I also do MIS / Data Analysis and Automation Projects using Power BI and Excel

For more info please read through my training & consulting page

If watching videos helps you learn better, h ead over to my YouTube Channel

Функции подсчета количества в DAX: COUNT, COUNTA, COUNTX, COUNTAX, COUNTBLANK, DISTINCTCOUNT и COUNTROWS (для Power BI и Power Pivot)

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы рассмотрим функции DAX из так называемой группы COUNT, отвечающей за подсчет количества значений, ячеек или строк при составлении формул в Power BI и Excel (Powerpivot).

И это функции COUNT, COUNTA, COUNTX, COUNTAX, COUNTBLANK, DISTINCTCOUNT и COUNTROWS, входящие в категорию статистических функций агрегирования DAX.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

Да, и еще один момент, до 24 июня 2022 г. у Вас имеется возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 50% (вместо 10000, всего за 5000 руб.)

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

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 50% (до 24 июня 2022 г.): узнать подробнее

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

DAX функции COUNT, COUNTA, COUNTX и COUNTAX в Power BI и Power Pivot

Итак, все эти функции COUNT, COUNTA, COUNTX и COUNTAX — отвечают за подсчет количества ячеек в Power BI и Power Pivot, но содержание ячеек в каждом из этих вариантов различается.

1. COUNT () — подсчитывает в столбце количество ячеек, которые содержат в себе числовое значение. В качестве числового значения признаются числа, даты и число, записанное в текстовом типе данных. Если в строке учитываемых значений нет, то функция выдаст 0. Если в таблице отсутствуют строки, то COUNT выдаст пустое значение.Синтаксис: COUNT ([Столбец])
2. COUNTA () — подсчитывает непустые ячейки в столбце. То есть, количество тех ячеек, которые в себе содержат хоть какое-то значение: числа, даты, любой текст или значения логического типа.Синтаксис: COUNTA ([Столбец])
3. COUNTX () — подсчитывает количество строк, содержащие в себе числовое значение, получившееся в результате построчного выполнения выражения. В качестве числового значения признаются числа, даты и число, записанное в текстовом типе данных.Синтаксис: COUNTX (‘Таблица’; Выражение), где:
   • ‘Таблица’ — исходная таблица или табличное выражение, по строкам которой будет вычисляться выражение из второго параметра функции
   • Выражение — любое выражение, которое необходимо выполнить по строкам таблицы, входящей в первый параметр функции

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

Записав следующую DAX формулу с участием COUNTAX ():

То COUNTBLANK вернет ответ: количество пустых ячеек = 2

DAX функция DISTINCTCOUNT или DISTINCT COUNT в Power BI и Power Pivot

Данную функцию почему-то очень часто называют неправильно, в два слова DISTINCT COUNT, хотя правильно писать в одно единое слово DISTINCTCOUNT.

Итак, DISTINCTCOUNT () — подсчитывает количество уникальных значений ячеек в столбце

Синтаксис: DISTINCTCOUNT ([Столбец])

Пример: имеется таблица, где в одном из столбцов перечисляются менеджеры:

Если мы подсчитаем количество уникальных фамилий менеджеров при помощи следующей формулы:

То ответ будет таким: количество уникальных фамилий менеджеров = 3

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

COUNTROWS () — часто используемая на практике функция, которая подсчитывает количество строк в таблице.

Синтаксис: COUNTROWS (‘Таблица’)

Пример: если мы подсчитаем количество строк в таблице «Менеджеры» из примера выше, то COUNTROWS выдаст ответ 4 строки.

COUNT и другие функции (CALCULATE, FILTER, IF)

Функции группы COUNT (подсчет количества) сами по себе используются довольно редко, обычно они нужны для каких-либо промежуточных вычислений в партнерстве с другими функциями. Например, функции группы COUNT используются совместно с CALCULATE, FILTER, с условиями «если» IF и другими функциями DAX.

Ну, как пример, можно подсчитать количество строк в таблице после применения фильтра функции FILTER:

Или после изменения фильтра при использовании CALCULATE:

На этом, по функциям группы COUNT в Power BI и Power Pivot в данной статье, все.

Подробное ВИДЕО «DAX функции COUNT (A, X, AX), COUNTBLANK, DISTINCTCOUNT, COUNTROWS для Power BI (Pivot)»

Ссылки из видео:
1) [Регистрируйтесь в бесплатном экспресс курсе] Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot: зарегистрироваться
2) [Скачивайте PDF] Справочник DAX функций для Power BI и Power Pivot на русском языке: скачать

Также, напоминаю Вам, что до 24 июня 2022 г. у Вас имеется шикарная возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 50% (вместо 10000, всего за 5000 руб.)

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

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 50% (до 24 июня 2022 г.): узнать подробнее

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

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

1. 5
2. 4
3. 3
4. 2
5. 1

Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

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

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

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

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

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

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

Вычисляем на языке DAX в Power BI и Power Pivot дату первой продажи товара

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

DAX функции YEARFRAC, WEEKDAY и WEEKNUM в Power BI и PowerPivot

Вычисления в PowerQuery

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

Вычисления в PowerQuery.xlsx (131,7 KiB, 1 382 скачиваний)

PowerQuery весьма мощный инструмент обработки данных внутри Excel, но если присмотреться – не видно даже намека на возможность использовать формулы. Нет хоть какого-то списка или значка по подобию Excel. Все потому, что PowerQuery не использует вычисления как таковые, если сравнивать с формулами в самом Excel. Но здесь есть другая возможность – добавлять целые вычисляемые столбцы. Для добавления такого столбца необходимо внутри редактора PowerQuery перейти на вкладку Добавить столбец —Пользовательский столбец. В первом окне(Имя нового столбца) задаем имя столбца, а во втором(Пользовательская формула столбца) – непосредственно формулу:

PowerQuery создаст новый столбец с заданным именем, в котором в каждой строке будут значения, вычисленные на основании заданной формулы. При этом все вычисления создаются исключительно линейно «внутри каждой строки» — сослаться на весь столбец или предыдущую строку возможности напрямую нет(как это делается в Excel).
Все доступные для использования в формуле столбцы перечислены в правом окне(Доступные столбцы). Для добавления в формулу какого-либо из столбцов можно просто щелкнуть по нему дважды левой кнопкой мыши или выделить нужный столбец и нажать кнопку Вставить.
На рисунке выше приведен пример простого объединения трех столбцов: Фамилия, Имя и Отчество при помощи формулы:
=[Фамилия]&» «&[Имя]&» «&[Отчество]
Она очень похожа на формулу Excel внутри «умной» таблицы — так же используются квадратные скобки с именами столбцов и нет никаких адресов ячеек. В результате этой формулы будет создан новый столбец ФИО, в котором будут последовательно объединены столбцы Фамилия, Имя и Отчество с пробелом в качестве разделителя.
Но это самый простой пример. На самом деле PowerQuery имеет большое количество встроенных функций, которые постоянно используются при любом нашем действии внутри редактора. Просто нам это не показывается напрямую. Но все эти функции можно посмотреть.
Во-первых они доступны в открытом доступе на сайте Microsoft: PowerQuery — функция языка M.
Во-вторых есть способ посмотреть список функций прямо из PowerQuery:
Вкладка Главная –Создать источник -Другие источники -Пустой запрос. В новом окне в строке формул записываем: =#shared и жмем Enter. Будет создан новый запрос, в котором будет выведет список всех доступных функций:

Теперь можно левой кнопкой мыши щелкнуть любую функцию из списка и появится окно с аргументами функции и её описанием.
По сути это практически тот же диспетчер функций в Excel, только разделитель аргументов здесь всегда запятая и самое главное – функции PowerQuery чувствительны к регистру. Например, если функцию Date.MonthName записать с маленькой буквы — d ate.MonthName , то получим синтаксическую ошибку. Поэтому очень важно копировать все формулы точь-в-точь как они записаны в справочнике.
Так же как и в случае с функциями Excel, функции PowerQuery делятся на категории(Text – текстовые, Date – дата, DateTime – дата и время и т.д.). И в отличии от функций в Excel для функций PowerQuery обязательно указывать из какой категории функция используется:
=Категория.ИмяФункции(аргументы)
Разберем пару коротких примеров формул с описанием того, что они делают:
• Text.Upper([ФИО]) – преобразует все буквы указанного столбца в верхний регистр
• Text.Lower([ФИО]) – преобразует все буквы указанного столбца в нижний регистр
• Text.Reverse([Имя]) – записывает все буквы указанного столбца в обратном порядке
• Text.Contains([Имя],»а») – определяет, встречается ли в тексте столбца Имя хоть одна буква «а».
Помимо простой вставки функции можно использовать и целые синтаксические конструкции. Правда для этого потребуется чуть больше знаний и очень желателен опыт работы с какими-либо языками программирования, т.к. именно таковым и является используемый в PowerQuery язык M. Возьмем простой пример: необходимо определить, встречается ли в тексте столбца Имя хоть одна буква «а» и если встречается – вернуть в вычисляемый столбец ФИО, а если нет – оставить пустым. В случае с Excel это выглядело бы так(в столбце A – имя, в столбце B – ФИО):
=ЕСЛИ(ЕОШ(НАЙТИ(«а»;A2));»»;B2)
Довольно сложно выглядит, но более-менее понятно. В PowerQuery для выполнения той же задачи придется использовать всего одну функцию, но вдобавок к ней целую условную конструкцию:

что дословно так и читается: если( if ) выражение( Text.Contains([Имя],»а») ) выполняется, то( then ) значение_если_истина([ФИО]), в противном случае(else) – значение если ложь(«»).
И здесь опять надо помнить про регистр: if , then , else – должны быть записаны в нижнем регистре, иначе PowerQuery просто их не распознает.

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

В столбце J (Отклонение всего) записана формула:
= G4 — C4
Её достаточно легко воспроизвести в PowerQuery:
[#»Факт, руб»]-[#»План, руб»]
А вот в столбце К (Ценовой фактор) формула сложнее:
=( H4 — D4 )* I4 *СУММ( $F$4:$F$15 )
Помимо «линейных» вычислений внутри строки здесь применяется функция СУММ . И аналогом этой формулы в PowerQuery в данном случае будет функция работы со списками List.Sum . Но применять её необходимо по своим правилам. Если просто записать её, применив к имени столбца:
List.Sum([#»Факт, шт»])
То обязательно получим ошибку, т.к. обращение [#»Факт, шт»] подразумевает под собой извлечение данных только из одной ячейки строки и представляет собой одно единственное значение. А функция List.Sum требует указания целого столбца. Чтобы указать целый столбец исходной таблицы необходимо скопировать название первого шага(из расширенного редактора или подсмотрев в шагах запроса) и добавить его перед именем столбца, чтобы получилось что-то такое:
List.Sum(Источник[#»Факт, шт»])

В итоге получится такая формула:
=([#»Факт, Средняя цена за шт»]-[#»План, Средняя цена за шт»])*[#»Факт, Доля продаж»]*List.Sum(Источник[#»Факт, шт»])

Конечно, функций в PowerQuery и конкретно в языке M довольно много и хочется рассказать про все 😊 Но какие-то из них слишком хитрые и требуют особого внимания и отдельной статьи на каждую функцию. Целью же данной статьи было дать некую отправную точку для изучения этой возможности PowerQuery. Надеюсь, после прочтения статьи и просмотра видеоурока понимания работы с языком М будет чуть больше и вы сможете самостоятельно создавать свои вычисления и подбирать для них нужные функции.