Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
PQ в условном или в настраиваемом столбце пронумеровать строки в случае истины
 
#1
17.02.2019 10:15:06
добрый день.вопрос по Power query. сейчас в запросе условный столбец помечает единицей в случае если условие выполняется. а как пронумеровать строки, в которых выполняется условие? подскажите пожалуйста.  
Изменено: artyrH - 17.02.2019 10:52:33
 
 
 
#2
17.02.2019 22:44:09
Добрый день!
все просто через "группировку"
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Условный столбец добавлен" = Table.AddColumn(Источник, "<30", each if [Столбец2] <= 30 then 1 else null),
    #"Добавлен индекс" = Table.AddIndexColumn(#"Условный столбец добавлен", "Индекс", 0, 1),
    #"Сгруппированные строки" = Table.Group(#"Добавлен индекс", {"<30"}, {{"Index", each Table.AddIndexColumn(_, "1", 1, 1), type table}}),
    #"Развернутый элемент Index" = Table.ExpandTableColumn(#"Сгруппированные строки", "Index", {"Столбец1", "Столбец2", "Индекс", "1"}, {"Столбец1", "Столбец2", "Индекс", "1"}),
    #"Условный столбец добавлен1" = Table.AddColumn(#"Развернутый элемент Index", "Пользовательская", each if [#"<30"] = null then null else [1]),
    #"Сортированные строки" = Table.Sort(#"Условный столбец добавлен1",{{"Индекс", Order.Ascending}}),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Сортированные строки",{"Столбец1", "Столбец2", "Пользовательская"})
in
    #"Другие удаленные столбцы"
 
 
 
#3
17.02.2019 23:14:15
Anton555, здравствуйте. спасибо за решение. вот вариант через объединение
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Добавлен индекс" = Table.AddIndexColumn(Источник, "Индекс", 0, 1),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Добавлен индекс", each [Столбец2] <= 30),
    #"Добавлен индекс1" = Table.AddIndexColumn(#"Строки с примененным фильтром", "Индекс.1", 1, 1),
    #"Объединенные запросы" = Table.NestedJoin(#"Добавлен индекс",{"Индекс"},#"Добавлен индекс1",{"Индекс"},"Строки с примененным фильтром",JoinKind.LeftOuter),
    #"Развернутый элемент Строки с примененным фильтром" = Table.ExpandTableColumn(#"Объединенные запросы", "Строки с примененным фильтром", {"Индекс.1"}, {"Индекс.1"}),
    #"Сортированные строки" = Table.Sort(#"Развернутый элемент Строки с примененным фильтром",{{"Индекс", Order.Ascending}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Сортированные строки",{"Индекс"})
in
    #"Удаленные столбцы"
только шагов много. в одну строку хотелось бы.
 
 
 
#4
18.02.2019 11:23:46
Ну, не в одну, но так, чисто поржать :D
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Столбец3 = List.Skip( List.Accumulate( Источник[Столбец2], {0}, (st,cur)=> if cur < 30 then st & {1 + List.Last(List.RemoveNulls(st))} else st & {null}), 1),
    Итог = Table.FromRows(List.Transform( List.Zip({ Table.ToRows(Источник), List.Transform(Столбец3, each {_}) }), each List.Union(_) ) )
in
    Итог

Грандов прошу сильно не пинать. Это всего лишь мои потуги в освоении List.Accumulate.
Изменено: PooHkrd - 18.02.2019 11:25:34 (добавил файл)
Вот горшок пустой, он предмет простой...
 
 
 
#5
18.02.2019 12:09:56
Доброе время суток
Цитата
PooHkrd написал:
так, чисто поржать
Ну разве что :)
 
 
 
#6
18.02.2019 12:21:02
Андрей VG, спасибо. Все никак мне не даются записи, а тут така вкуснятинка.
Код
=Table.FromRecords(
    List.Accumulate(
        Table.ToRecords(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]), 
        [id = 0, recs ={}], 
        (acc, item) => [id = (if item[Столбец2] < 30 then acc[id] + 1 else acc[id]), recs = acc[recs] & {Record.AddField(item, "<30", if id = acc[id] then null else id)}]
    )[recs]
)

Так все таки понятнее, хоть и не соответствует пожеланиям ТС про одну строку.
Вот горшок пустой, он предмет простой...
 
 
 
