Как найти дубликаты в power bi
Перейти к содержимому

Как найти дубликаты в power bi

  • автор:

Сохранение или удаление повторяющихся строк (Power Query)

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

Удаление повторяющихся строк

  1. Чтобы открыть запрос, найдите ранее загруженную из Редактор Power Query, выделите ячейку в данных, а затем выберите запрос>изменить. Дополнительные сведения см. в статье «Создание, загрузка и изменение запроса в Excel».
  2. Выберите столбец, щелкнув заголовок столбца. Чтобы выбрать несколько столбцов, нажмите клавиши SHIFT+ЩЕЛЧОК или CTRL+ Щелкните каждый последующий столбец. Столбцы могут быть непрерывными или неуявными.
  3. Выберите «Главная >удалить строки>удалить дубликаты.

Сохранение повторяющихся строк

  1. Чтобы открыть запрос, найдите ранее загруженную из Редактор Power Query, выделите ячейку в данных, а затем выберите запрос>изменить. Дополнительные сведения см. в статье «Создание, загрузка и изменение запроса в Excel».
  2. Выберите столбец, щелкнув заголовок столбца. Чтобы выбрать несколько столбцов непрерывно или неопределяемо, нажмите клавиши SHIFT+CLICK или CTRL+Click для каждого последующего столбца.
  3. Выберите home >keep rows >KeepDuplicates (Сохранить повторяющиеся строки).

Работа с повторяющимися значениями

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

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

В этой статье примеры используют следующую таблицу с идентификаторами, категориями и итоговыми столбцами.

Initial sample table.

Удаление дубликатов

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

Remove duplicates.

  1. Выберите столбцы, содержащие повторяющиеся значения.
  2. Перейдите на вкладку «Главная «.
  3. В группе «Уменьшить строки» выберите «Удалить строки«.
  4. В раскрывающемся меню выберите «Удалить дубликаты«.

При удалении повторяющихся экземпляров не гарантируется, что первый экземпляр в наборе дубликатов будет выбран. Чтобы узнать больше о сохранении сортировки, перейдите в раздел «Сохранить сортировку «.

Удаление дубликатов из нескольких столбцов

В этом примере необходимо определить и удалить дубликаты с помощью всех столбцов из таблицы.

Initial table with duplicates in multiple columns.

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

Результат этой операции даст вам таблицу, которую вы ищете.

Final table with duplicates removed from all columns.

Эта операция также может выполняться с подмножеством столбцов.

Удаление дубликатов из одного столбца

В этом примере необходимо определить и удалить дубликаты с помощью только столбца категории из таблицы.

Initial table with duplicates in the Category column.

Вы хотите удалить эти дубликаты и сохранить только уникальные значения. Чтобы удалить дубликаты из столбца категории , выберите его, а затем нажмите кнопку «Удалить дубликаты«.

Результат этой операции даст вам таблицу, которую вы ищете.

Final table with duplicates removed from the Category column.

Сохранение дубликатов

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

Keep duplicates.

  1. Выберите столбцы, содержащие повторяющиеся значения.
  2. Перейдите на вкладку «Главная «.
  3. В группе «Уменьшить строки» выберите «Сохранить строки«.
  4. В раскрывающемся меню выберите «Сохранить дубликаты«.

Сохранение повторяющихся данных из нескольких столбцов

В этом примере необходимо определить и сохранить дубликаты с помощью всех столбцов из таблицы.

Initial table for keeping duplicates from multiple columns.

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

Результат этой операции даст вам таблицу, которую вы ищете.

Final table with only duplicated rows from multiple columns.

Сохранение повторяющихся данных из одного столбца

В этом примере необходимо определить и сохранить дубликаты с помощью только столбца идентификатора из таблицы.

Initial table for keeping duplicates from a single column.

В этом примере имеется несколько дубликатов, и вы хотите сохранить только эти дубликаты из таблицы. Чтобы сохранить дубликаты из столбца идентификатора, выберите столбец идентификатора и нажмите кнопку «Сохранить дубликаты«.

