Дубликаты - очень частая проблема данных. Почти в каждой таблице есть повторяющиеся строки и важно научиться правильно от них избавляться. С помощью Power Query можно быстро удалить дубликаты и получить уникальные записи.
Пример: удалить дубликаты городов из списка филиалов.
Загрузим данные в Power Query в Excel. Проще всего для этого превратить нашу таблицу в "умную" кнопками CTRL + T. После этого ставим курсор мышки на любую ячейку таблицы, переходим на вкладку "Данные" и нажимаем кнопку "Из таблицы/диапазона":
В появившемся окне запросов Power Query видим загруженный список городов:
В Power Query можно удалить повторения несколькими способами:
- команды на ленте.
- команды в контекстном меню.
Способ 1. На вкладке Главная выбираем "Сократить строки" – "Удалить строки" – "Удалить дубликаты":
Способ 2. Правой кнопкой мыши вызываем контекстное меню, выбираем команду "Удалить дубликаты":
В результате получаем список только с уникальными городами:
Осталось выгрузить результаты на лист Excel через вкладку Главная - Закрыть и загрузить:
Если данные в исходной "умной" таблице изменятся или к ней будут дописаны новые строки, достаточно будет обновить запрос:
Пока всё кажется простым, но при работе с дублями в Power Query есть свои нюансы.
Регистр текста и пробелы
Power Query различает строчные и прописные буквы - для него Москва и москва – это два разных города. Поэтому, перед удалением дубликатов лучше привести всё в верхний или в нижний регистр:
Чтобы лишние пробелы не мешали, перед удалением повторов используйте преобразование "Усечь". Так вы избавитесь от лишних пробелов в начале или конце текста.
Для устранения лишних пробелов в середине текста можно сделать замену нескольких пробелов на один.
Проблема сортировки
Важный момент: при удалении дублей может не учитываться примененная вами ранее сортировка.
Пример: вам нужно получить таблицу с наименьшими суммами транзакций за каждый день. Для этого вы отсортировали даты по возрастанию, а для каждой даты расположили суммы от меньшей к большей.
Теперь нужно удалить повторяющиеся даты с учётом добавленной сортировки. Но удаление дубликатов приведет к удалению элементов с учётом их первоначального порядка. Если данные были первоначально загружены с сортировкой по убыванию - тут будет работать именно она. Ваша новая сортировка по возрастанию учтена не будет.
Анонсы всех видео, статей и полезностей - в нашем Telegram🔥
Присоединяйтесь, обсуждайте и автоматизируйте!
Обходное решение 1: перед удалением повторов создадим столбец индекса, чтобы зафиксировать сортировку столбцов.
Для этого на вкладке Добавление столбца выбираем команду "Столбец индекса". Можно добавлять индекс от нуля или единицы - это не важно:
Появляется дополнительный столбец с индексами:
Далее смело выполняем шаг "Удаление дубликатов", при этом требуемая сортировка сохранится:
Чтобы получить красивую таблицу, удаляем уже не нужный нам столбец индекса:
Обходное решение 2: перед удалением повторов буферизуем таблицу. Можно заставить Power Query удалять дубликаты в соответствии с текущим порядком сортировки, используя функцию Table.Buffer.
На практике это выглядит как изменение функции Table.Distinct на шаге Удаления дубликатов. Вместо обычного Table.Distinct, который выглядит так:
мы выполняем Table.Distinct уже от буферизованной таблицы:
Table.Buffer загружает таблицу в память в текущем состоянии и это сбрасывает порядок, который был при загрузке таблицы в Power Query.
Заключение
В этой статье разобрали способы удаления дубликатов, а также все нюансы и обходные решения для устранения проблем с дублями.
Перед удалением дублей проведите операцию усечения пробелов, приведите в порядок регистр и не забудьте зафиксировать порядок сортировки. Если забыли зафиксировать порядок заранее, просто добавьте буферизацию таблицы в шаг удаления дублей. Это избавит вас от частых ошибок, связанных с дубликатами.
Комментарии