#7
18.02.2019 14:09:52
Ещё вариант, для разнообразия.  :)  Правда, не в одну строку:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    list = List.Generate(()=>
[i = 0, a = Source{i}[Столбец2], b = Number.From(a<30), c = if b=0 then null else b],
each [i] < Table.RowCount(Source),
each [i = [i]+1, a = Source{i}[Столбец2], b = [b]+Number.From(a<30), c = if a < 30 then b else null], each [c]),
    final = Table.FromColumns(Table.ToColumns(Source)&{list}, Table.ColumnNames(Source)&{"<30"})
in
    final
 
 
 
#8
18.02.2019 16:04:18
PooHkrd, Андрей VG, Aleksei_Zhigulin, большое спасибо за ваши решения.
только
Цитата
PooHkrd написал:
так, чисто поржать
я не въехал - когда ржать начинать то?) и чем мне это грозит?
чем грозит функция List.Accumulate ? может, не использовать эту функцию?  
 
 
 
#9
18.02.2019 16:19:27
Цитата
PooHkrd написал:
чисто поржать
это про вашу фразу:
Цитата
artyrH написал:
в одну строку хотелось бы.
Вот Андрей - понял и предложил вам именно такое решение. В принципе логика решения моего и Андрея одна и та же, только я генерил список, и потом прикручивал его к исходной таблице, а Андрей генерил сразу готовые записи с новым полем, которые потом собрал в таблицу.
Алексей тоже генерил столбец, но уже по-другому, чем я, прикручивал его к итоговой таблице.
У них обоих код получше будет, я уже нашел у себя кое чего лишнего.
Вот горшок пустой, он предмет простой...
 
 
 
#10
18.02.2019 16:42:07
Цитата
artyrH написал:
чем грозит функция List.Accumulate ?
я к тому что во всех вариантах эта функция. не подведет же?)
это щас я придумываю темы и смотрю можно решить или нет. а потом, когда коснется, на больших объемах я уже не смогу проверить правильный результат или нет. успокойте меня что функция отличная.
 
 
 
#11
18.02.2019 16:46:31
Цитата
artyrH написал:
и чем мне это грозит?
Может код разобрать, как это сделал коллега PooHkrd?
Подумайте на этой частью моего кода
Цитата
acc[recs] & {Record.AddField(item, "<30", if id = acc[id] then null else id)}
Пусть в таблице N строк. Сколько раз будет создан новый список записей? Как это будет быстро, учитывая функциональные особенности Power Query? Сделайте для самого себя простейший тест на списке в 100000 элементов с использованием List.Accumulate с формированием нового списка, где каждый элемент на 1 больше.

P. S. Aleksei_Zhigulin, можно сделать на порядок быстрее, если использовать
Код
Source =List.Buffer(Table.ToRecords(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]))
тут скорость доступа практически такая, как в VBA с доступом к элементу коллекции по его номеру. Не For Each, конечно, но куда лучше скорости доступа к записи по номеру строки таблицы.
 
 
 
#12
18.02.2019 17:07:40
Цитата
Андрей VG написал:
можно сделать на порядок быстрее
Спасибо, буду знать. List.Generate использовал всего во второй раз, на моей памяти. С записями, по Вашему примеру, уже делал, теперь вот попробовал напрямую к таблице обращаться. Догадывался, что с записями на больших объёмах будет быстрее, но не ожидал, что настолько.
 
 
 
#13
18.02.2019 17:50:46
Код
// мои пять копеек
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Индекс", 1, 1),
    #"Added Index1" = Table.AddIndexColumn(Table.SelectRows(#"Added Index", each [Столбец2] < 30), "<30", 1, 1) & Table.SelectRows(#"Added Index", each [Столбец2] >= 30),
    #"Sorted Rows" = Table.Sort(#"Added Index1",{{"Индекс", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Индекс"})
in
    #"Removed Columns"
F1 творит чудеса
 
 
 
#14
18.02.2019 19:49:44
Максим Зеленский, спасибо.
Цитата
Андрей VG написал:
Может код разобрать ?
разобрать - это как, вот так?
Код
let
    Таблица1 = Table.FromRecords(List.Accumulate(Table.ToRecords(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]), [id = 0, recs ={}], (acc, item) => [id = (if item[Столбец2] < 30 then acc[id] + 1 else acc[id]), recs = acc[recs] & {Record.AddField(item, "<30", if id = acc[id] then null else id)}])[recs]),
    П1 = Table.ToRecords(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]),
    П2 = List.Accumulate(П1, [id = 0, recs ={}], (acc, item) => [id = (if item[Столбец2] < 30 then acc[id] + 1 else acc[id]), recs = acc[recs] & {Record.AddField(item, "<30", if id = acc[id] then null else id)}])[recs],
    П3 = Table.FromRecords(П2)
in
    П3
