Удалить дубликаты в Power Query: пробелы, регистр, правила сортировки

Удалить дубликаты в Power Query: пробелы, регистр, правила сортировки

Дубликаты - очень частая проблема данных. Почти в каждой таблице есть повторяющиеся строки и важно научиться правильно от них избавляться. С помощью Power Query можно быстро удалить дубликаты и получить уникальные записи.

Пример: удалить дубликаты городов из списка филиалов.

Загрузим данные в Power Query в Excel. Проще всего для этого превратить нашу таблицу в "умную" кнопками CTRL + T. После этого ставим курсор мышки на любую ячейку таблицы, переходим на вкладку "Данные" и нажимаем кнопку "Из таблицы/диапазона": 

В появившемся окне запросов Power Query видим загруженный список городов:

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

  1. команды на ленте.
  2. команды в контекстном меню.

Способ 1. На вкладке Главная выбираем "Сократить строки" – "Удалить строки" – "Удалить дубликаты":

Способ 2. Правой кнопкой мыши вызываем контекстное меню, выбираем команду "Удалить дубликаты":

В результате получаем список только с уникальными городами:

Осталось выгрузить результаты на лист Excel через вкладку Главная - Закрыть и загрузить:

Если данные в исходной "умной" таблице изменятся или к ней будут дописаны новые строки, достаточно будет обновить запрос:

Пока всё кажется простым, но при работе с дублями в Power Query есть свои нюансы.

Регистр текста и пробелы

Power Query различает строчные и прописные буквы - для него Москва и москва – это два разных города. Поэтому, перед удалением дубликатов лучше привести всё в верхний или в нижний регистр:

Чтобы лишние пробелы не мешали, перед удалением повторов используйте преобразование "Усечь". Так вы избавитесь от лишних пробелов в начале или конце текста. 

Для устранения лишних пробелов в середине текста можно сделать замену нескольких пробелов на один. 

Проблема сортировки

Важный момент: при удалении дублей может не учитываться примененная вами ранее сортировка.

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

Теперь нужно удалить повторяющиеся даты с учётом добавленной сортировки. Но удаление дубликатов приведет к удалению элементов с учётом их первоначального порядка. Если данные были первоначально загружены с сортировкой по убыванию - тут будет работать именно она. Ваша новая сортировка по возрастанию учтена не будет.

Обходное решение 1: перед удалением повторов создадим столбец индекса, чтобы зафиксировать сортировку столбцов.

Для этого на вкладке Добавление столбца выбираем команду "Столбец индекса". Можно добавлять индекс от нуля или единицы - это не важно:

Появляется дополнительный столбец с индексами:

Далее смело выполняем шаг "Удаление дубликатов", при этом требуемая сортировка сохранится:

Чтобы получить красивую таблицу, удаляем уже не нужный нам столбец индекса:

Обходное решение 2: перед удалением повторов буферизуем таблицу. Можно заставить Power Query удалять дубликаты в соответствии с текущим порядком сортировки, используя функцию Table.Buffer. 

На практике это выглядит как изменение функции Table.Distinct на шаге Удаления дубликатов. Вместо обычного Table.Distinct, который выглядит так:

мы выполняем Table.Distinct уже от буферизованной таблицы:

Table.Buffer загружает таблицу в память в текущем состоянии и это сбрасывает порядок, который был при загрузке таблицы в Power Query.

Заключение

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

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

Комментарии