Страницы: 1
RSS
Как в Power Query пронумеровать строки
 
Добрый день. Можно ли в Power Query пронумеровать строки в такой последовательности -первые 4 строки 1, вторые 4 строки 2 и тд? Индекс и последущее деление от остатка (на 4 ) не дает желаемого результата, транспортировкой так же не смог объединять по четыре столбца.
Есть список , где  Фио разнесено на каждую строку, нужно объединить их вместе-если использовать  макрос то там ясно.
А вот как сделать в Power Query хз...
Благодарю за помощь!
Изменено: Юра1987 - 26.06.2020 01:03:26
 
Цитата
Юра1987 написал:
Индекс и последущее деление от остатка (на 4 ) не дает желаемого результата
странно, так же работает вроде:
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица4"]}[Content],
    add = Table.AddIndexColumn(from, "Индекс", 0, 1),
    to = Table.TransformColumns(add, {"Индекс", each Number.RoundDown(_ / 4)+1})
in
    to
Цитата
Юра1987 написал:
транспортировкой так же не смог объединять
а вот это даже представить страшно
Изменено: buchlotnik - 26.06.2020 01:39:38
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik,  Делал просто через интерфейс,он не позволял так сделать, а в коде квери не так силен. Спасибо, все отлично работает!
 
Цитата
Юра1987 написал:
Делал просто через интерфейс,он не позволял так сделать
Кому не позволял?
Делай раз!

Делай два!

Делай три!

Вот горшок пустой, он предмет простой...
 
Я бы примерно так раскидал бы в нормальную таблицу, а потом хоть объединяй, хоть что делай:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица4"]}[Content],
    res1 = Table.FromRows(List.Split(Source[Столбец1],4)),
    rem1 = Table.RemoveColumns(res1,{"Column4"}),
    ind1 = Table.AddIndexColumn(rem1, "ind", 0, 1),
    res2 = Table.FromRows(List.Split(Source[Столбец2],4)),
    rem2 = Table.SelectColumns(res2,{"Column1"}),
    ind2 = Table.AddIndexColumn(rem2, "ind", 0, 1),
    res = Table.NestedJoin(ind1, {"ind"}, ind2, {"ind"}, "res", JoinKind.LeftOuter),
    expand = Table.ExpandTableColumn(res, "res", {"Column1"}, {"Column1.1"}),
    #"Removed Columns2" = Table.RemoveColumns(expand,{"ind"})
in
    #"Removed Columns2"
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
про нормальную форму речи не шло, но раз пошла такая пьянка:
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица4"]}[Content],
    lst = Table.Split(from,4),
    rec = List.Transform(lst, each [Ф=_{0}[Столбец1],И=_{1}[Столбец1],О=_{2}[Столбец1],Д=_{0}[Столбец2]]),
    to = Table.FromRecords(rec)
in
     to
или в общем виде
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица4"]}[Content],
    fn=(t,n)=>[ a=Table.Split(t,n),
                b=(t)=>List.Combine(List.Transform(Table.ToColumns(t),List.RemoveNulls)),
                c=Table.FromRows(List.Transform(a,b))][c],
    to=fn(from,4)
in
    to
Изменено: buchlotnik - 26.06.2020 12:46:56
Соблюдение правил форума не освобождает от модераторского произвола
 
Offtop
Цитата
PooHkrd написал:
Кому не позволял?
У него гранаты не той системы :D
 
Еще вариант
Код
let a = Excel.CurrentWorkbook(){[Name="Таблица4"]}[Content]
  in  Table.SplitColumn(Table.Group(a,{"Столбец2"},{"a",each 
      List.RemoveLastN(_[Столбец1],each _=null)&{_[Столбец2]
      {0}}},0,(a,b)=>Number.From(b[Столбец2]<>null))[[a]],
      "a",(i)=>i)
 
PooHkrd, за скрины отдельное спасибо - теперь буду знать!
Андрей VG,
Цитата
Андрей VG написал:
У него гранаты не той системы
и не поспоришь))

И всем кто подсказывал разные варианты огромное спасибо, так как очень пригодится для дальнейшего развития в квери.
 
оффтоп
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
любите вы поэкономить буквы и строки..
Не ну себе я хотя бы так сохранил, а то дня через три самому придется разбирать что я там наделал
Код
// Таблица4
let a = Excel.CurrentWorkbook(){[Name="Таблица4"]}[Content],
    b=Table.Group(a,{"Столбец2"},{"a",each 
      List.RemoveLastN(_[Столбец1],each _=null)&{_[Столбец2]{0}}
                },0,(a,b)=>Number.From(b[Столбец2]<>null)),   
 c = Table.SplitColumn(b[[a]],"a",(i)=>i,{"a","b","c","d"})
in c
Да и вообще, Вам ли мое изучать :)  
 
Михаил Л, ну а почему бы и не поизучать. Все знать невозможно, да и в последнее время в моей практике больше DAX, чем PQ.

Решение очень элегантное, на самом деле. Я пробовал локальной группировкой после заполнения 2го столбца, но там есть проблема с повторяющимися подряд должностями, они схлопываются.
5й аргумент в группировке использую крайне редко, и здесь не уверен, что до конца понял, как именно он у вас срабатывает. Если правильно понимаю, при локальной группировке мы идем по порядку строк, и при сравнении пустой строки на <> с null получаем false, оно же 0, а 0 это для группировки значит =. Соответственно дойдя до следующего <не null>, мы получаем true, или 1, и мы начинаем новую группу, потом опять null и оно добавляет до следующей заполненной строки. Если все понял верно, то очень интересный подход, который возьму на вооружение.
F1 творит чудеса
 
Забавно, можно ещё полирнуть
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица4"]}[Content],
    group = Table.Group(Source, {"Столбец2"}, {"temp", each 
    List.FirstN([Столбец1], 3) & List.FirstN([Столбец2], 1)}, 
    GroupKind.Local, (a, b) => Number.From(b[Столбец2]<>null) ),
    return = Table.FromRows(group[temp], {"Фамилия", "Имя", "Отчество", "Должность"})
in
    return

Размножил до более 800 тысяч строк. Этот 2,5 секунды, Михаил Л - 3,7, buchlotnik в общем виде и Дмитрий(The_Prist) Щербаков не дождался, прибил.
Цитата
Максим Зеленский написал:
то очень интересный подход, который возьму на вооружение.
Максим, насколько помню этому подходу уже несколько лет. В первые увидел в исполнении Сергея Лосева, ну, и, могу ошибаться, и в вашем тоже :)

Updated
Ради интереса поменял функцию признака группировки на человекочитаемый вид
Код
(a, b) => if b[Столбец2] = null then 0 else 1
Декларируемого падения скорости при использовании if не увидел.
Канонический вариант тоже вполне себе терпим 6 секунд
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица4"]}[Content],
    id = Table.AddIndexColumn(Source, "id", 0),
    toMark = Table.TransformColumns(id, {"id", each Number.IntegerDivide(_, 4)}),
    group = Table.Group(toMark, {"id"}, {"temp", each 
        List.FirstN([Столбец1], 3) & List.FirstN([Столбец2], 1)
    }),
    return = Table.FromRows(group[temp], {"Фамилия", "Имя", "Отчество", "Должность"})
in
    return
Изменено: Андрей VG - 26.06.2020 18:40:15
 
Цитата
Максим Зеленский написал:
мы идем по порядку строк, и при сравнении пустой строки на <> с null получаем false, оно же 0, а 0 это для группировки значит =. Соответственно дойдя до следующего  , мы получаем true, или 1, и мы начинаем новую группу, потом опять null и оно добавляет до следующей заполненной строки
Я понимаю так: если строка отвечает условию(True), то начать группировку до следующего True. Если первая строка не отвечает условию(False), то все равно начать группировку до True(и потом этот момент надо обыграть, то есть удалить первую группировку. В файле варианты)
Обычно под рукой файл с группировками. Больше всего перенял у Андрея Лящук(что смог понять, например,здесь долго до меня доходило)
Прикладываю файл. Может кто свои варианты добавит
Изменено: Михаил Л - 26.06.2020 19:23:03
 
Цитата
Андрей VG написал:
более 800 тысяч строк. Этот 2,5 секунды,  Михаил Л  - 3,7
Если каждый вариант проверять раз пять, секунды меняются?
 
Цитата
Михаил Л написал:
раз пять, секунды меняются?
Нет, для этих колебания в пределах десятой другой.
Потестируйте, я обычно отключаю после первой загрузки изменение ширины столбцов - меня скорость выгрузки интересует, а этот параметр сильно влияет и вносит не стабильность. Предполагается, что на листе одна выгрузка из Power Query.
Код
Public Sub speedTest()
    Dim t As Single
    t = Timer
    ActiveSheet.ListObjects(1).QueryTable.Refresh False
    MsgBox Timer - t
End Sub
 
Цитата
Андрей VG написал:
Потестируйте, я обычно отключаю после первой загрузки изменение ширины столбцов
Спасибо, сегодня тестировать не успею.  Завтра сделаю
 
Цитата
Михаил Л написал:
если строка отвечает условию(True), то начать группировку до следующего True.
сомневаюсь. 5 аргумент - это, по сути, обычный Value.Compare, который выдает -1, 0 или 1 в зависимости от результата сравнения двух аргументов. Соответственно, группируются только те строки, которые дают при сравнении 0 (т.е. равно). Number.From параллельно превращает true/false в числа 1 или 0 соответственно
Поэтому Number.From(b[Столбец2]<>null) дает нам 1 (то есть больше, в данном случае не важно - неравенство и есть неравенство), если значение в столбце2 <>null, и дает 0, если значение столбца2 = null. Эти 0 повторяются до тех пор, пока не встретится новое название должности в столбце2
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
Number.From(b[Столбец2]<>null) дает нам 1 (то есть больше, в данном случае не важно - неравенство и есть неравенство), если значение в столбце2 <>null, и дает 0
Не знаю что сказать :)  Буду придерживаться своего подхода
Страницы: 1
Наверх