Power Query: удаление строк по условию – подробная инструкция

Для кого эта статья:

• аналитики данных и BI-специалисты
• студенты и начинающие специалисты в области анализа данных
• практикующие аналитики, ищущие способы оптимизации обработки данных

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

Хотите освоить Power Query на профессиональном уровне? Курс «BI-аналитик» от SkyPro погружает студентов в тонкости работы с данными через практические задачи. Вы научитесь не только удалять строки по условию, но и создавать комплексные ETL-процессы, автоматизирующие рутину. Уже через 3 месяца вы будете уверенно трансформировать данные для создания аналитических дашбордов!

Power Query: необходимость удаления строк по условию

Удаление лишних строк — критически важный этап обработки данных, особенно когда вы работаете с большими объемами информации. Почему это так необходимо? Вот ключевые причины:

• 🎯 Повышение производительности — меньше строк означает более быстрый анализ и визуализацию
• 📊 Улучшение качества данных — избавление от аномалий и выбросов делает анализ точнее
• 🧠 Снижение когнитивной нагрузки — удаление нерелевантной информации упрощает интерпретацию данных
• ⚙️ Оптимизация ETL-процессов — фильтрация на ранних этапах сокращает нагрузку на последующие преобразования

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

Михаил Ветров, ведущий BI-аналитик Однажды мне достался проект с данными о продажах крупной розничной сети — более 20 миллионов строк. Отчёты загружались мучительно долго, а визуализации создавали больше вопросов, чем ответов. На поверку выяснилось, что почти 40% строк содержали тестовые данные, отменённые транзакции и дубликаты. Вместо того чтобы фильтровать данные на уровне визуализаций или DAX-формул, я применил Power Query для удаления строк по нескольким условиям: тестовые ID магазинов, транзакции со статусом "отменено", продажи с нулевой суммой. Результат превзошёл ожидания: отчёты стали загружаться в 3 раза быстрее, а высвободившиеся вычислительные ресурсы позволили добавить более сложные меры и визуализации, которые раньше просто "подвешивали" систему. Урок прост — фильтруйте данные на самом раннем этапе, который возможен. Power Query идеально подходит для этой задачи.

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

Базовые методы удаления строк в Power Query

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

1. Удаление через контекстное меню

Самый простой способ — выделить нежелательные строки и удалить их через контекстное меню:

1. Выделите одну или несколько строк (используйте Ctrl или Shift для множественного выбора)
2. Нажмите правую кнопку мыши
3. Выберите "Удалить строки"

Этот метод удобен для небольших наборов данных, когда вы точно знаете, какие именно строки нужно удалить. Однако он не масштабируется для больших объемов данных.

2. Фильтрация данных через встроенные фильтры

Более практичный подход — использование встроенных фильтров:

1. Нажмите на стрелку рядом с заголовком нужного столбца
2. В появившемся меню фильтров выберите или снимите отметки с нужных значений
3. Нажмите "OK"

Power Query автоматически добавит шаг "Отфильтрованные строки" в редактор запросов. Этот метод отлично подходит, когда вы хотите удалить строки на основе конкретных значений.

3. Удаление строк через ленту инструментов

Лента Power Query содержит специальные кнопки для быстрого удаления различных типов строк:

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

// Пример кода M, который генерируется при использовании инструмента "Удалить дубликаты"
Table.Distinct(Source, {"ProductID", "Date"})

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

Продвинутые функции фильтрации данных Power Query

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

Фильтрация по расширенным условиям

Расширенные фильтры позволяют создавать сложные условия, используя логические операторы:

1. Нажмите на стрелку рядом с заголовком столбца
2. Выберите "Фильтры текста/чисел/дат" (в зависимости от типа данных)
3. Нажмите "Расширенные фильтры"
4. Создайте условия с помощью операторов "И"/"ИЛИ"

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

Использование функции Table.SelectRows

Наиболее гибкий метод фильтрации — прямое использование функции Table.SelectRows. Эта функция принимает таблицу и условие в виде выражения, которое должно возвращать TRUE или FALSE для каждой строки:

Table.SelectRows(
Source,
each [Sales] > 1000 and [Returns] < 100 and Text.Contains([Customer], "Corp")
)

Для использования этой функции:

1. Нажмите "Расширенный редактор" на вкладке "Вид"
2. Добавьте вызов Table.SelectRows с нужными условиями
3. Закройте редактор, нажав "Готово"

Преимущество этого подхода — практически неограниченные возможности по составлению условий фильтрации.

Динамические фильтры на основе других таблиц

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

Table.SelectRows(
Products,
each List.Contains(
ValidCategories[CategoryID],
[CategoryID]
)
)

Для более сложных сценариев фильтрации по нескольким столбцам можно использовать функцию Table.MatchesAllRows или комбинировать несколько условий с помощью логических операторов.

Комбинируя эти функции, вы можете создавать сложные условия фильтрации, которые будут точно соответствовать вашим аналитическим требованиям.

Не уверены, какая область аналитики вам подойдет? Тест на профориентацию от SkyPro поможет определить, станет ли работа с Power Query и обработка данных вашим призванием. За 5 минут вы получите персонализированные рекомендации по развитию карьеры в аналитике и смежных областях, основанные на ваших навыках и предпочтениях. Возможно, именно вам предстоит создавать интеллектуальные системы фильтрации данных!

Автоматизация удаления строк через пользовательские формулы

Истинная мощь Power Query раскрывается при создании пользовательских формул и функций, которые автоматизируют процесс удаления строк. Такой подход особенно полезен при регулярной работе со схожими наборами данных. 🤖

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

Пользовательские функции — это переиспользуемые блоки кода, которые можно применять к разным таблицам. Вот как создать функцию для удаления строк с отрицательными продажами:

// Определение функции
(table as table) as table =>
let
FilteredTable = Table.SelectRows(table, each [Sales] >= 0)
in
FilteredTable

Для создания такой функции:

1. Нажмите "Создать" → "Пустой запрос" на вкладке "Главная"
2. Откройте "Расширенный редактор"
3. Вставьте код функции
4. Дайте функции информативное название, например "RemoveNegativeSales"

Теперь вы можете применить эту функцию к любой таблице, содержащей столбец "Sales":

RemoveNegativeSales(YourTable)

Параметризация условий фильтрации

Еще более гибкое решение — создать функцию с параметрами, которые определяют условия фильтрации:

// Функция с параметрами
(table as table, columnName as text, minimumValue as number) as table =>
let
FilteredTable = Table.SelectRows(
table, 
each Record.Field(_, columnName) >= minimumValue
)
in
FilteredTable

Такую функцию можно вызывать с разными параметрами:

FilterByMinimum(SalesTable, "Revenue", 1000)
FilterByMinimum(InventoryTable, "Quantity", 5)

Автоматизация очистки через шаблоны запросов

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

1. Создайте запрос с последовательностью шагов очистки
2. Щелкните правой кнопкой мыши на запросе и выберите "Создать функцию"
3. Укажите параметры (обычно это источник данных)
4. Задайте имя функции

Теперь вы можете один раз настроить все необходимые фильтры и преобразования, а затем применять их ко всем новым данным одним щелчком мыши.

Обработка исключений при фильтрации

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

// Фильтрация с обработкой исключений
Table.SelectRows(
Source,
each try [Sales] > 1000 otherwise false
)

Оператор try...otherwise позволяет избежать ошибок при обработке некорректных данных и делает ваши автоматизированные процессы более надежными.

Практические кейсы оптимизации ETL с удалением строк

Анна Соколова, Data Engineer В проекте для крупного ритейлера мы столкнулись с проблемой: бизнес-пользователи тратили по 30-40 минут на загрузку отчетов, в которых использовались данные о продажах за все время существования компании. При этом реально им нужна была информация только за последние 3 года. Мы решили оптимизировать процесс, добавив в ETL-конвейер шаг удаления старых данных. В Power Query это выглядело так:

let
Source = Excel.Workbook(...),
SalesTable = Source{[Name="Sales"]}[Data],
FilteredByDate = Table.SelectRows(
SalesTable,
each [Date] >= Date.AddYears(Date.From(DateTime.LocalNow()), -3)
)
in
FilteredByDate

Простое условие фильтрации сократило объем обрабатываемых данных на 76%, что привело к ускорению загрузки отчетов до 5-7 минут. А поскольку такая фильтрация была встроена в ETL-процесс, все последующие трансформации и вычисления также выполнялись быстрее. Это классический пример того, как раннее удаление ненужных данных экономит ресурсы на всем пути их обработки.

Рассмотрим еще несколько практических сценариев, где удаление строк по условию значительно оптимизирует ETL-процессы.

Кейс 1: Фильтрация лог-файлов по уровню важности

При анализе лог-файлов серверов или приложений часто нужно сосредоточиться только на ошибках определенного уровня важности:

let
Source = Csv.Document(...),
FilteredLogs = Table.SelectRows(
Source,
each ([LogLevel] = "ERROR" or [LogLevel] = "CRITICAL") 
and not Text.Contains([Message], "known_issue")
)
in
FilteredLogs

Такая фильтрация может сократить объем анализируемых данных на 95-99%, оставляя только действительно важную информацию.

Кейс 2: Удаление промежуточных результатов в финансовых отчетах

Финансовые отчеты часто содержат как детализированные, так и агрегированные данные. Для анализа обычно необходимо работать только с одним уровнем детализации:

let
Source = Excel.Workbook(...),
FinancialData = Source{[Name="FinancialReport"]}[Data],
RemoveSubtotals = Table.SelectRows(
FinancialData,
each not Text.EndsWith([AccountCode], "00") 
and not [Description] = null 
and not Text.Contains([Description], "Total")
)
in
RemoveSubtotals

Кейс 3: Исключение выбросов из аналитических данных

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

let
Source = Excel.Workbook(...),
MeasurementsData = Source{[Name="Measurements"]}[Data],
// Расчет статистических показателей
Stats = let
Mean = List.Average(MeasurementsData[Value]),
StdDev = List.StandardDeviation(MeasurementsData[Value])
in
[Mean = Mean, StdDev = StdDev],
// Удаление выбросов (более 3 стандартных отклонений от среднего)
FilteredData = Table.SelectRows(
MeasurementsData,
each [Value] >= Stats[Mean] – 3 * Stats[StdDev] 
and [Value] <= Stats[Mean] + 3 * Stats[StdDev]
)
in
FilteredData

Рекомендации по оптимизации ETL-процессов

На основе рассмотренных кейсов можно сформулировать общие рекомендации по оптимизации ETL с помощью удаления строк:

1. Фильтруйте как можно раньше — удаление ненужных данных на начальных этапах ETL экономит ресурсы при всех последующих трансформациях
2. Используйте индексацию — если ваш источник данных поддерживает индексы, фильтруйте по индексированным полям
3. Комбинируйте условия — объединяйте несколько фильтров в одно выражение для повышения эффективности
4. Документируйте условия фильтрации — используйте комментарии в коде, чтобы объяснить, почему определенные данные исключаются
5. Тестируйте на репрезентативных выборках — проверяйте, что ваши фильтры не отсекают важные данные

Внедрение этих практик позволит значительно повысить производительность ETL-процессов и качество получаемых данных, что в конечном итоге влияет на точность и скорость аналитики.

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