только не понимаю я шаг П2.
Подумайте на этой частью моего кода
"acc[recs] & {Record.AddField(item, "<30", if id = acc[id] then null else id)}"

Record.AddField добавляет запись в любом случае, при равенстве  null, иначе id. а id при каждом неравенстве увеличивается на единицу.
Андрей VG написал:
Пусть в таблице N строк. Сколько раз будет создан новый список записей? Как это будет быстро, учитывая функциональные особенности Power Query?

если в таблице 10 строк, то и записей будет 10. или ошибаюсь? Сколько раз будет создан новый список записей? этот список что, перезаписывается каждый раз?
Андрей VG написал:
Сделайте для самого себя простейший тест на списке в 100000 элементов с использованием List.Accumulate с формированием нового списка, где каждый элемент на 1 больше

как сделать тест - не знаю.
мне уже расхотелось использовать в запросах List.Accumulate
Изменено: artyrH - 18.02.2019 19:55:02
 
 
 
#15
19.02.2019 11:01:18
Цитата
artyrH написал:
этот список что, перезаписывается каждый раз?
Именно. В этом и проблема этого кода. Пусть есть список с N элементами. Каждый шаг итерации в List.Accumulate в представленном алгоритме создаёт новый список как результат сцепки предшествующего списка, сцепленного с очередным элементом. Пусть i текущий элемент исходно списка. Тогда для создание нового нужно скопировать i - 1 элементов предшествующего в новый плюс текущая позиция. На начальном шаге итерации 1 копирование, на последнем N. В среднем N / 2 скопированных элементов на каждом шаге итерации. Всего итераций N. Соответственно сложность алгоритма N^2 / 2.
И что будет, если элементов списка 100 000?
Цитата
artyrH написал:
как сделать тест - не знаю.
А какие сложности воспользоваться на листе функцией СИМВОЛ(СЛУЧМЕЖДУ(224;227)) для первого столбца и СЛУЧМЕЖДУ(1;100) для второго столбца, размножив их на нужное количество строк, потом преобразовав формулы в значения (исходя из вашего примера)?
Цитата
artyrH написал:
мне уже расхотелось использовать в запросах List.Accumulate
А вот бояться её не стоит. Если использовать её по назначению, когда второй аргумент функции используется именно как аккумулятор обработки функцией, задаваемой третьим аргументом над заданным первым аргументом списком. Такая функция нужна, когда требуется определить, что делать с текущим элементом списка на основании предшествующего состояния. Альтернативой здесь может выступить рекурсивная функция, но тогда доступ к элементу исходного списка будет дольше и будет проигрышь во времени. Вы просто пока не сталкивались такими задачами.
Изменено: Андрей VG - 19.02.2019 11:08:43
 
 
 
#16
19.02.2019 11:26:19
Цитата
Андрей VG написал:
Соответственно сложность алгоритма N^2 / 2.
Странно, я думал что он проезжается конвейером по исходному списку и набивает новый стек в один проход, а тут вон оно как. Каждый раз лезет в исходник и считывает по-новой.
List.Generate так же работает? Он вроде ж быстрее кумулятивные расчеты считает.
Вот горшок пустой, он предмет простой...
 
 
 
