Excel power query счетесли
Эти функции позволяют создавать табличные значения и управлять ими.
Конструирование таблиц
Функция | Описание |
---|---|
ItemExpression.From | Возвращает абстрактное синтаксическое дерево (AST) для текста функции. |
ItemExpression.Item | Узел абстрактного синтаксического дерева (AST), представляющий элемент в выражении элемента. |
RowExpression.Column | Возвращает абстрактное синтаксическое дерево (AST), представляющее доступ к столбцу в выражении строки. |
RowExpression.From | Возвращает абстрактное синтаксическое дерево (AST) для текста функции. |
RowExpression.Row | Узел абстрактного синтаксического дерева (AST), представляющий строку в выражении строки. |
Table.FromColumns | Возвращает таблицу из списка, содержащего вложенные списки с именами и значениями столбцов. |
Table.FromList | Преобразует список в таблицу путем применения заданной функции разбиения к каждому элементу в списке. |
Table.FromRecords | Возвращает таблицу из списка записей. |
Table.FromRows | Создает таблицу из списка, в которой каждый элемент списка является списком, содержащим значения столбца для одной строки. |
Table.FromValue | Возвращает таблицу со столбцом, содержащим указанное значение или список значений. |
Table.FuzzyGroup | На основе нечеткого соответствия группирует строки таблицы со значениями строк указанного столбца. |
Table.FuzzyJoin | Соединяет строки из двух таблиц, обладающие нечетким соответствием по заданным ключам. |
Table.FuzzyNestedJoin | Выполняет нечеткое соединение таблиц по указанным столбцам и выводит результат соединения в новом столбце. |
Table.Split | Разделяет указанную таблицу на список таблиц с заданным размером страницы. |
Table.View | Создает или расширяет таблицу, используя определенные пользователем обработчики операций запросов и действий. |
Table.ViewFunction | Создает функцию, которая может быть перехвачена обработчиком, определенным в представлении (через Table.View ). |
Преобразования
Функция | Описание |
---|---|
Table.ToColumns | Возвращает список вложенных списков, каждый из которых представляет столбец значений во входной таблице. |
Table.ToList | Возвращает таблицу в список путем применения заданной функции объединения к каждой строке значений в таблице. |
Table.ToRecords | Возвращает список записей из входной таблицы. |
Table.ToRows | Возвращает вложенный список значений из входной таблицы. |
Сведения
Функция | Описание |
---|---|
Table.ApproximateRowCount | Возвращает приблизительное количество строк в таблице. |
Table.ColumnCount | Возвращает количество столбцов в таблице. |
Table.IsEmpty | Возвращает значение true, если таблица не содержит строк. |
Table.Profile | Возвращает профиль столбцов таблицы. |
Table.RowCount | Возвращает количество строк в таблице. |
Table.Schema | Возвращает таблицу, содержащую описание столбцов (например, схему) указанной таблицы. |
Tables.GetRelationships | Возвращает связи между набором таблиц. |
Операции со строками
Операции со столбцами
Функция | Описание |
---|---|
Table.Column | Возвращает значения столбца в таблице. |
Table.ColumnNames | Возвращает имя столбцов в таблице. |
Table.ColumnsOfType | Возвращает список с именами столбцов, соответствующих указанным типам. |
Table.DemoteHeaders | Понижает уровень строки заголовка до первой строки таблицы. |
Table.DuplicateColumn | Дублирует столбец с указанным именем. Значения и тип копируются из исходного столбца. |
Table.HasColumns | Возвращает значение true, если таблица содержит указанный столбец или столбцы. |
Table.Pivot | При наличии таблицы и столбца атрибутов, содержащих pivotValues, создает новые столбцы для каждого из значений сведения, и назначает им значения из valueColumn. Можно предоставить необязательную функцию aggregationFunction для обработки нескольких вхождений одного и того же значения ключа в столбце атрибутов. |
Table.PrefixColumns | Возвращает таблицу, где для всех столбцов задано текстовое значение в качестве префикса. |
Table.PromoteHeaders | Продвигает первую строку таблицы в заголовок или имена столбцов. |
Table.RemoveColumns | Возвращает таблицу, не имеющую определенного столбца или столбцов. |
Table.ReorderColumns | Возвращает таблицу с конкретными столбцами в заданном порядке относительно друг друга. |
Table.RenameColumns | Возвращает таблицу со столбцами, переименованными, как указано. |
Table.SelectColumns | Возвращает таблицу, содержащую только определенные столбцы. |
Table.TransformColumnNames | Преобразует имена столбцов с помощью предоставленной функции. |
Table.Unpivot | При наличии списка столбцов таблицы преобразует эти столбцы в пары "атрибут-значение". |
Table.UnpivotOtherColumns | Преобразует все столбцы, отличные от заданного набора, в пары "атрибут-значение", объединенные с остальными значениями в каждой строке. |
Параметры
Преобразование
Параметры для параметров группы
Параметры для типов соединения
Алгоритм соединения
Следующие значения JoinAlgorithm можно указать для Table.Join
Значения параметров | Описание |
---|---|
JoinSide.Left | Указывает левую таблицу соединения. |
JoinSide.Right | Указывает правую таблицу соединения. |
Пример данных
В примерах этого раздела используются следующие таблицы.
Таблица Customers
Таблица Orders
Членство
Параметры для проверок членства
Спецификация вхождения
Компонент | Описание |
---|---|
Table.Contains | Определяет, появляется ли запись в виде строки в таблице. |
Table.ContainsAll | Определяет, появляются ли все указанные записи в виде строк в таблице. |
Table.ContainsAny | Определяет, появляются ли какие-либо указанные записи в виде строк в таблице. |
Table.Distinct | Удаляет повторяющиеся строки из таблицы, обеспечивая уникальность всех оставшихся строк. |
Table.IsDistinct | Определяет, содержит ли таблица только уникальные строки. |
Table.PositionOf | Определяет позицию или позиции строки в таблице. |
Table.PositionOfAny | Определяет позицию или позиции любой из указанных строк в таблице. |
Table.RemoveMatchingRows | Удаляет все вхождения строк из таблицы. |
Table.ReplaceMatchingRows | Заменяет определенные строки из таблицы новыми строками. |
Упорядочение
Демонстрационные данные
В примерах этого раздела используются следующие таблицы.
Таблица Employees
Компонент | Описание |
---|---|
Table.Max | Возвращает самую большую строку или строки из таблицы с помощью comparisonCriteria. |
Table.MaxN | Возвращает из таблицы количество N самых длинных строк. После сортировки строк необходимо указать параметр countOrCondition для дальнейшей фильтрации результатов. |
Table.Min | Возвращает наименьшую строку или строки из таблицы с помощью comparisonCriteria. |
Table.MinN | Возвращает количество N наименьших строк в заданной таблице. После сортировки строк необходимо указать параметр countOrCondition для дальнейшей фильтрации результатов. |
Table.Sort | Сортирует строки в таблице с помощью comparisonCriteria или порядка по умолчанию, если он не указан. |
Другое
Компонент | Описание |
---|---|
Table.Buffer | Помещает таблицу в буфер памяти, изолируя ее от внешних изменений во время оценки. |
Значения параметров
Именование выходных столбцов
Этот параметр представляет собой список текстовых значений, указывающих имена столбцов результирующей таблицы. Этот параметр обычно используется в функциях построения таблиц, таких как Table.FromRows и Table.FromList.
Критерии сравнения
Критерий сравнения может быть предоставлен как одно из следующих значений:
Числовое значение для указания порядка сортировки. См. раздел о порядке сортировки в разделе о значениях параметров.
Чтобы вычислить ключ, используемый для сортировки, можно использовать функцию с одним аргументом.
Чтобы выбрать ключ и порядок, критерием сравнения может быть список, содержащий ключ и порядок.
Примеры см. в описании Table.Sort.
Критерий количества или условия
Этот критерий обычно используется при упорядочивании или в операциях со строками. Он определяет количество строк, возвращаемых в таблице, и может принимать две формы — число или условие:
Число указывает, сколько значений следует вернуть, а также соответствующую функцию.
Если указано условие, возвращается строка, содержащая значения, которые изначально соответствуют условию. Если значение не соответствует условию, дальнейшие значения не учитываются.
Обработка дополнительных значений
Используется для указания того, как функция должна управлять дополнительными значениями в строке. Этот параметр указывается в виде числа, которое сопоставляется с параметрами ниже.
Дополнительные сведения см. в разделе Table.FromList.
Обработка отсутствующих столбцов
Используется для указания того, как функция должна работать с отсутствующими столбцами. Этот параметр указывается в виде числа, которое сопоставляется с параметрами ниже.
Используется в преобразовании или операциях со столбцами. Примеры см. в разделе Table.TransformColumns.
Порядок сортировки
Используется для указания того, как должны быть отсортированы результаты. Этот параметр указывается в виде числа, которое сопоставляется с параметрами ниже.
Критерии равенства
Критерий равенства для таблиц можно указать как:
значение функции, которое является:
селектором ключа, определяющим столбец в таблице для применения условий равенства;
функцией сравнения, которая используется для указания применяемого типа сравнения. Встроенные функции сравнения можно указать. См. раздел о функциях сравнения.
You know how to countif in Excel so why is Power Query so difficult?
If you are familiar with the Excel COUNTIF formula then you might find it frustrating to try and replicate this in Power Query or PowerBI in M Language.
I've put together a simple Excel 2016 file to illustrate the Excel way vs the Power Query / PowerBi M Language equilvalent.
You can download this data in the 2016 Excel file here on Dropbox.
Let's create two tables:
The two tables are on WorkSheet countif and I have inserted the seed data below as tables named City and People . You can see the table design by clicking the design tab and inspecting the Table Name field.
City is made up of two columns and an additional four of the alternatives for counting the population :
- Id
- Name
- Population(x) -> represents countif using excel
- Population(m) -> countif using M Language (option 1)
- Population(so) -> countif using M Language (option 2)
- Population(f) -> countif using M Language Customer Function
People is made up of three columns:
Let's fill these tables with some seed data for our examples.
CITY
Id | Name | Population(x) | Population(m) | Population(so) | Population(f) |
---|---|---|---|---|---|
1 | Brisbane | 2 | 2 | 2 | 2 |
2 | Sydney | 1 | 1 | 1 | 1 |
3 | Melbourne | 1 | 1 | 1 | 1 |
PEOPLE
Id | Name | CityId |
---|---|---|
1 | Jay | 1 |
2 | Sam | 2 |
3 | Sarah | 1 |
3 | Holly | 3 |
I asked a few questions about this over on StackOverflow with [Add Column - CountIF across two tables][1] and [Add Function CountIF][2].
I tried to break this question down as simple as possible. Two very basic tables and wanting to add a column that counts the occurences of the first tables Primary Keys in the second tables reference field.
This is pretty basic if you are across Excel Table Structured References or even just a basic Excel formulas.
To complete the field Population(x) we can simply add an extra column to the City table in Excel (as I have already done) and copy the formula =COUNTIF(People[CityId],[@Id]) . Make sure this is pasted down the entire column and bam! it is done. How simple and easy :)
Now to replicate this in Power Query using the M Language.
I have already created the queries by adding the data from Excel Tables (Click in the Table -> Click the Data Tab -> Click From Table). We will then open the Advanced Editor and build out our Power Query M Language code.
Query Editor Group Directory Structure
All the magic of the Power Query M is happening in the City.m and the f_countif.m . First we'll inspect the City.m query.
Here you can see the 3 additional columns add through the M Table.AddColumn method. I'll add commmentary to the code to explain each step.
add_pop_m
The steps of this portion of the M Query goes someting like this:
- add a new column to the previous table and name it "Population(m)"
- each row of the name column gets assigned the values of the current row of the City
- a function is then invoked on that variable
- this function counts rows within the People table
- the People table is filtered where each row of the CityId column matches the CityRow[Id] of the CityRow variable
add_pop_so
This is a similar alternative version from the StackOverflow answer given by Imke Feldman. It starts off the same way but with slightly different syntax that I don't yet full understand. But it works :)
add_pop_fm
This is another alternative that utilised a Custom Function. The benefits of this is that it is much shorter within the City query but required an additional Customer Function query to be created. This query can therefore be used in other queries as a Custom Function which can keep our code DRY.
This function has a table, a column and a value passed in. It works much more like the traditional Excel formula.
Closing Thoughts
There is another alternative way where a table is created that groups the People table by the CityId and sums this into a new field called City.Count . This table can then be merged with the City table. This adds additional steps but could be completed with less queries on the database. What is unclear at this stage is which is the most efficient alternative. One issue I would like to investigate is whether running a function within an each block would trigger multiple queries to the database for each iteration.
This is a problem I have had in the past in MVC frameworks such as Ruby on Rails.
That is all for now. If you have anything you can add please post your comment below. Other sources of good information that I have used for this post include include:
Also thanks to the people that helped talk through this scenario on StackOverflow.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
С помощью статистической функции СЧЁТЕСЛИ можно подсчитать количество ячеек, отвечающих определенному условию (например, число клиентов в списке из определенного города).
Самая простая функция СЧЁТЕСЛИ означает следующее:
=СЧЁТЕСЛИ(где нужно искать;что нужно найти)
СЧЁТЕСЛИ(диапазон;критерий)
Имя аргумента
диапазон (обязательный)
Группа ячеек, для которых нужно выполнить подсчет. Диапазон может содержать числа, массивы, именованный диапазон или ссылки на числа. Пустые и текстовые значения игнорируются.
критерий (обязательный)
Число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать.
Например, критерий может быть выражен как 32, ">32", В4, "яблоки" или "32".
В функции СЧЁТЕСЛИ используется только один критерий. Чтобы провести подсчет по нескольким условиям, воспользуйтесь функцией СЧЁТЕСЛИМН.
Примеры
Чтобы использовать эти примеры в Excel, скопируйте данные из приведенной ниже таблицы и вставьте их на новый лист в ячейку A1.
Количество ячеек, содержащих текст "яблоки" в ячейках А2–А5. Результат — 2.
Количество ячеек, содержащих текст "персики" (значение ячейки A4) в ячейках А2–А5. Результат — 1.
Количество ячеек, содержащих текст "яблоки" (значение ячейки A2) и "апельсины" (значение ячейки A3) в ячейках А2–А5. Результат — 3. В этой формуле для указания нескольких критериев, по одному критерию на выражение, функция СЧЁТЕСЛИ используется дважды. Также можно использовать функцию СЧЁТЕСЛИМН.
Количество ячеек со значением больше 55 в ячейках В2–В5. Результат — 2.
Количество ячеек со значением, большим или равным 32 и меньшим или равным 85, в ячейках В2–В5. Результат — 1.
Количество ячеек, содержащих любой текст, в ячейках А2–А5. Подстановочный знак "*" обозначает любое количество любых символов. Результат — 4.
Количество ячеек, строка в которых содержит ровно 7 знаков и заканчивается буквами "ки", в диапазоне A2–A5. Подставочный знак "?" обозначает отдельный символ. Результат — 2.
Распространенные неполадки
Возможная причина
Для длинных строк возвращается неправильное значение.
Функция СЧЁТЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов.
Для работы с такими строками используйте функцию СЦЕПИТЬ или оператор сцепления &. Пример: =СЧЁТЕСЛИ(A2:A5;"длинная строка"&"еще одна длинная строка").
Функция должна вернуть значение, но ничего не возвращает.
Аргумент критерий должен быть заключен в кавычки.
Эта ошибка возникает при вычислении ячеек, когда в формуле содержится функция, которая ссылается на ячейки или диапазон в закрытой книге. Для работы этой функции необходимо, чтобы другая книга была открыта.
Рекомендации
Помните о том, что функция СЧЁТЕСЛИ не учитывает регистр символов в текстовых строках.
Критерий не чувствителен к регистру. Например, строкам "яблоки" и "ЯБЛОКИ" будут соответствовать одни и те же ячейки.
Использование подстановочных знаков
Подстановочные знаки — вопросительный знак (?) и звездочка (*) — можно использовать в критериях. Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед искомым символом.
Например, =СЧЁТЕСЛИ(A2:A5;"яблок?") возвращает все вхождения слова "яблок" с любой буквой в конце.
Убедитесь, что данные не содержат ошибочных символов.
При подсчете текстовых значений убедитесь в том, что данные не содержат начальных или конечных пробелов, недопустимых прямых и изогнутых кавычек или непечатаемых символов. В этих случаях функция СЧЁТЕСЛИ может вернуть непредвиденное значение.
Для удобства используйте именованные диапазоны.
ФУНКЦИЯ СЧЁТЕСЛИ поддерживает именованные диапазоны в формуле (например, =СЧЁТЕСЛИ(> =32")-СЧЁТЕСЛИ(> 85). Именованный диапазон может располагаться на текущем листе, другом листе этой же книги или листе другой книги. Чтобы одна книга могла ссылаться на другую, они обе должны быть открыты.
Примечание: С помощью функции СЧЁТЕСЛИ нельзя подсчитать количество ячеек с определенным фоном или цветом шрифта. Однако Excel поддерживает пользовательские функции, в которых используются операции VBA (Visual Basic для приложений) над ячейками, выполняемые в зависимости от фона или цвета шрифта. Вот пример подсчета количества ячеек определенного цвета с использованием VBA.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
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
- Right click on the Dates column
- Go to the Transform Option
- Pick the Year
- 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
- In the Transform Tab go to Group By
- In the group by box, group it by Year and Region
- 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)
- 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
- Now that we have an All Rows (expandable) Column, let’s expand that and get the figures for Units, Sales and Regions
- We can now simply create 2 Columns by dividing each Unit and Sales by their Totals
- In case you would cleanse this further, you may now choose to remove the Total Columns (for units and sales)
- 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
В данной статье я хочу рассказать о некоторых возможностях бесплатной и крайне полезной, но пока еще мало известной надстройки над MS Excel под названием Power Query.
Power Query позволяет забирать данные из самых разных источников (таких как csv, xls, json, текстовых файлов, папок с этими файлами, самых разных баз данных, различных api вроде Facebook opengraph, Google Analytics, Яндекс.Метрика, CallTouch и много чего еще), создавать повторяемые последовательности обработки этих данных и загружать их внутрь таблиц Excel или самого data model.
И вот под катом вы можете найти подробности всего этого великолепия возможностей.
Совместимость и технические подробности
Power Query доступен бесплатно для всех версий Windows Excel 2010, 2013 и встроен по умолчанию в Windows Excel 2016. Для пользователей MacOS X Power Query недоступен (впрочем, даже без этого маковский Excel отвратителен на ощупь и продвинутые пользователи, включая меня, чаще всего работают с нормальным Excel через Parallels или запуская его на удаленной виндовой машинке).
Также, Power Query встроен в новый продукт для бизнес аналитики — Power BI, а еще, ходят слухи, что Power Query будет появляться и в составе других продуктов от Microsoft. Т.е. Power Query ждет светлое будущее и самое время для адептов технологий Microsoft (и не только) заняться его освоением.
Как оно работает
После установки Power Query в интерфейсе Excel 2010–2013 появляется отдельная одноименная вкладка.
В новом Excel 2016 функционал Power Query доступен на вкладке Data (данные), в блоке “Get & Transform”.
Сначала, в интерфейсе Excel мы выбираем конкретный источник данных, откуда нам их нужно получить, и перед нами открывается окошко самого Power Query с предпросмотром первых строчек загруженных данных (область 1). В верхней части окошка располагается Ribbon с командами по обработке данных (область 2). И в правой части экрана (область 3) у нас расположена панель с последовательностью всех действий, которые применяются к данным.
Возможности Power Query
У Power Query очень много возможностей и я хочу остановиться на некоторых из числа моих любимых.
Как я уже писал выше, Power Query замечателен тем, что позволяет подключаться к самым разным источникам данных. Так он позволяет загружать данные из CSV, TXT, XML, json файлов. Притом процесс выбора опций загрузки тех-же CSV файлов гибче и удобнее, чем он реализован штатными средствами Excel: кодировка автоматически выбирается часто правильно и можно указать символ разделителя столбцов.
Объединение файлов лежащих в папке
Power Query умеет забирать данные из указанной папки и объединять их содержимое в единые таблицы. Это может быть полезно, например, если вам периодически приходят какие-то специализированные отчеты за отдельный промежуток времени, но данные для анализа нужны в общей таблице. Гифка
Текстовые функции
- Разделить столбец по символу или по количеству символов. И в отличие от Excel можно задать максимальное количество столбцов, а также направление откуда нужно считать символы — слева, справа.
- Изменить регистр ячеек в столбце
- Подсчитать количество символов в ячейках столбца.
Числовые функции
К столбцам с числовыми значениями по нажатию на кнопки на Ribbon можно применять:
- Арифметические операции
- Возводить в степени, вычислять логарифмы, факториалы, корни
- Тригонометрические операции
- Округлять до заданных значений
- Определять четность и т.д.
Функции для работы с датами, временем и продолжительностью
К столбцам со значениями даты и времени по нажатию на кнопки на Ribbon можно применять:
- Автоматическое определение формата вписанной даты (в excel c этим большая боль)
- Извлекать в один клик номер месяца, дня недели, количество дней или часов в периоде и т.п.
Unpivot — Pivot
В интерфейсе Power Query есть функция “Unpivot”, которая в один клик позволяет привести данные с одной метрикой разложенные по столбцам по периодам к форме, которая будет удобна для использования в сводных таблицах (понимаю что трудно написал — смотрите пример). Также, есть функция с обратным действие Pivot. Гифка
Операция Merge — смерть ВПР
Функция ВПР (VLOOKUP) одна из наиболее используемых функций в MS Excel. Она позволяет подтягивать данные в одну таблицу из другой таблицы по единому ключу. И вот как раз для этой функции в Power Query есть гораздо более удобная альтернатива — операция Merge. При помощи этой операции соединение таблиц нескольких таблиц в одну по ключу (по простому или по составному ключу, когда соответствие нужно находить по нескольким столбцам) выполняется буквально в 7 кликов мыши без ввода с клавиатуры.
Операция Merge — это аналог join в sql, и ее можно настроить чтобы join был разных типов — Inner (default), Left Outer, Right Outer, Full Outer.
Upd.Мне тут подсказали, что Power Query не умеет делать Aproximate join, а впр умеет. Чистая правда, из коробки альтернатив нет. Гифка
Подключение к различным базам данных. Query Folding.
Power Query также замечателен тем, что умеет цепляться к самым разным базам данных — от MS SQL и MySQL до Postgres и HP Vertica. При этом, вам даже не нужно знать SQL или другой язык базы данных, т.к. предпросмотр данных отображается в интерфейсе Power Query и все те операции, которые выполняются в интерфейсе прозрачно транслируются в язык запросов к базе данных.
А еще в Power Query есть понятие Query Folding: если вы подключены к совместимой базе данных (на текущий момент это MS SQl), то тяжелые операции по обработке данных Power Query будет стараться выполнить на серверной стороне и забирать к себе лишь обработанные данные. Эта возможность радикально улучшает быстродействие многих обработок.
Язык программирования “М”
Надстройка Power Query — это интерпретатор нового, скриптового, специализированного для работы с данными, языка программирования М.
На каждое действие, которое мы выполняем с данными в графическом интерфейсе Power Query, в скрипт у нас пишется новая строчка кода. Отражая это, в панели с последовательностью действий (область 3), создается новый шаг с говорящим названием. Благодаря этому, используя панель с последовательностью действий, мы всегда можем посмотреть как выглядят у нас данные на каждом шаге обработки, можем добавить новые шаги, изменить настройки применяемой операции на конкретном шаге, поменять их порядок или удалить ненужные шаги. Гифка
Также, мы всегда можем посмотреть и отредактировать сам код написанного скрипта. И выглядеть будет он примерно так:
Язык M, к сожалению, не похож ни на язык формул в Excel, ни на MDX и, к счастью, не похож на Visual Basic. Однако, он очень прост в изучении и открывает огромные возможности по манипуляции данными, которые недоступны с использованием графического интерфейса.
Загрузка данных из Яндекс.Метрики, Google Analytics и прочих Api
Немного овладев языком “M” я смог написать программки в Power Query, которые умеют подключаться к API Яндекс.Метрики и Google Analytics и забирать оттуда данные с задаваемыми настройками. Программки PQYandexMetrika и PQGoogleAnalytics я выложил в опенсорс на гитхаб под лицензией GPL. Призываю пользоваться. И я буду очень рад, если эти программы будут дорабатываться энтузиастами.
Для Google Analytics подобного рода экспортеров в разных реализациях достаточно много, но вот для Яндекс.Метрики, насколько я знаю, мой экспортер был первым публично доступным, да еще и бесплатным :)
Power Query умеет формировать headers для post и get запросов и забирать данные из интернета. Благодаря этому, при должном уровне сноровки, Power Query можно подключить практически к любым API. В частности, я для своих исследований дергаю данные по телефонным звонкам клиентов из CallTouch API, из API сервиса по мониторингу активности за компьютером Rescuetime, занимаюсь парсингом нужных мне веб-страничек на предмет извлечения актуальной информации.
Еще раз про повторяемость и про варианты применения
Как я уже писал выше, скрипт Power Query представляет собой повторяемую последовательность манипуляций, применяемых к данным. Это значит, что однажды настроив нужную вам обработку вы сможете применить ее к новым файлам изменив всего один шаг в скрипте — указав путь к новому файлу. Благодаря этому можно избавиться от огромного количества рутины и освободить время для продуктивной работы — анализа данных.
Я занимаюсь веб-аналитикой и контекстной рекламой. И так уж получилось, что с момента, как я познакомился с Power Query в ее интерфейсах я провожу больше времени, чем в самом Excel. Мне так удобнее. Вместе с тем возросло и мое потребление другой замечательной надстройки в MS Excel — PowerPivot.
- разбираю семантику для Толстых проектов,
- Делаю частотные словари,
- Создаю веб-аналитические дашборды и отчеты для анализа конкретных срезов,
- Восстанавливаю достижение целей в системах веб-аналитики, если они не настроены на проекте,
- Сглаживаю прогноз вероятности методами Андрея Белоусова (+Байеса:),
- Делаю аудит контекстной рекламы на данных из K50 статистика,
- И много других разных ad-hoc analysis задач, которые нужно сделать лишь однажды
Вот bi систему, про которую я рассказывал на Yac/M 2015 (видео) я делал полнстью при помощи Power Query и загружал данные внутрь PowerPivot.
Пару слов про локализацию
На сайте Microsoft для пользователей из России по умолчанию скачивается Power Query с переведенным на русским язык интерфейсом. К счастью, локализаторы до перевода на русский языка программирования (как это сделано с языком формул в excel) не добрались, однако жизнь пользователям неоднозначными переводами сильно усложнили. И я призываю вас скачивать, устанавливать и пользоваться английской версией Power Query. Поверьте, она будет гораздо понятнее.
Читайте также: