Страницы: 1
RSS
Удаление дубликатов из столбца по условию в другом столбце при помощи Power Query
 
Привет всем,

Помогите пожалуйста с таким вопросом.
Есть таблица данных. В одном столбце есть повторяющиеся значения, в другом столбце дата и время, не повторяющиеся.
Нужно средствами Power query убрать дубликаты с самой ранней ( или поздней) датой создания. Было бы круто 2 варианта.
Файл примера во вложении

Заранее спасибо за помощь.
 
Не очень понятно изложено, если дубликатов в первом столбце 3 и более, то вам из всех них нужно:
1.  оставить только один с самой ранней (поздней) датой - этот вопрос  решается банальной группировкой по первому столбцу и указанием мин/макс  значений по датам
2. или нужно убрать один из дубликатов с самой ранней (поздней датой)? Тут чуть по-сложнее, но тоже ничего сверхъестественного.

Вы бы в примере справа показали таблицу, которую нужно получить из исходной - стало бы проще.
Изменено: PooHkrd - 19.10.2017 11:19:28
Вот горшок пустой, он предмет простой...
 
Привет

Скорее первый вариант.
В примере добавил таблицу которую нужно получить из исходной.
 
Группировкой, вот так:
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"№", Int64.Type}, {"Date of creation", type datetime}}),
    #"Сгруппированные строки" = Table.Group(#"Измененный тип", {"№"}, {{"Дата самая поздняя", each List.Min([Date of creation]), type datetime}})
in
    #"Сгруппированные строки"
Но в таком случае, если в таблице будут столбцы с другими данными, которые нужно оставить, то вы их потеряете.
Поэтому добавляю второй запрос, в нем именно помечаются те строки, которые нужно удалить, после чего именно они удаляются.
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"№", Int64.Type}, {"Date of creation", type datetime}}),
    #"__Сгруппированные строки" = Table.Group(#"Измененный тип", {"№"}, {{"Дата самая поздняя", each List.Min([Date of creation]), type datetime}}),
    #"Объединенные запросы" = Table.NestedJoin(#"Измененный тип",{"№", "Date of creation"},#"__Сгруппированные строки",{"№", "Дата самая поздняя"},"NewColumn",JoinKind.LeftOuter),
    #"Развернутый элемент NewColumn" = Table.ExpandTableColumn(#"Объединенные запросы", "NewColumn", {"Дата самая поздняя"}, {"Дата самая поздняя"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Развернутый элемент NewColumn", each ([Дата самая поздняя] <> null)),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Строки с примененным фильтром",{"Дата самая поздняя"})
in
    #"Удаленные столбцы"
Изменено: PooHkrd - 19.10.2017 11:50:04
Вот горшок пустой, он предмет простой...
 
Спасибо большое.