Результат этой операции даст вам таблицу, которую вы ищете.

Удалить дубликаты в 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.

Заключение

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

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

Функции таблиц уникальных значений в DAX: VALUES и DISTINCT в Power BI и Power Pivot

Антон Будуев

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье поговорим про функции в DAX, возвращающие таблицы уникальных значений в Power BI и Power Pivot, а именно, про VALUES и DISTINCT.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

DAX функция VALUES в Power BI и Power Pivot

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

Синтаксис: VALUES (‘Таблица’) или VALUES ([Столбец])

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

В качестве примера возьмем исходную таблицу «СпрМенеджеры», находящуюся в Power BI и содержащую информацию по менеджерам и отделам:

Исходная таблица

Создадим в Power BI Desktop во вкладке «Моделирование» вычисляемую таблицу уникальных значений отделов на основе формулы с участием VALUES:

Таблица = VALUES ('СпрМенеджеры'[Отдел])

Как результат, мы увидим столбец с уникальными значениями отделов:

Результат работы формулы на основе функции VALUES

Саму по себе функцию VALUES, как на примере выше, используют достаточно редко. Обычно в формулах она используется в партнерстве с другими DAX функциями.

Например, мы можем в рамках одной формулы объединить VALUES с функцией COUNTROWS, которая считает количество строк. Тем самым, мы можем создать меру, вычисляющую на основе исходной таблицы «СпрМенеджеры» количество отделов в организации:

Количество Отделов = COUNTROWS ( VALUES ('СпрМенеджеры'[Отдел]) )

Результатом выполнения кода этой формулы будет количество отделов, равное 3:

Результат работы формулы с участием функции VALUES

DAX функция DISTINCT в Power BI и Power Pivot

DISTINCT () — DAX функция, которая возвращает столбец с уникальными значениями исходного столбца или таблицы. В своей практике я эту функцию очень часто использую при создании так называемых таблиц справочников (измерений) на основе исходного столбца из таблицы фактов.

Таблицы фактов обычно содержат очень большой объем данных (миллионы строк), которые могут повторятся. Например, таблица фактов «Заявки», в которой в каждой строке указана информация по одной заявке — сумма, затраты, менеджер, дата сделки и т.д.

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

Синтаксис: DISTINCT ([‘Таблица’]) или DISTINCT ([Столбец])

В качестве примера работы функции DISTINCT напишем простую формулу, которая создаст в нашей модели данных справочник по менеджерам на основе исходной таблицы фактов «Заявки»:

Исходная таблица фактов Заявки

Итак, код формулы в DAX достаточно простой:

СпрМенеджеры = DISTINCT ('Заявки'[Менеджер])

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

Таблица справочник по менеджерам, созданная на основе функции DISTINCT

Далее, в Power BI Desktop во вкладке «Связи» можно будет объединить все таблицы, содержащие столбец [Менеджер], на основе созданного функцией DISTINCT, справочника «СпрМенеджер»:

Объединение связью нескольких таблиц на основе таблицы, созданной функцией DISTINCT

В результате использования функции DISTINCT, мы смогли создать справочник с уникальными значениями менеджеров, при помощи которого, объединили две таблицы фактов «Заявки» и «Дополнительные затраты».

На этом, с разбором DAX функций VALUES и DISTINCT в Power BI и Power Pivot, в этой статья все.

Пожалуйста, оцените статью:

  1. 5
  2. 4
  3. 3
  4. 2
  5. 1

[Экспресс-видеокурс] Быстрый старт в языке DAX

Антон Будуев

Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

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

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

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

Что еще посмотреть / почитать?

DAX функции MAX и MIN

DAX функции MAX, MAXA, MAXX и MIN, MINA, MINX (для Power BI и Power Pivot)

DAX функции YEARFRAC, WEEKDAY и WEEKNUM

DAX функции YEARFRAC, WEEKDAY и WEEKNUM в Power BI и PowerPivot

Первая дата продажи

Вычисляем на языке DAX в Power BI и Power Pivot дату первой продажи товара

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *