Перевести формулу Excel для Power Query (язык M)

Содержание

Вопрос или проблема
Ответ или решение
Теория
Функциональные возможности:
Пример
Преобразование формулы в M:
Пример кода на M языке:
Применение

Вопрос или проблема

Всем привет, у меня есть источник данных, который необходимо отфильтровать. Ниже представлена упрощенная версия таблицы. В столбцах “A” до “D” у меня есть исходные данные, а в столбце “E” я написал формулу, которая позволяет мне фильтровать данные.

=IF(COUNTIF([Key];[@Key])=1;"Y";
IF(COUNTIF([Key];[@Key])>2;"";
IF(COUNTIFS([Key];[@Key];[Nr];[@Nr];[Provisional];[@Provisional])=2;"";
IF(AND(COUNTIFS([Key];[@Key];[Nr];[@Nr])=1;[@Nr]=1);"Y";
IF(AND(COUNTIFS([Key];[@Key];[Nr];[@Nr])=2;[@Provisional]="N");"Y";
"")))))

Запись действительна, если:

“Key” уникален
“Key” не уникален, но “Nr” уникален и равен 1
“Key”+”Nr” не уникален, но “Provisional” уникален и равен “N”.

Моя формула в Excel далека от идеала, но она делает то, что мне нужно. Мог бы кто-нибудь написать мне соответствующую формулу для Power Query (язык M)? Мне нужно использовать её для фильтрации данных перед импортом их в Power BI.

Заранее спасибо.

Используя эту таблицу (называется Table1):

| Key        | Nr | Provisional | VALUE |
|------------|----|-------------|-------|
| RED1000    | 1  | Y           | 14    |
| RED1000    | 2  | Y           | 8     |
| BLUE1001   | 2  | Y           | 18    |
| BLUE1001   | 2  | N           | 29    |
| YELLOW1002 | 1  | N           | 30    |
| YELLOW1002 | 1  | N           | 42    |
| GOLD1003   | 2  | Y           | 14    |
| PINK1004   | 1  | Y           | 15    |
| PINK1004   | 2  | N           | 21    |
| PINK1004   | 1  | Y           | 14    |
| PINK1004   | 2  | N           | 7     |
| GREY1005   | 1  | Y           | 21    |
| GREY1005   | 1  | N           | 16    |

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeDataType = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Nr", Int64.Type}, {"Provisional", type text}, {"VALUE", Int64.Type}}),

    //Указать любые значения Key, которые уникальны, с Y, остальные значения оставить как null.
    KeyIsUnique = Table.AddColumn(ChangeDataType, "Unique Key", each if List.Count(List.FindText(ChangeDataType[Key],[Key]))=1 then "Y" else null),

    //Объединить столбцы Key и Nr.
    AddTempCol = Table.AddColumn(KeyIsUnique, "Temp1", each [Key] & "|" & Number.ToText([Nr])),
    
    //Если столбец "Unique Key" равен null, то посчитать значения в TempColumn. Если он уникален и Nr = 1, то добавить Y, иначе оставить как null.
    NrIsUnique = Table.AddColumn(AddTempCol, "NrIsUnique", each if [Unique Key]=null then if List.Count(List.FindText(AddTempCol[Temp1],[Temp1]))=1 and [Nr]=1 then "Y" else null else null),

    //Объединить столбцы Key и Provisional.
    AddTempCol2 = Table.AddColumn(NrIsUnique, "Temp2", each [Key] & "|" & [Provisional]),

    //Посчитать комбинацию столбцов Key и Provisional, если столбцы Unique Key и NrIsUnique равны null. Если он уникален и Provisional равно N, то добавить Y
    ProvIsUnique = Table.AddColumn(AddTempCol2, "ProvIsUnique", each if [Unique Key]=null and [NrIsUnique]=null then if List.Count(List.FindText(AddTempCol2[Temp2],[Temp2]))=1 and [Provisional]="N" then "Y" else null else null),
    
    //Объединить три результата столбцов и удалить другие столбцы.
    AddValidRows = Table.AddColumn(ProvIsUnique, "VALID ROWS", each Text.Combine({[Unique Key], [NrIsUnique], [ProvIsUnique]})),
    RemoveOtherColumns = Table.SelectColumns(AddValidRows,{"Key", "Nr", "Provisional", "VALUE", "VALID ROWS"})

