Страницы: 1
RSS
PQ ускорение преобразования таблицы с промежуточными итогами в нормализованный вид
 
Всем доброго дня!
Имеется таблица ~8 тыс строк, содержащая промежуточные итоги (наглядно видно в файле-примере).
Мне нужно столбец 1 (в котором или федеральный округ или субъект или конкретная организация или ничего) разделить  на 3 отдельных столбца, чтобы можно было потом работать. Свой вариант на PQ я написал, но на 8 тыс. строк он работает как-то очень медленно. Хотелось бы ускорить в N раз.
Что я знаю о первом столбце:
1. После Федерального округа всегда идёт субъект РФ
2. После субъекта РФ всегда идёт строка "Итого по ОИВ" (я предполагаю, что тут могут быть разные варианты, но слово "ОИВ" точно будет. Если существенно быстрее будет проверить на точное совпадение,то можно считать, что всегда "Итого по ОИВ").
3. После строки "Итого по ОИВ" могут быть пустые строки (любое количество), а может сразу начинаться следующий субъект РФ или Федеральный орган.
4. Первая строчка и все промежуточные итоги по субъекту и ФО меня не особо интересуют (будут потом удаляться). Таблица на листе "Выгрузка" меня вполне устраивает, вот только формируется медленно.
С федеральными округами всё понятно, а вот субъекты РФ у меня ищутся очень медленно. В коде ниже [1] это столбец, который я пытаюсь разделить.
= Table.AddColumn(#"Добавлен индекс", "Субъект", each if [1]<>null then if Text.Contains(#"Добавлен индекс"[1]{[Индекс]+1},"ОИВ") then [1] else null else null)

P.s. Формулой 8888 строк Субъектов РФ делаются почти мгновенно, но хотелось бы в PQ.
Изменено: Wiss - 29.01.2020 12:10:36
Я не волшебник, я только учусь.
 
Переделал вам затупляющий весь запрос шаг. Остальное не трогал. Не делайте так больше, это очень медленный алгоритм обращения к ячейкам с фиксированным смещением от текущей.
Код
let
//Функция, которая добавляет в таблицу (указанную как первый аргумент) копию столбца (указанного во втором аргументе) со смещением элементов на 1 строку вверх или вниз (в третьем аргументе 0 - вниз / 1 - вверх). 
//Вместо выбывающего элемента добавляется null.
    fnTableAddOffsetColumn = ( tbl as table, clmn as text, dir as number ) => 
    if Table.RowCount( tbl ) = 0
        then tbl
        else if dir = 0 
            then Table.FromColumns( 
                Table.ToColumns( tbl ) & Table.ToColumns( #table( {clmn}, {{null}} ) & Table.RemoveLastN( Table.SelectColumns( tbl, {clmn}), 1 ) ), 
                Table.ColumnNames( tbl ) & {"Пред."&clmn} )
            else Table.FromColumns( 
                Table.ToColumns( tbl ) & Table.ToColumns( Table.RemoveFirstN( Table.SelectColumns( tbl, {clmn}), 1 ) & #table( {clmn}, {{null}} ) ), 
                Table.ColumnNames( tbl ) & {"След."&clmn} ),

    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Добавлен пользовательский объект" = Table.AddColumn(Источник, "ФО", each if [1]<>null then if Text.Contains([1],"едеральн") then [1] else null else null),
    Custom1 = fnTableAddOffsetColumn( #"Добавлен пользовательский объект", "1", 1 ),
    #"Добавлен пользовательский объект1" = Table.AddColumn(Custom1, "Субъект", each if [1]<>null then if Text.Contains([След.1],"ОИВ") then [1] else null else null),
    #"Замененные ошибки" = Table.ReplaceErrorValues(#"Добавлен пользовательский объект1", {{"ФО", null}, {"Субъект", null}}),
    #"Заполнение вниз" = Table.FillDown(#"Замененные ошибки",{"ФО", "Субъект"}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Заполнение вниз",{"ФО", "Субъект", "1", "А", "2"})
in
    #"Переупорядоченные столбцы"
Изменено: PooHkrd - 29.01.2020 12:33:33
Вот горшок пустой, он предмет простой...
 
PooHkrd, большое человеческое Спасибо.
Изменено: Wiss - 29.01.2020 12:48:29
Я не волшебник, я только учусь.
 
еще вариантик
Код
let Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Filter = Table.SelectRows(Источник, each [1] <> null and [1] <> ""),
    Group = Table.Group(Filter,{"1"}, {{"All", each 
    [Step1 = _ {0}[1],Step2 = _ {1}[1],Step3 = _ {2}[1]]}}, 0, 
    (a,b)=> Number.From( Text.Contains(b[1], "едеральн")))
in  Table.FromRecords( Group[All])
 
Цитата
abc1 написал:
еще вариантик
Какое-то очень сильное колдунство. Попробую чуть позже разобраться, как оно работает. Пока вариант PooHkrd, в полной мере покрыл все мои потребности.
Я не волшебник, я только учусь.
 
Wiss, там никакого колдунства, просто нужно осваивать 4 и 5-й аргументы для функции Table.Group. Функция и без них предоставляет крайне широкое поле для кручения-верчения, а уж с ними - это просто праздник души.
abc1, в случае обращения к номерам строк таблицы рекомендую ставить ? типа такого:
Код
Step1 = _ {0}?[1]

а еще лучше так
Код
Step1 = _ [1]{0}?

это позволит в случае отсутствия строки с нужным номером не ловить ошибку, а просто получить в результате null.
Изменено: PooHkrd - 29.01.2020 15:54:11
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
рекомендую ставить ?
Добрый день! Подскажите пожалуйста как это работает?
 
Цитата
PooHkrd написал:
осваивать 4 и 5-й аргументы для функции Table.Group
Вы недооцениваете уровень моего невежества. Я пока только по кнопочкам тыкать научился :)
Но всё-равно спасибо. Сейчас почитаю.
Я не волшебник, я только учусь.
 
Цитата
Murderface_ написал:
как это работает?
Вот здесь все очень подробно разжёвано.
Цитата
Wiss написал:
Я пока только по кнопочкам тыкать научился
У Лени всегда есть тысяча оправданий.  :D
Цитата
Wiss написал:
Сейчас почитаю.
Вот! Ответ не мальчика, но мужа! :) Читать можно здесь. Там и файлик есть с примером кода запроса если что. Также можно поискать на форуме темы по запросу Table.Group за последние 3-4 месяца, тут было не мало примеров его использования. Сможете разобрать разные кейсы.
Изменено: PooHkrd - 29.01.2020 17:08:11
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
в случае отсутствия строки с нужным номером не ловить ошибку, а просто получить в результате null
Ваша правда
Страницы: 1
Наверх