Как делать Countifs/Sumifs в формулах PowerQuery M.Language?
Можно ли использовать формулы Excel Countifs/Sumifs в формулах powerquery? Допустим, у меня есть [Столбец1] как «Диапазон критериев» и [Столбец2] как «Критерии» (пример критерия [Текст столбца2] содержит «Продукт»), и я хочу, чтобы подсчет/сумма ячеек [Столбец1] в новом пользовательском столбце ([Столбец3 ])
6 ответов
Вы можете попробовать эту формулу:
Он не такой гибкий, как СУММЕСЛИ, но подсчитывает количество записей в столбце 1, содержащих слово, которое стоит в текущей строке столбца 2.
Я использую эту функцию в моем редакторе Power Query для СЧЁТЕСЛИ, но я еще не освоил СЧЁТЕСЛИМН или СУММЕСЛИМН:
Затем я использую его как
Я довольно подробно написал об этом в своем блоге, и это мой предпочтительный способ сделать это.
Я надеюсь, что это поможет вам 🙂
Вы можете использовать фильтры для фильтрации на основе ваших критериев, а затем использовать Count Rows на вкладке ленты Transform. Фильтрацию можно выполнить, нажав на стрелку в столбце.
Кроме того, вы можете отфильтровать столбец, а затем использовать Group By для вычисления количества или суммы.
Думаю, я решил этот вопрос.
Я создал CSV. [Sumifs test.csv] и связал запрос с этим файлом.
Это исходная таблица, выглядит так: [Заголовок]
Пусть
Source = Csv.Document(File.Contents(«D:\Power BI\Tests\Sumifs test.csv»),[Delimiter=»,», Encoding=1252]),
#»PromoteHeaders1″ = Table.PromoteHeaders(Source),
#»Измененный тип» = Table.TransformColumnTypes(#»Promoted Headers1″,<<"Num1", Int64.Type>, <"Num2", Int64.Type>, <"Value", Int64.Type>>),
MyList = List.Buffer(#»Сгруппированные строки»[Значения]),
Custom1 = #»Сгруппированные строки»,
#»Добавленный пользовательский» = Table.AddColumn(Custom1, «Накопительный», каждый List.Sum( List.Select(MyList, (x) => x>=[Values]))),
MyList2 = List.Buffer(#»Сгруппированные строки1″[Значения2]),
Custom2 = #»Сгруппированные строки1″,
#»Объединенные запросы» = Table.NestedJoin(Phase1,<"Alpha1">,Custom2,<"Alpha1">,»NewColumn»,JoinKind.LeftOuter),
#»Expanded NewColumn» = Table.ExpandTableColumn(#»Объединенные запросы», «NewColumn», <"Values2">, <"NewColumn.Values2">), Phase2=#»Расширенная новая колонка»,
#»Объединенные запросы2″ = Table.NestedJoin(Phase2,<"Alpha1_1">,Custom1,<"Alpha1_1">,»NewColumn»,JoinKind.LeftOuter),
#»Expanded NewColumn1″ = Table.ExpandTableColumn(#»Объединенные запросы2″, «NewColumn», <"Values">, <"NewColumn.Values">)
Посмотрите на этот пример: у меня есть таблица с 4 столбцами (серийный номер, команда, имя, роль), как показано в здесь и хотите создать сводную таблицу со столбцами (Team, HasAdmin?, CountOfMembers)
Я делаю это в 3 шага, как показано на рисунке:
Шаг 1: добавьте 2 новых столбца с помощью кнопки conditional column на ленте Add Column , как показано на здесь и здесь
Шаг 2: измените тип новых столбцов на «целое число», как показано здесь
Шаг 3. Выберите столбец [Команда] и нажмите кнопку Group By на ленте Home и заполните, как показано здесь
Для «sumif» вы можете поместить значение ячейки вместо «1» на шаге 1
Powerquery больше использует подход ETL. Таким образом, у вас не может быть активного OLE в случае powerquery. Вы можете связать существующую таблицу в Excel в качестве источника данных и создать определенную сводку для того, что вы хотите. Если вы хотите добиться такой же функциональности, как в Excel, есть некоторые предварительные условия.
Ваши данные должны находиться внутри таблицы Excel. Создайте еще одну таблицу, содержащую критерии поиска. Теперь вам нужно сделать две таблицы, одну для данных, вторую для ваших критериев поиска.
Теперь в таблице, полученной из ваших существующих данных, вы можете создать дополнительный столбец индикатора, в котором вы можете вывести данные на основе критериев поиска, так как вам нужно что-то вроде реализации countifs, если Text.Contains (col1, критерии поиска1) и Text.Contains (col2, searchcriteria 2), чем 1, иначе 0 для реализации сумм, если Text.Contains(col1,searchcriteria1) и Text.Contains(col2,searchcriteria 2), чем [sumcol] else 0 суммирует таблицу на основе этого столбца и просто выбирает выходной диапазон на лист для выходного результата (который будет еще одной таблицей с одной ячейкой)
Если вы уже извлекаете данные из другого источника, просто создайте таблицу параметров для критериев поиска, если ваши критерии поиска фиксированы, просто пропустите данные и таблицу критериев поиска и используйте столбец индикатора.
Power query как посчитать счетесли
Доброго времени суток!
Который день пытаюсь построить цепочку функций в Power Query, но так и не смог, т.к. логика построения формул Excel в PQ не очень работает.
Вопрос про альтернативу СЧЕТЕСЛИМН в Power Query.
Вот есть массив (файл прилагаю) со столбцом Count, требуется по аналогии создать столбец в PQ для каждой позиции в массиве.
Спасибо заранее.
Доброго времени суток!
Который день пытаюсь построить цепочку функций в Power Query, но так и не смог, т.к. логика построения формул Excel в PQ не очень работает.
Вопрос про альтернативу СЧЕТЕСЛИМН в Power Query.
Вот есть массив (файл прилагаю) со столбцом Count, требуется по аналогии создать столбец в PQ для каждой позиции в массиве.
Спасибо заранее. Digitalizer
Сообщение Доброго времени суток!
Который день пытаюсь построить цепочку функций в Power Query, но так и не смог, т.к. логика построения формул Excel в PQ не очень работает.
Вопрос про альтернативу СЧЕТЕСЛИМН в Power Query.
Вот есть массив (файл прилагаю) со столбцом Count, требуется по аналогии создать столбец в PQ для каждой позиции в массиве.
Спасибо заранее. Автор — Digitalizer
Дата добавления — 23.05.2019 в 10:38
COUNTIF in 5 languages (VBA/SQL/PYTHON/M query/DAX powerBI)
How to make your favorite Excel feature in another analytic language (VBA/SQL/PYTHON/M query/DAX powerBI)
Excel is a powerful spreadsheet used by most people working in data analysis. The increase of volume of data and development user-friendly tools is an opportunity of improvement of Excel reports by mixing them with another tool or language.
As working for a financial reporting department I faced the need to boost our reporting tools. A simple way to start working with a new language is to translate what we use to do in excel, in another language. “How can I pivot this?”, “How can I vlookup that?”.
In this article I will share with you how you can make COUNTIF in 5 different languages: VBA, python, SQL, DAX (Power BI), M (Power query). it will simple tips but if you want to get more detailed article don’t forget to follow me!
COUNTIF in Excel
COUNTIF is one of the statistical function of Excel to count the number of cells that meet a criterion.
The simplest way of using it is as follow:
But in the part “What do you want to look for?” you can put a conditional criteria ( it could be logical operator (<,>,<>,=) and wildcards (*,$) for partial conditions).
In this article I will use a similar example as another article I wrote to present the SUMIF in 5 languages:
SUMIF in 5 languages (VBA/SQL/PYTHON/M query/DAX powerBI)
How to make your favorite Excel feature in another analytic language (VBA/SQL/PYTHON/M query/DAX powerBI)
It will be a tab with a list of items In cell C2 we can put a COUNTIF formula with arguments : Range=A2:A8, Criteria=cell(A2). So basically we count the number of “Item1” in our list.
COUNTIF in VBA
Visual Basic for Application (VBA) is an implementation of Microsoft Visual Basic integrated into Microsoft Office applications.
On your code, you can set up a variable to store the result of the COUNTIF result using the statement “WorksheetFunction.sumif”. It works exactly as the function itself. Then you can store the result directly into cell C2.
Let’s do it properly by declaring variables now:
COUNTIF in SQL
SQL ( Structured Query Language) or sequel, is a standard language for storing, manipulating and retrieving data in databases. It is one of the common upgrade done by companies that face limits with Excel. Usually, the first reaction is to negotiate some budget in order to store the data into a database and use SQL to “speak” with this database and organise, manipulate the data. The language is also highly appreciable. Close to a natural language, you don’t feel coding when typing simple SQL request.
Let’s start with creating a table with items:
Here is the result:
Then keep in mind that in SQL, when you want to get an information you will often use the expression SELECT. Basically in SQL you play with data, tables and relationship. So you have to understand well the “how I can get this information” in order to SELECT it:
SELECT “How I will get what I want” FROM “Were I have to apply the How”.
So in our case we will select the count of the case where the items of the column “Item” equals “Item1”. Let’s write the formula and then analyze it:
So we SELECT the COUNT of the CASE WHEN (CASE in SQL is a kind of “IF” function) Item='Item1' THEN (if it’s the case) we count 1. And this applied in table1.
COUNTIF in Python
Python is an interpreted, high level language with a generic purpose. It is used in a large range of application, including data analysis. We can present python by saying “For all application its libraries”. And for data, without surprise, we will use the famous Pandas.
There are several ways to COUNTIF in python. We could play with a dataframe, columns, group by etc. But here I will simply create a list with all items, then loop into this list and add 1 every time that the loop meet an “Item1”. All this stored in a variable result:
Here is the result:
COUNTIF in M
M is the powerful language behind the tool power query. Even if you are working on the query editor, every single step will be written in M. M stands for Data Mashup or Data Modeling. I highly recommend to have a look at this language, we can do so much more than just using the graphical interface.
Regarding the COUNTIF, the graphical interface is so smooth that we can directly use it. You just have to import your table into the graphical interface and then use the filter in the column Item:
Then you select the column “Items”, and on the field Transform do the COUNT:
And you get the count. Another way is to use the Group By in button in the left, but in the scale of our example, it will add more step that we can skip by directly filter and count. I will present this with another feature where it will be more useful (The pivot table).
COUNTIF in DAX:
DAX stands for Data Analysis Expressions. More than a language, it is a library of functions and operators that can be used to build formulas in Power BI and Power Pivot.
The COUNTIF is a good opportunity to introduce the function CALCULATE. It is one of the most famous function in Power BI that evaluates an expression after applying a filtering rule. It is more close to the COUNTIFS as you can apply several filters arguments.
Expression: is the expression to be evaluated.
Filter1,2,…: can be a Boolean expression, a table filter or even another function.
In our case, the expression to be evaluated is the count of Table1 items, by applying one filter table in column “Items”=”Item1"
CONCLUSION
This article is a part of a set of articles where I share my way of doing an Excel feature in other languages. For sure, when you decide to move from Excel to programming, your approach of data analysis will totally change and you will learn that there are tons of way to get to your solution. If you have another manner to do this or even know how to do it with other languages, feel free to share them in the comments or by contacting me in my social networks! I will be pleased to read your thoughts!
Power Query Базовый №17. Группировка
В этом уроке мы научимся выполнять группировку в Power Query. Группировка — это операция, при которой вы находите уникальные значения в одном поле и выполняете агрегирования каких-то других полей. Например, в этом уроке мы найдем сумму продаж на каждый день, а еще посчитаем количество сделок на каждый день, найдем максимальную сделку на каждый день и выполним другие агрегирования.
Группировку можно также выполнять и по нескольким полям. В таком случае мы получим агрегаты для каждого уникального сочетания этих нескольких полей.
Группировка в Power Query — это аналог операции GROUP BY из SQL. Так же это напоминает функции СУММЕСЛИ/СУММЕСЛИМН, СЧЕТЕСЛИ/СЧЕТЕСЛИМН, СРЗНАЧЕСЛИ и другие подобные функции. Если вы искали аналоги этих функций в Power Query, то вам нужно воспользоваться группировкой.
Решение
Задачу можно решить используя только лишь пользовательский интерфейс Power Query.
Нужно выбрать столбец, по которому мы будем делать группировку, перейти на вкладку Главная — Группировать по.
