Формулы в Power Query

Введение в Power Query и его возможности

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

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

Основные типы данных и их преобразования

В Power Query существует несколько основных типов данных, которые важно понимать для эффективного использования формул:

• Текст: строки символов, такие как имена или адреса. Текстовые данные часто требуют очистки и стандартизации, например, удаление лишних пробелов или приведение к единому регистру.
• Числа: целые числа и числа с плавающей запятой. Числовые данные могут потребовать округления, преобразования форматов или выполнения арифметических операций.
• Дата и время: даты, время и их комбинации. Работа с датами и временем включает операции по извлечению отдельных компонентов (год, месяц, день) и вычислению разницы между датами.
• Логические значения: TRUE или FALSE. Логические значения используются в условиях и фильтрах для выполнения различных проверок.
• Списки и таблицы: коллекции данных, которые можно обрабатывать как единое целое. Списки и таблицы позволяют выполнять агрегатные функции, такие как суммирование или нахождение среднего значения.

Преобразование типов данных

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

Пример преобразования типа данных:

1. Выберите столбец, который нужно преобразовать.
2. Перейдите на вкладку "Преобразование".
3. Выберите нужный тип данных из выпадающего списка.

Часто используемые формулы и их примеры

Формула Text.Proper

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

Пример:

Text.Proper("power query") // Результат: "Power Query"

Формула Number.Round

Формула Number.Round используется для округления чисел до заданного количества знаков после запятой. Это особенно полезно при работе с финансовыми данными или данными, требующими точности до определенного знака.

Пример:

Number.Round(123.456, 2) // Результат: 123.46

Формула DateTime.LocalNow

Формула DateTime.LocalNow возвращает текущую дату и время. Это полезно для временной метки данных или для выполнения операций, зависящих от текущего времени.

Пример:

DateTime.LocalNow() // Результат: текущая дата и время

Формула List.Sum

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

Пример:

List.Sum({1, 2, 3, 4, 5}) // Результат: 15

Формула Table.AddColumn

Формула Table.AddColumn используется для добавления новых столбцов в таблицу. Это полезно для создания вычисляемых столбцов, которые зависят от значений других столбцов.

Пример:

Table.AddColumn(Source, "NewColumn", each [OldColumn] * 2) // Результат: добавляет столбец с удвоенными значениями

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

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

Пример пользовательской формулы

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

Пример:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    AddCustom = Table.AddColumn(Source, "HighSale", each if [Sales] > 1000 then "Yes" else "No")
in
    AddCustom

Редактирование пользовательских формул

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

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

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    AddCustom = Table.AddColumn(Source, "HighSale", each if [Sales] > 1500 then "Yes" else "No")
in
    AddCustom

Практические советы и лучшие практики

Используйте комментарии в формулах

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

Пример:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    // Добавляем колонку с проверкой на высокие продажи
    AddCustom = Table.AddColumn(Source, "HighSale", each if [Sales] > 1000 then "Yes" else "No")
in
    AddCustom

Оптимизируйте производительность

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

Пример оптимизации:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    FilteredRows = Table.SelectRows(Source, each [Sales] > 1000),
    Summarized = Table.Group(FilteredRows, {"Region"}, {{"TotalSales", each List.Sum([Sales]), type number}})
in
    Summarized

Проверяйте результаты на каждом шаге

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

Сохраняйте оригинальные данные

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

Используйте шаблоны и повторное использование кода

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

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