#17
19.02.2019 11:47:44
Цитата
PooHkrd написал:
и набивает новый стек в один проход
А нет в List.Accumulate формирования списка, как в List.Generate (там да, внутренний механизм обеспечивает это). В List.Accumulate предполагается простая структура для аккумулятора.
Цитата
PooHkrd написал:
Каждый раз лезет в исходник и считывает по-новой.
Этого не понял :(
 
 
 
#18
19.02.2019 11:55:28
Цитата
Андрей VG написал:
Этого не понял
Вот.
Цитата
Андрей VG написал:
Пусть i текущий элемент исходно списка. Тогда для создание нового нужно скопировать i - 1 элементов предшествующего в новый плюс текущая позиция. На начальном шаге итерации 1 копирование, на последнем N.
Я считал что перед заходом в функцию берется исходный список из первого аргумента и пробегаясь по его элементам функция собирает новый список на каждой итерации добавляя в него плюс один элемент, из вашего же описания следует что на каждом этапе список формируется каждый раз целиком новый. Ну, или я чего-то не так понял.
З.Ы. кажется начинаю понимать:
Цитата
Андрей VG написал:
В List.Accumulate предполагается простая структура для аккумулятора.
т.е. если в качестве аккумулятора выступают простые типы числа/текст, то он каждый раз записывает полученное на предыдущем шаге значение в память и работает с ним, если же типы составные таблицы/записи/списки, то на них уже влияет ленивость вычислений. Так получается?
Изменено: PooHkrd - 19.02.2019 12:01:44
Вот горшок пустой, он предмет простой...
 
 
 
#19
19.02.2019 12:52:55
Цитата
PooHkrd написал:
берется исходный список из первого аргумента и пробегаясь по его элементам функция собирает новый список
Нет, так работает List.Transform. Здесь результатом является второй аргумент Accumulate.
Цитата
PooHkrd написал:
то на них уже влияет ленивость вычислений
И это тоже. Но в рассматриваемом решении тормозом является каждый раз создаваемый список. Можно привести аналог. Тут та же проблема, что и в VBA с Redim Preseve для массива в цикле.
Код
Dim Arr() As Long, i As Long
Redim Arr(1 To 1)
For i = 1 to 100000
    Redim Preserve Arr(1 To i)
    Arr(i) = i
Next

Фактически. Создаётся новый массив Arr c числом элементов равным i, в этот новый массив копируется i - 1 элемент старого массива. Старый массив удаляется. Как бы не было мало время копирования одного элемента, время с увеличением числа элементов массива также линейно растёт.
Со скаляром же проще - там время создания и копирование одно и тоже.
 
 
 
#20
19.02.2019 12:57:33
Цитата
PooHkrd написал:
собирает новый список на каждой итерации добавляя в него плюс один элемент
не очень точно. Не собирает новый список, а высчитывает очередное некое значение, копируя предыдущее состояние и изменяя его в соответствии с функцией. То, что второй аргумент - запись, содержащая список, так это задано руками. Вообще аккумулятор возвращает одно значение, например:
Код
List.Accumulate({1..5},1,(s,c)=>s*c)

Никакие списки тут не создаются, а высчитывается некое значение в соответствии с количеством итераций=List.Count(первый аргумент)
F1 творит чудеса
 
 
 
#21
19.02.2019 13:06:58
Цитата
Максим Зеленский написал:
Никакие списки тут не создаются
Это я и так знал, просто мое косноязычие не дает правильно сформулировать мысль. Для меня итог такой - использование в качестве аккумуляторов составных типов лучше не делать, т.к. при каждой итерации пересчитывается весь тот массив, который создаю.
Если нужно создать в цикле строки таблицы, элементы списка, записи, и в расчетах учитывать предыдущее состояние то лучше для этого использовать List.Generate. Он как я понял работает по аналогии с List.Transform, только не преобразует ничего, а создает с список нуля.
Решение задачи ТС, на мой субъективный взягляд Максим предложил самое оптимальное по скорости. В принципе и сам такое проделывал, но тут вот хотелось по-извращаться с аккумулятором, да и высказывание ТС про код в одну строку меня прям улыбнуло.  :D
Максим, Андрей, огромное спасибо за науку :idea: .
Вот горшок пустой, он предмет простой...
 
 
 
#22
19.02.2019 13:59:00
Цитата
PooHkrd написал:
Он как я понял работает по аналогии с List.Transform, только не преобразует ничего, а создает с список нуля
не совсем... на List.Transform не сильно похоже. Это скорее Do While ... Loop
Описывал тут когда-то: Генератор произвольных списков в Power Query и Power BI
А ещё в List.Accumulate есть такое нечто, связанное с promises :) что может приводить к достаточно интересному эффекту Stack Overflow :) вот тут в конце и в комментариях
F1 творит чудеса
 
 
 
#23
19.02.2019 14:14:00
Максим Зеленский, читал про эту штуку, Андрей как-то давал ссылочку. Сергей Лосев там очень интересное решение предложил, я его до сих пор не разобрал, все некогда.
Я собственно про это promices и имел в виде, когда писал что если в роли аккумулятора выступают
Цитата
PooHkrd написал:
типы составные таблицы/записи/списки, то на них уже влияет ленивость вычислений.
В List.Generate этих обещаний, как я понимаю нет. и ленивость на составление итогового списка не влияет, Ну, если исходные данные передать функции корректно, через буфер и все такое.
Вот горшок пустой, он предмет простой...
 
 
 
#24
19.02.2019 14:20:38
Да черт его знает, есть там ленивость + обещания, или нет. Я подозреваю, что есть, но точнее сказать могут только два человека, а их на нашем форуме нет. Не зря же все аргументы - функции. Они, как известно, вычисляются лениво. Да и в принципе в самой логике работы (насколько я вообще понимаю promises, а понимаю я их весьма поверхностно) для обещаний как раз простор в реализации.
F1 творит чудеса
 
 
 
Страницы: 1
Наверх