Страницы: 1
RSS
Power Query: поиск дубликатов по определенным столбцам таблицы
 
Всем привет!

Как необходимо изменить запрос в Table3, чтобы в столбце результат через запятую были перечислены  индексы строк дубликатов по колонкам А1-А6?
Файл-пример прикладываю

Спасибо!
 
Цитата
CainV написал:
чтобы в столбце результат через запятую были перечислены  индексы строк дубликатов по колонкам А1-А6
Именно в такой формулировке? Или как в примере перечислить все индексы, исключая индекс текущей строки?
Вот горшок пустой, он предмет простой...
 
PooHkrd, можно и все индексы, включая саму строку, можно и исключая, не принципиально
 
В общем так:
Код
let
    Table3 = Table.Distinct(Table1 & Table2),
    #"Added Index" = Table.AddIndexColumn(Table3, "Index", 0, 1),
    #"Сгруппированные строки" = Table.Group(#"Added Index", {"A1", "A2", "A3", "A4", "A5", "A6"}, {{"сп", each _[Index], type table}, {"таб", each _, type table}}),
    #"Развернутый элемент таб" = Table.ExpandTableColumn(#"Сгруппированные строки", "таб", {"Index", "B1", "B2"}, {"Index", "B1", "B2"}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Развернутый элемент таб", "Индексы", each Text.Combine( List.Transform( List.RemoveItems([сп], {[Index]}), Text.From ), ", " ), type text ),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Добавлен пользовательский объект",{"сп"}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Удаленные столбцы",{"Index", "A1", "A2", "A3", "A4", "A5", "A6", "B1", "B2", "Индексы"})
in
    #"Переупорядоченные столбцы"
Вот горшок пустой, он предмет простой...
 
CainV, Пух уже сделал, только в чем смысл? Что вы дальше с этим будете делать? Глазами искать строки? Можно просто сгруппировать по А1-А6 и добавить две агрегации: считать количество строк, все строки. Далее фильтруем полученный результат, где количество строк получилось больше 1, и разворачиваем прочие столбцы.
 
PooHkrd, спасибо! А можно объяснить как это без Advanced Editor сделать, используя  вкладки PQ? Дело в том, что в исходнике стобцов B1,B2.. и т.д. очень много и я это точно замучаюсь вручную вводить:(
 
StepanWolkoff, спасибо, попробую
 
Цитата
CainV написал:
А можно объяснить как это без Advanced Editor
Как дурак заморочился и сделал без расширенного редактора, и на тебе.  8-0 Так и знал что Table.Combine'ом надо было. :D
Куда вы чего замучаетесь вводить? Там где столбцы раскрывать есть шестеренка справа от названия шага, тыкайте в нее и выбирайте галочкой названия столбцов которые нужно раскрывать после группировки.
Вот горшок пустой, он предмет простой...
 
Еще вариант (not a good idea)
Код
= Table.AddColumn(#"Reordered Columns", "a", each [   
    names = List.Select(Table.ColumnNames(#"Reordered Columns"),each Text.StartsWith(_, "A")),
    a = _, b  = a[Index], c = Table.FromRecords({a}),
    i = Text.Combine(List.Transform(List.Select(Table.NestedJoin(c,names,#"Reordered Columns",names,
   "Table1",JoinKind.LeftOuter)[Table1]{0}[Index], each _<>b),Text.From),", ")][i])
Изменено: Михаил Л - 31.03.2020 13:53:10
 
CainV, вот вам без упоминания столбцов В1 и т.п.
Код
let
    Table3 = Table.Distinct(Table1 & Table2),
    #"Added Index" = Table.AddIndexColumn(Table3, "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"}),
    #"Added Custom" = Table.AddColumn(Merged, "Duplicated", each Text.Combine(List.Transform(List.Difference([idx], {[Index]}), Text.From),","), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"idx"})
in
    #"Removed Columns"
F1 творит чудеса
 
Михаил Л, Вы уверены, что на таблице с 100500 строк выполнять 100500 джойнов к небуфферизированному предыдущему шагу - хорошая идея? или даже к буфферизированному...
F1 творит чудеса
 
PooHkrd, не могли бы объяснить как на шаге "Сгруппированные строки" получился столбец "сп" ? Как получился столбец "таб" я понял - группировка А1-А6 -> All rows
 
Максим Зеленский, очень плохая идея)
Увеличил количество строк до 1200 и запустил запрос. Примерно в секунду по строке. Посмеялся
Изменено: Михаил Л - 31.03.2020 14:22:55
 
Добрый день!

Усложню задачу.
Можно ли в поле индексы в результате работы запроса PQ обозначить через тире границы диапазона, в котором индексы идут подряд. Например, такая конструкция в ячейке:  "7, 8, 10 - 18, 24 - 26". Или это фантастика?
В VBA решаю подобную задачу, а в PQ?
На всякий случай прикрепил файл - пример топикстартера, слегка подкорректированный в части того, что надо получить.  
 
Цитата
quasarrr написал:
обозначить через тире границы диапазона
На основе запроса, который предложил Максим Зеленский
 
Михаил Л, круто. Пошёл разбираться.
 
Михаил Л, одной проверочки не хватает: вместо 5-6, 8-9, по условиям задачи должно быть 5,6,8,9
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
вместо 5-6, 8-9, по условиям задачи должно быть 5,6,8,9
Ну что ж... Жду другие решения
 
Цитата
Михаил Л написал:
Жду другие решения
Не надо другие, берем ваше и чутка допиливаем, я ж говорю одной проверочки не хватает:
Код
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.FromList(List.Transform(List.Difference([idx], {[Index]}), Text.From)),
    b = Table.AddIndexColumn(a, "index"),
    c = Table.AddColumn(b, "b", each Number.From([Column1])-[index]),
    i = Text.Combine(Table.Group(c, {"b"}, 
        {{"a", each let min = List.Min([Column1]), 
                        max = List.Max([Column1]) 
                    in 
                        if min = max then Text.From(min) else Text.From(min) & (if Number.From(max)-Number.From(min) = 1 then ", " else "-") & Text.From(max)}})[a],", ")][i])