in
    RemoveOtherColumns

Возвращает эту таблицу:

Ответ или решение

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

Теория

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

Функциональные возможности:

Добавление или преобразование столбцов: M язык позволяет создавать новые столбцы на основе вычислений или логических проверок.
Фильтрация данных: Также возможна избирательная фильтрация данных, которая выполняется на основе логических условий.
Типы данных: M язык поддерживает строгую типизацию, что способствует более точным вычислениям и минимизации ошибок.
Комбинирование данных: Можно комбинировать данные из различных таблиц или источников, что значительно увеличивает эффективность обработки информации.

Пример

В следующем примере пользователь хотел бы фильтровать записи в таблице на основе ряда логических условий. В Excel это осуществлено через формулу, использующую IF и COUNTIF. Для адаптации этой логики в Power Query, мы реализуем аналогичную логику, но более функционально и понятно. Логика фильтрации заключается в следующем:

"Key" является уникальным.
"Key" не уникален, но "Nr" уникален и равен 1.
"Key"+"Nr" не уникальна, но "Provisional" уникальна и равна "N".

Преобразование формулы в M:

Вводя Power Query в обрабатываемый проект, мы создаем несколько временных колонок для расчета условий, аналогичных тем, что использовались в Excel. Это включает в себя использование функций для вычисления количества определённых записей (Analyze unique occurrences), сравнение значений и объединение данных столбцов.

Пример кода на M языке:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeDataType = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Nr", Int64.Type}, {"Provisional", type text}, {"VALUE", Int64.Type}}),

    // Определение уникальности ключа
    KeyIsUnique = Table.AddColumn(ChangeDataType, "Unique Key", each if List.Count(List.Select(ChangeDataType[Key], (x) => x = [Key]))=1 then "Y" else null),

    // Создание временного столбца для комбинации Key и Nr
    AddTempCol = Table.AddColumn(KeyIsUnique, "Temp1", each [Key] & "|" & Number.ToText([Nr])),

    // Проверка уникальности Nr при условии неуникальности Key
    NrIsUnique = Table.AddColumn(AddTempCol, "NrIsUnique", each if [Unique Key]=null then if List.Count(List.Select(AddTempCol[Temp1], (x) => x = [Temp1]))=1 and [Nr]=1 then "Y" else null else null),

    // Создание временного столбца для комбинации Key и Provisional
    AddTempCol2 = Table.AddColumn(NrIsUnique, "Temp2", each [Key] & "|" & [Provisional]),

    // Проверка уникальности комбинации Key и Provisional
    ProvIsUnique = Table.AddColumn(AddTempCol2, "ProvIsUnique", each if [Unique Key]=null and [NrIsUnique]=null then if List.Count(List.Select(AddTempCol2[Temp2], (x) => x = [Temp2]))=1 and [Provisional]="N" then "Y" else null else null),

    // Добавление столбца с результирующей меткой
    AddValidRows = Table.AddColumn(ProvIsUnique, "VALID ROWS", each Text.Combine({[Unique Key], [NrIsUnique], [ProvIsUnique]})),

    // Удаление временных столбцов для чистоты данных
    FinalTable = Table.SelectColumns(AddValidRows,{"Key", "Nr", "Provisional", "VALUE", "VALID ROWS"})

in
    FinalTable

Применение

Этот код предназначен для использования в Power Query в Microsoft Excel или Power BI. Он позволит вам более автоматизированно и точно фильтровать данные, что является важным аспектом в бизнес-аналитике, где точность данных имеет критическое значение. Точно определив критерии уникальности, вы сможете избежать дублирования данных и повысить качество анализа.

Комплексность и гибкость Power Query делают его важным инструментом для аналитиков, работающих с большими объёмами данных, предоставляя свободу в создании условий и фильтров, которые сложно реализовать напрямую средствами Excel. Использование Power Query в вашей рутине работы с данными не только улучшает эффективность работы, но и расширяет возможности для дальнейшего анализа.