Это как раз то, что мне нужно.
 
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"№", Int64.Type}, {"Date of creation", type datetime}}),
    Grouped = Table.Group(#"Changed Type", {"№"}, {{"T", each Table.Min(_, {"Date of creation"}), type record}}),
    Expanded = Table.ExpandRecordColumn(Grouped, "T", {"Date of creation"}, {"Date of creation"})
in
    Expanded

собственно если изначально столбцов больше, то последний шаг подредактировать
F1 творит чудеса
 
Блин, Максим, опять вы нарушаете мой покой!
Что это за Table.ExpandRecordColumn, из справки МС вообще ничего не понятно.

З.Ы. Вроде вкурил - это вы сгруппировали таблицу из записей, а потом раскрыли записи.
Интересно, спасибо.
Изменено: PooHkrd - 19.10.2017 22:18:46
Вот горшок пустой, он предмет простой...
 
Максим Зеленский, попробовал ваш код слегка модифицировать:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"№", Int64.Type}, {"Date of creation", type datetime}}),
    Grouped = Table.Group(#"Changed Type", {"№"}, {{"T", each Table.Min(_, {"Date of creation"}), type record}}),
    Expanded = Table.FromRecords(Grouped[T])
in
    Expanded

И прогнал на миллионе строк, ваш код отрабатывает за 8-9 сек, моя модификация за 15-17. Неужто собрать таблицу из списка записей настолько дольше чем развернуть поле? Не думал, что такая разница получится, особенно с учетом того, что на выходе получился массив в 1505 строк. :( А то уж больно мне такая форма записи понравилась - столбцы перечислять не нужно. Знаю что и в вашем варианте можно перечисление столбцов легко автоматизировать, но вот мне хотелось так решить вопрос. Будет мне на заметку.
Еще проверил такой вариант:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"№", Int64.Type}, {"Date of creation", type datetime}}),
    Grouped = Table.Group(#"Changed Type", {"№"}, {{"T", each Table.FirstN(Table.SelectRows(_, let latest = List.Max(_[Date of creation]) in each [Date of creation] = latest),1), type table}}),
    Expanded = Table.ExpandTableColumn(Grouped, "T", {"Date of creation"}, {"Date of creation"})
in
    Expanded

на том же массиве отработал за 11 сек, если же не разворачивать столбец, а собирать итог через
Код
Expanded = Table.Combine(Grouped[T])

то за 13 сек.
Вот такая вот загогулина, панимаешь!
Надеюсь кому-то эти замеры помогут с составлением быстрых обработок.
Изменено: PooHkrd - 13.02.2019 12:38:22
Вот горшок пустой, он предмет простой...
 
Я так понимаю, в первом случае вы просто заменили разворот столбца записей на сбор таблицы из записей?
Любопытное наблюдение. Нет под рукой подходящего массива, чтобы перепроверить.

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

Я тоже привык считать, что таблица это скорее список записей. АндрейVG часто демонстрировал преимущества использования Table.ToRecords для последующей обработки с сбором в обратно таблицу в некоторых случаях (и есть подозрение, что большая часть встроенных функций обработки таблиц основана на Table.FromRecords <- List.Transform <- Table.ToRecords), но, судя по вашему тесту, эффективно это не всегда.

По поводу второго варианта - имейте в виду, что ExpandTableColumn всегда будет медленнее ExpandRecordColumn:
Код
Table.ExpandTableColumn = (table, column, columnNames, newColumnNames) as table =>
    Table.ExpandRecordColumn(Table.ExpandListColumn(table, column), column, columnNames, newColumnNames)

Немного парадоксально, не правда ли:
Код
let
    Source = #table(2, List.Zip({{1..5},{6..10}})),
    Custom1 = #table(2, {{1,Source}, {2,Source}}),
    Custom2 = Table.ExpandListColumn(Custom1, "Column2")
in
    Custom2


Третий вариант, подозреваю, эквивалентен чему-то вроде такого:
Код
Table.FromRecords(List.Combine(List.Transform(table[column], Table.ToRecords)))

что тоже не выглядит супер-эффективным (хотя из-за наличия второго аргумента - списка столбцов - в Table.Combine я думаю всё еще навороченнее)
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
Немного парадоксально, не правда ли:
Я вот честно не знаю каким макаром они там свои функции заворачивают, но попробовал вот так:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"№", Int64.Type}, {"Date of creation", type datetime}}),
    Grouped = Table.Group(#"Changed Type", {"№"}, {{"T", each Table.FromRecords({Table.Min(_, {"Date of creation"})}), type table}}),
    Expanded = Table.Combine(Grouped[T])
in
    Expanded

Отработал за 8 секунд, прямо как ваш код! Все таки я его обманул! :D  8)
Вот горшок пустой, он предмет простой...
 
Всем привет!
Не стал, создавать новую тему, вопрос подобный.
Есть исходная таблица в столбце "Значение" есть дубликаты, на основании которых я вывожу связь между наименованиями в столбце "Area-Line-Sheet", то есть проще сказать "Значение" это ключ, который связывает наименования в столбце "Area-Line-Sheet", Но при выводе значений в Таблицу2 у меня образуются повторы в строчку, напротив, в комментария прописал "надо удалить так  как они повторяются в строчку". Пытался припилитьTable.Distinct, но думаю тут нужно трансформировать таблицу
Прошу прощения, пример приложил
Изменено: Adamm - 09.04.2022 09:31:45
 
Adamm, без примера вряд ли кто-то поможет.
 
Может так (кнопочный вариант)
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Area-Line-Sheet", type text}, {"Spool", type text}, {"Значение", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Spool", "Значение"}),
    #"Removed Duplicates1" = Table.Distinct(#"Removed Duplicates", {"Area-Line-Sheet", "Spool"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates1",{"Значение"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"Spool"},Таблица1,{"Spool"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Area-Line-Sheet"}, {"NewColumn.Area-Line-Sheet"}),
    #"Removed Duplicates2" = Table.Distinct(#"Expanded NewColumn", {"Area-Line-Sheet", "NewColumn.Area-Line-Sheet"}),
    #"Removed Duplicates3" = Table.Distinct(#"Removed Duplicates2", {"Spool", "NewColumn.Area-Line-Sheet"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Duplicates3",{"NewColumn.Area-Line-Sheet", "Spool", "Area-Line-Sheet"})
in
    #"Reordered Columns"
 
Adamm, просто отфильтруйте строки с разными значениями в двух столбцах:
Код
  filter = Table.SelectRows ( #"Удаленные дубликаты1", each [#"Area-Line-Sheet"] <> [#"Area-Line-Sheet.1"] )
 
jakim, спасибо, но первая строчка выдает:
2279001-AC273774-3SP03-ZZ2279001-AC273774-3
а должна:
2279001-AC273774-3SP03-ZZ2279001-AC273774-4
Не могу понять где ошибка
Изменено: Adamm - 09.04.2022 11:33:12
 
surkenny, все гениальное просто!
Цитата
написал:
просто отфильтруйте строки с разными значениями в двух столбцах:
А я второй день правду ищу)
Изменено: Adamm - 09.04.2022 11:34:36
Страницы: 1
Наверх