in 
    a
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
и чутка допиливаем
Код
= Table.AddColumn(Merged, "Duplicated", each [
    a= Table.FromList(List.Transform(List.Difference([idx], {[Index]}), Text.From)),
    b = Table.AddIndexColumn(a, "index"),
    c = Table.AddColumn(b, "b", each Number.From([Column1])-[index]),
    i = Text.Combine(List.Transform( Table.Group(c, {"b"}, {{"a", each if List.Min
([Column1])= List.Max([Column1]) then Text.From(List.Min
([Column1])) else Text.From(List.Min([Column1]))&"-"& 
Text.From(List.Max([Column1]))}})[a], each if 1=Number.From(Text.AfterDelimiter(_,"-"))-Number.From(Text.BeforeDelimiter(_,"-")) then Replacer.ReplaceText(_,"-",", ") else _),", ")][i])
 
Михаил Л, жестокий вы человек,  :) имея на руках готовые слагаемые собрать из них текст, а потом их из этого же текста выковыривать. Все таки посчитать сразу мин/максы, а потом с ними возиться и в один прогон собрать результат, так быстрее должно работать.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
жестокий
первый раз слышу. Не вдаваясь во весь код, просто добавил условие
Я фильм уже третий день не могу досмотреть. Все отвлекаюсь
 
дабы не гонять лишний раз Text.From и Number.From я бы написал так
Код
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

так Text.From вызывается 1 или 2 раза в каждой группе, Number.From не используется вообще
 
Цитата
Андрей Лящук написал:
(a,b)=>if b[a]-a[a]>b[b]-a[b] then 1 else 0
Супер! Все время забываю о пятом аргументе
F1 творит чудеса
 
Доброе время суток.
Присоединяюсь, ещё одна попытка
Код
let
    intervalsToText = (items) =>
    let
        addLocalId = Table.AddIndexColumn(Table.FromColumns({items}), "local"),
        addDiff = Table.AddColumn(addLocalId, "dif", each [Column1] - [local]),
        grouped = Table.Group(addDiff, {"dif"}, {"temp", (sub) => 
            if Table.RowCount(sub) > 2 then
                Text.From(List.First(sub[Column1]))  & "-" & Text.From(List.Last(sub[Column1]))
            else
                Text.Combine(List.Transform(sub[Column1], Text.From), ",")
        })
    in
        Text.Combine(grouped[temp], ","),
    Source = Table.Distinct(Table1 & Table2),
    groupNames = List.Transform({"1".."6"}, each "A" & _),
    addId = Table.AddIndexColumn(Source, "id"),
    calcer = Table.Group(addId, groupNames, {"temp", (sub) => 
    let
        ids = sub[id]
    in
        Table.AddColumn(sub, "Индексы", each intervalsToText(List.RemoveMatchingItems(ids, {[id]})))
    }),
    return = Table.ExpandTableColumn(calcer, "temp", List.RemoveItems(Table.ColumnNames(addId), groupNames) & {"Индексы"})
in
    return
Изменено: Андрей VG - 02.04.2020 03:59:48 (Table.Distinct - пропустил)
Страницы: 1
Наверх