What you want to do is
Right-click the ID column, Group By…
Click [Advanced]
It should default to Count Rows in the first row at bottom
[Add Aggregation] with New Column Name ‘All» and operation ‘All Rows’
Hit [OK]
Click on arrows at top of All column and [x] the ID column and any other columns that may have been there, here assumed to be called Other
The count column will be >1 for dupe rows.
Then you can remove extra columns and filter if wanted
Or add a custom column with formula
=if [Count]>1 then "Dupe" else "Single"
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Count", each Table.RowCount(_), type number}, {"All", each _, type table}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"ID", "Other"}, {"ID.1", "Other"})
in #"Expanded All"
Зачем нужен Power Query
Как установить Power Query
Как его Настроить
Как изменить запрос
on
September 6, 2022, 8:21 AM PDT
How to find duplicates using Microsoft Power Query
Microsoft Power Query requires less specialized knowledge when trying to find duplicate records in a Microsoft Excel sheet.
We may be compensated by vendors who appear on this page through methods such as affiliate links or sponsored partnerships. This may influence how and where their products appear on our site, but vendors cannot pay to influence the content of our reviews. For more info, visit our Terms of Use page.
There are several ways to find duplicates in a Microsoft Excel sheet. You can use functions, conditional formatting, filtering and more. Microsoft Excel even offers a built-in feature that will find and delete duplicates for you. If you’re working with a large amount of data or importing data into Power BI, you might want to consider using Power Query to denote duplicates, without deleting them.
In this tutorial, I’ll show you how to use Microsoft Power Query to find duplicates in Excel data. The benefit is that the process requires no special knowledge of functions or conditional formatting. You can download the demo for this Power Query tutorial.
SEE: Feature comparison: Time tracking software and systems (TechRepublic Premium)
I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions through Excel 2010.
How to connect to Power Query
The first step to using Power Query with Excel data is to make a connection between the two, which is simple; however, Power Query requires that you format the data as a Table object. You don’t have to worry about it though because when you start the process, Excel will prompt you to convert a data range into a Table if necessary.
Next, you identify the Excel data, which contains duplicate records, as shown in Figure A, by doing the following:
- Click anywhere inside the Table or data range and then click the Data tab. If prompted to create a Table, click OK.
- In the Get & Transform Data group, click From Table/Range.
Figure A
That’s it. As you can see in Figure B, the data is now in Power Query.
Figure B
With the data in Power Query, it’s time to find the duplicates. There are two duplicate rows, which you can easily find by sorting, but you don’t have to sort when using Power Query.
How to label duplicates in Power Query
We don’t want to remove the duplicates. Instead, we want to label them in some way. We’ll add a new column that identifies duplicates in some way. This process is simpler than you might think.
First, we need to add an index column, which will make more sense later. For now, do the following:
- Click the Add Column tab.
- From the Index Column dropdown in the General group, choose From Zero.
Figure C
As you can see in Figure C, Power Query added an index column — a column of consecutive values that begin with 0.
Before we can identify duplicates, we must determine what constitutes the duplicate. We’ll use an advanced group based on the Date, Value and Personnel columns. The other two columns are irrelevant. Statistically, it isn’t impossible that the same employee might make two sales on the same day of the same value, but it’s not likely. In our simple data set, this is the best we can do because there isn’t a column that uniquely identifies each record, such as an invoice number.
First, we need to create this group:
- Select the Date, Value and Personnel rows by holding down Shift while you click each header.
- Click the Transform tab and then click Group By in the Table group. In the resulting dialog, Power Query populates the first three dropdowns with the selected column names.
- Name the column Find Duplicates and choose Count Rows from the Operation dropdown.
- Click Add Aggregation.
- Name the column Find Duplicates 2 and choose All Rows from the dropdown (Figure D).
- Click OK.
Figure D
Figure E
As you can see in Figure E, the Find Duplicates column returns the value 2 if the record has a duplicate. The Find Duplicates 2 column returns the term Table, which we’ll take care of later. What this query doesn’t do is display the duplicate records.
Currently, the table returns only unique records, which isn’t what we want. We want to keep all records, which is a simple task:
- Click the Expand button for the Find Duplicates 2 column.
- Uncheck the columns that make up the group: Date, Value and Personnel.
- Uncheck the Use Original Column Name As Prefix option if necessary (Figure F).
- Click OK.
Figure F
Figure G
Now you can see all the records, as shown in Figure G. Currently, Power Query identifies duplicates with the value 2 in the Find Duplicates column and displays all duplicates. However, if you scroll to the right, you can see that the Index column is out of order. Obviously, Power Query sorted the records.
Earlier, I mentioned that adding this column would make sense later. This column allows you to maintain the original order, if necessary. Simply resort the Index column by clicking the Index column’s dropdown and choosing Sort Ascending. You can remove the Index column, but I’ll leave it.
With the duplicates identified and all the records visible, you can load the data to Excel.
How to load the data to Excel
Loading the data to Excel is a quick and easy step. On the Home tab, click Close and Load in the Close group. Then click Close and Load from the resulting dropdown. Power Query creates a new sheet based on the Table’s name, which in this case is TableSales, as shown in Figure H.
Figure H
Once the data is back in Excel, you can use it the way you would any other data in Excel. You might want to add a conditional format that highlights the duplicates based on the Find Duplicates values of 1 and 2. The process for labeling the duplicates is easy and gives you the flexibility to display them or not in Excel.
Also See
-
Windows 11 cheat sheet: Everything you need to know
(TechRepublic) -
Get the most out of Excel with these 13 courses
(TechRepublic Academy) -
Software usage policy
(TechRepublic Premium) -
Best software for businesses and end users
(TechRepublic on Flipboard)
-
Microsoft
-
Software
let
Table3 = Table.Distinct(Table1 & Table2),
Custom2 = Table.Repeat(Table3,100),
#"Added Index" = Table.AddIndexColumn(Custom2, "Index", 0, 1),
Grouped = Table.Group(#"Added Index", {"A1", "A2", "A3", "A4", "A5", "A6"}, {{"idx", each _[Index], type list}}),
Merged = Table.Join(#"Added Index", {"A1", "A2", "A3", "A4", "A5", "A6"}, Grouped, {"A1", "A2", "A3", "A4", "A5", "A6"}),
a = Table.AddColumn(Merged, "Duplicated", each [
a = Table.FromColumns({List.RemoveMatchingItems([idx],{[Index]})},{"a"}),
b = Table.AddIndexColumn(a, "b"),
c = Table.Group(b, {"a","b"},
{{"c",
(_)=>let
rc = Table.RowCount(_),
aa = if rc=1 then [a] else List.RemoveRange([a],1,rc-2),
d = if rc=2 then "," else "-"
in
Text.Combine(List.Transform(aa,Text.From),d), type text
}},0,
(a,b)=>if b[a]-a[a]>b[b]-a[b] then 1 else 0
),
d = Text.Combine(c[c],",")
][d])
in
a
Power Query, он же редактор запросов (Query Editor)
В программе Power BI Desktop Power Query встроенный модуль. В справке и интерфейсе этот модуль называется Редактор Запросов или Query Editor . Power Query это основной инструмент для получения данных в модель данных Power BI desktop. Как показано на скриншоте ниже все выделенные кнопки относятся к редактору запросов Power Query.
Power Query в Excel 2010, 2013
Также, Power Query это надстройка над MS Excel 2010 — 2013. Она устанавливается дополнительно. Скачать надстройку можно по ссылке. На панели Ribbon в Excel 2010 и 2013 Power Query посвящена отдельная вкладка.
Power Query в Excel 2016, 2019, 365
В Excel 2016 и последующих версиях Power Query встроена. Найти ее можно на вкладке Data (Данные), блок «Get and Transform». Скриншоты, как Power Query выглядит в различных версиях офиса приведены ниже:
В зависимости от версии подписки Excel функционал Power Query может различаться.
Зачем нужен Power Query
Power Query нужен для удобного преобразования данных (ETL-процесса).
Согласно википедии — ETL (от англ. Extract, Transform, Load) — процесс в управлении хранилищами данных, который включает в себя:
- извлечение данных из внешних источников;
- их трансформация и очистка, чтобы они соответствовали потребностям бизнес-модели;
- и загрузка их в хранилище данных.
Power Query отлично подходит для задач:
- подключения к разнообразным источникам (различным типам файлов, api, базам данных и т.п.);
- для удобного и гибкого преобразования данных в необходимый формат;
- Для создания повторяемых последовательностей обработки данных.
Что такое запрос (Query)
Запрос (Query) это программа на языке M, задающая последовательность обработки данных.
В своем теле запрос может обращаться к неограниченному количеству источников данных (включая другие запросы). Результат выполнения запроса может иметь любой из поддерживаемых структурированнных или примитивных типов данных, например:
- таблица — table
- значение — value,
- список — list,
- запись — record и т.д.
Список из всех запросов в Excel можно увидеть в разных местах.
В списке из запросов книги (Queries Pane) интерфейсе Excel 2016:
В интерфейсе самого Power Query:
У каждого запроса есть свое имя.
Имя запроса можно увидеть в нескольких местах. Там же его можно изменить:
В интерфейсе Excel 2016.
В интерфейсе Power Query.
По имени запроса можно обращаться к результатам этого запроса из других запросов.
В случае, если имя запроса содержит пробелы (например, состоит из нескольких слов), то при обращении к этому запросу из других запросов он начинается с # и заключается в кавычки:
Действия над запросами (по правому щелчку мыши на них)
Duplicate (Дублировать)
Команда Duplicate позволяет создать новый запрос и продублировать в нем все шаги исходного запроса (т.е. при дублировании появляется новый запрос с #"Имя (2)", в котором содержатся все шаги из исходного запроса). Новый запрос, созданный при использовании команды Duplicate, никак не связан с оригинальным запросом.
Reference (Сослаться)
Создать новый запрос, в первом шаге которого обратиться по имени к оригинальному запросу.
Шаг (Step)
Отдельный этап обработки данных в рамках конкретного Запроса.
Запрос состоит из шагов и включает как минимум один шаг.
На каждое примененное действие в интерфейсе создается новый шаг.
Список шагов конкретного запроса можно посмотреть в правой части экрана в панели настроек Запроса.
Каждый шаг это отдельная переменная, расположенная на отдельной строчке кода в скрипте запроса на языке программирования M.
Чтобы посмотреть формулу конкретного шага необходимо включить отображение строки формул на вкладке view и выбрать интересующий шаг в панели «Applied Steps».
Очередность шагов можно менять через интерфейс, используя контекстное меню.
А также перетаскивая шаги в списке.
Параметры в Power Query
- 🇺🇲Статья о параметрах Power Query
Получение данных в Power Query
Чтобы начать работать с Power Query, необходимо настроить получение данных из какого-либо источника. Сделать это можно из интерфейса Power Query в Power BI по нажатию на кнопку Get Data.
В Excel 2010-2013 сделать это можно нажав на кнопки с указанием различных источников на панели Ribbon.
А также из интерфейса Power Query в Excel.
Типы данных в Power Query
В Power Query существуют примитивные и структурированные типы данных. Примитивные типы данных содержат в себе лишь одно значение, в то время как структурированные типы данных могут содержать в себе как одно так и множество значений. В зависимости от заданного типа данных
Примитивные типы данных Power Query
- Decimal number — десятичное число
Number.Typeилиtype number - Time — время
Time.Typeилиtype time - Date — дата
Date.Typeилиtype date - Date / Time — дата / время
DateTime.Typeилиtype datetime - Date / Time / Timezone — дата / время / часовая зона
DateTimeZone.Typeилиtype datetimezone - Duration — длительность
Duration.Typeилиtype duration - Fixed Decimal number — десятичное округленное до 4 знака
Currency.Type - Whole number — целое число
Int64.Type - Text — текст
Text.Typeилиtype text - True/False — истина / ложь
- Binary — двоичный код (например, изображение в формате bmp)
- Percentage — проценты
Percentage.Type
Структурированные типы данных в Power Query
- Table — таблица
#table({"Заголовок1", "Заголовок2"}, { {"Строка1Столбец1", "Строка1Столбец2"}, {"Строка2Столбец1", "Строка2Столбец2"} }) - List — список
{1,2,4} - список из элементов Чисел со значениями 1, 2, 4 - Record — запись
[field1 = "текст в кавычках", field2 = "текст в кавычках2"]
Задание типов данных для столбцов в Power Query
Присвоенные отдельным столбцам типы данных определяют операции, которые применимы к ним. Если выбрать столбец, то примененные к нему типы данных можно увидеть в следующих местах:
- Типы данных столбцов таблицы обозначаются иконками в области заголовков:
- При выбранном столбце, его тип данных в Power BI и Excel на вкладке «Home»
- При выбранном столбце, его тип данных в Power BI и Excel на вкладке «Transform»
Автоматическое определение типов данных для столбцов
В Power Query есть функция «автоматически определять типы данных». При помощи данной функции Power Query будет подбирать тип данных к столбцу на основ первой тысячи строчек конкретного столбца.
Создание дубликата столбца
Команда на Ribbon:
Команда в контекстном меню:
Переименование столбцов
Чтобы переименовать столбец нужно дважды щелкнуть на его названии:
Remove Other Columns — удаление прочих столбцов
Для повышения личной эффективности (за счет лучшего фокуса на конкретных цифрах), сохранения оперативной памяти и поддержки быстродействия моделей следует оставлять в модели данных только необходимые данные (только необходимые столбцы и строчки).
Для этих целей отлично работает команда «remove other columns» (удалить прочие столбцы).
Split Column by Delimeter — Разделить текстовый столбец по разделителю
Команду «разделить столбец по разделителю» можно найти в нескольких местах:
Кнопка на Ribbon — Split Column
В контекстном меню, по щелчку на заголовок столбца.
Разделить столбец по произвольному разделителю
Указать максимальное количество столбцов
Действия над таблицами и столбцами таблиц
Append — добавление одной таблице к другой таблице
Из интерфейса Power Query:
Из интерфейса Excel:
Merge — соединение данных одного запроса с другим запросом по общему ключу (аналог ВПР)
Начало операции из интерфейса Power Query:
Начало операции merge из интерфейса Excel:
Выбор таблицы, из которой будем подтягивать данные, определение ключевых столбцов и типа операции
Соединение данных по составному ключ в Power Query
После нажатия на кнопку OK мы видим новый столбец с кнопкой
Нажимаем на кнопку, раскрываем столбец и выбираем желаемую операцию
Expand — развернуть данные из выбранных столбцов
Aggregate — подсчитать данные в конкретных столбцах
Важно помнить что типы данных у ключевых столбцов (в обеих таблицах) должны быть одинаковыми.
Команда Group by (сгруппировать по полю)
Команду можно вызвать по клику на кнопку на панели Ribbon
Также команду можно вызвать из контекстного меню (если нажать правой кнопкой на заголовке столбца)
Интерфейс команды Group By с комментариями представлен на скриншоте ниже:
Добавление нового столбца в Power Query
if then else условия
Для выбора действия в зависимости от условия в Power Query используется структура с оператором if then else
Пример:
if [столбец1] 0 then [столбец2] else [столбец3]
Условный столбец (Conditional column)
Мастер добавления столбца с условным значением (условного столбца), формирует значение столбца согласно заданным правилам. Мастер добавления условного столбца можно найти в Power Query, на вкладке добавления столбца, как показано на скриншоте ниже.
Пояснения к мастеру конфигурации условного столбца показаны на скриншоте ниже.
Для сведения — задаваемые условия в интерфейсе мастера пишут команду скрипта содержащего операторы if then else. Это значит, что первым будет проверено первое условие в мастере условного столбца. Все множество значений будет проверено по этому условию. В случае, если будут найдены результаты удовлетворяющие первому условию, то для них будет присвоено значение по результатам выполнения первого условия. Для остальных значений из множества, будет проверено следующее условие. И так далее, до тех пор, пока не будут проверенны все условия. Если для элементов множества не будет выполненно ни одно условие, то в условный столбец попадет значение из поля «В противном случае / (otherwise)».
При создании условий важно помнить, операции сравнения включающие type null и другие типы данных, возвращают ошибку в качестве результата. Подробнее об этом можно прочитать в блоге у Максима Зеленского
Изменение типа данных у столбца с текстового на десятичный, в случае если в качестве разделителя десятичной части используется точка вместо запятой
- Необходимо щелкнуть правой кнопкой на заголовке столбца
- Выбрать пункт «Change type»
- И далее выбрать пункт «Using locale»
- Выбираем страну, где в качестве разделителя используется точка (например, USA)
Удалить дубликаты в столбцах
Команда «Remove Duplicates» проходит по выбранным столбцам (если выбрана вся таблица, то по всей таблице) и смотрит в них повторяющиеся ячейки (строчки, в случае если выбрана таблица). Если дубликаты найдены функция оставляет первую попавшуюся уникальную строчку и удаляет все последующие повторяющиеся Найти команду можно на Ribbon — Home — Remove Duplicates (Удалить дубликаты в выбранных столбцах)
Либо найти команду можно щелкнув правой кнопкой на заголовке одного или нескольких выбранных столбцов. В случае, если выбраны несколько столбцов, то тогда будут удалены все неуникальные сочетания значений в каждой отдельной строчке в выбранных столбцах.
Удалить дубликаты строк в таблице можно нажав на кнопку в левом верхнем углу таблицы предпросмотра.
Аналогичного результата можно добиться если использовать команду «Group By»
Count rows — Подсчитать количество строчек в текущей таблице
Извлечение шагов в отдельный запрос
Для выполнения необходимо щелкнуть правой кнопкой на конкретном шаге обработки. Выбрать пункт меню «Extract previous steps»
ввести имя нового запроса, который будет создан на основе предыдущих шагов
Функция Сохранить строки (Keep Top Rows)
Функция Сохранить ошибки (Keer Errors)
Функция Заменить ошибки в столбце (Replace Errors)
Функция доступна по нажатию правой кнопкой на заголовке столбца и позволяет заменить ошибки в столбце (например, получившиеся после применения нового типа данных) на выбранное значение. Обратите внимание, что по состоянию на 2019-07-29 функция доступна лишь при выборе одного столбца.
Получение данных из различных источников
Получение данных из файлов
Получение данных из текстовых файлов (csv, tsv, txt и т.д.)
При получении данных из текстового файла в Power Query открывается окно мастера настроек импорта файла.
Извлечение данных из файлов лежащих в папке
Выбираем в качестве типа источника папку. Далее выбираем конкретную папку с файликами, которые предполагается объединить. Файлики должны быть одного типа и с одинаковыми столбцами.
В появившемся окошке предпросмотра данных жмем на кнопочку edit.
Получение данных из интернета
Права доступа, Formula.Firewall
Текст взят из Power bi formula firewall privacy settings — marketing-wiki.ru
При работе в Power BI, при обращении к внешним источникам данных вроде различных API могут возникать ошибки вроде: OLE DB or ODBC error: [information is needed in order to combine data]
или Formula.Firewall: Query is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination
Это ошибки, которые возникают из-за встроенного в Power BI Fomrula.Firewall — механизма, который следит, чтобы данные из Power BI передавались только согласно выставленным правилам доступа.
то есть Power Bi пытается защитить нас, чтобы мы случайно не отправили какие-либо данные (вроде токена) на сервер-злоумышленника.
Однако, если мы работаем с API, то нам неминуемо нужно отправлять данные в интернет. Соответственно, чтобы не иметь проблем в этом процессе проще всего в настройках Power BI выключить Formula.Firewall. Это делается в разделе Privacy. Нужно выбрать 3-й пункт — «ignore privacy level settings»
Символы разрыва строки
При помощи операции найти или для разделения ячейки на отдельные строчки можно воспользоваться символами #(cr)#(lf) либо воспользоваться функцией Lines.FromText