Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Power Query. Ускорение процесса объединения текста предыдущей, текущей и последующей строк одного столбца, В Power Query создал запрос, объединяющий текст из предыдущей, текущей и последующей строк одного столбца. Все работает. НО... очень долго. Всего то около 10 тысяч строк. Подскажите, как ускорить обработку. Пример приложен.
 
#1
19.04.2023 10:36:36
Из 1С выгружаются файлы, в которых за строкой с Наряд-заказа СЛЕДУЮЩИМИ строками прикладывается детализация с данными об Сотрудниках, принимавших участие в данном Наряд-заказе. Сдельная оплата также привязана к этим Наряд-заказам. По неизвестным мне причинам в детализации иногда  приводятся ФИО Сотрудников, но без начисления сдельной оплаты. Тогда в строке по графе оплаты нулевые (null) значения. Отдельные Наряд-заказы не содержат детализации по Сотрудникам, так как им по этим работам начисление ведется по окладной системе. И тогда вэтих строках также оплата и ФИО Сотрудника - null.
Задача удалить номера Наряд-заказов с детализаций с указанием их в строках с детализацией. Эта задача легко решается путем заполнения ВНИЗ. Проблема в определении строк для удаления. Для этой цели был добавлен столбец с идентификацией строк с наличием ФИО Сотрудника: пошел путем исключения - проверяю наличие номера Наряд-заказа при отсутствии ФИО. Еще один дополнительный столбец сформировал за счет применения Индекса путем объединения текстов предыдущей, текущей и последующей строк.
Теперь еще нужна фильтрация.
Проблема в том, что запрос немного подвисает в редакторе. При этом в режиме "Только подключение" файл спокойно закрывается и сохраняется и не санкционированных закрытий нет.
Но самое главное, он виснет напрочь даже без шага фильтрации при выгрузке выходных данных на лист. И это всего-то на 10 тысячах строк. При этом Excel самопроизвольно закрывается после загрузки 4,5 - 5 тысяч строк. А если учесть фильтрацию и увеличение объема данных, то мой и так на этом массиве не работающий код, вообще ничего не даст.
Попробовал List.Accumulate. Но не смог оптимально собрать в один-два шага нужны операции обработки я не смог.
Chat CPT по умного правильно советует брать метаданные столбцов и работать пакетно. И даже пишет код. НО... он не работает. А так как я еще не разобрался в функциях высокого порядка, то и правильно выявить ошибку не могу.
Прошу помощи по ускорению обработки...
P.S. Исходный запрос работает с предыдущими групповыми запросами: объединение файлов из папок. До шага выгрузки код работает более или менее быстро...
UPD1 Столбцов в таблице 25. Двадцать пять. Могу уменьшить частично путем свертывания группы столбцов. НО... мне кажется это не повлияет или совсем незначительно
Изменено: AzatKukanov - 19.04.2023 11:45:24
 
 
 
#2
19.04.2023 11:17:51
AzatKukanov, если хотите сократить/ускорить запрос, который в файле, то попробуйте вот это
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Табл1"]}[Content],
    f = (t as table) as table => Table.SelectRows(t, each [Сотрудник] <> null),
    gr = Table.Group(Источник, {"Номер наряда", "Операция"}, {{"all", each f(_)}}, GroupKind.Local, (s, c) => Number.From(c[Номер наряда] <> null)),
    expand = Table.ExpandTableColumn(gr, "all", {"Сумма оплаты по наряду", "Сотрудник"})
in
    expand

но что-то мне подсказывает, что не там вы ищете.
Пришелец-прораб.
 
 
 
#3
19.04.2023 11:39:45
Цитата
написал:
AzatKukanov, если хотите сократить/ускорить запрос, который в файле, то попробуйте вот это
Код
    [URL=#]?[/URL]       1  2  3  4  5  6  7      let          Источник = Excel.CurrentWorkbook(){[Name=  "Табл1"  ]}[Content],          f = (t as table) as table => Table.SelectRows(t, each [Сотрудник] <> null),          gr = Table.Group(Источник, {  "Номер наряда"  ,   "Операция"  }, {{  "all"  , each f(_)}}, GroupKind.Local, (s, c) => Number.From(c[Номер наряда] <> null)),          expand = Table.ExpandTableColumn(gr,   "all"  , {  "Сумма оплаты по наряду"  ,   "Сотрудник"  })    in          expand  
 
но что-то мне подсказывает, что не там вы ищете.
Проблема не на этапе определения строк с номерами Наряд-заказов. Проблема в объединении текста ТРЕХ СТРОК ОДНОГО и ТОГО ЖЕ СТОЛБЦА. До этого шага, включая предпоследний шаг (добавление индекса) все ОК. Потом все повисает
 
 
 
#4
19.04.2023 11:48:11
AzatKukanov, ну возможно. Код то попробовали применить на практике?
Если вернуться к вашему коду (к проблемной его части)
Код
= Table.AddColumn(
    AddIndex,
    "Combine",
    each
      if [Index] = 0 then [Заголовки]
      else if [Index] = Table.RowCount(AddIndex) - 1 then [Заголовки] & " " & "Last"
      else if [Index] > 0 then
        Table.Column(AddIndex, "Заголовки"){[Index] - 1} & ";" & [Заголовки] & ";" & Table.Column(AddIndex, "Заголовки"){[Index] + 1}
      else
        null
  )

то вот проблемные места:
- else if [Index] > 0 then - это лишнее. Индекс же всегда >= 0 (вы его так определили), а на ноль вы его уже проверили выше.
- Table.Column(AddIndex, "Заголовки") : надо было предварительно присвоить какой-то переменной значение выражения List.Buffer(AddIndex[Заголовки]) и в шаге ссылаться на эту переменную.
- Table.RowCount(AddIndex): то же самое. Предварительно ("снаружи") посчитали это число 1 раз , присвоили значение какой-то переменной и потом ссылаетесь на эту переменную в запросе.
Изменено: Alien Sphinx - 19.04.2023 12:28:29
Пришелец-прораб.
 
 
 
#5
19.04.2023 13:38:03
Цитата
написал:
AzatKukanov, ну возможно. Код то попробовали применить на практике?
Если вернуться к вашему коду (к проблемной его части)
Код
    [URL=#]?[/URL]       1  2  3  4  5  6  7  8  9  10  11      = Table.AddColumn(          AddIndex,          "Combine"  ,          each            if [Index] = 0 then [Заголовки]            else if [Index] = Table.RowCount(AddIndex) - 1 then [Заголовки] &   " "   &   "Last"            else if [Index] > 0 then              Table.Column(AddIndex,   "Заголовки"  ){[Index] - 1} &   ";"   & [Заголовки] &   ";"   & Table.Column(AddIndex,   "Заголовки"  ){[Index] + 1}            else              null        )  
 
то вот проблемные места:
- else if [Index] > 0 then - это лишнее. Индекс же всегда >= 0 (вы его так определили), а на ноль вы его уже проверили выше.
- Table.Column(AddIndex, "Заголовки") : надо было предварительно присвоить какой-то переменной значение выражения List.Buffer(AddIndex[Заголовки]) и в шаге ссылаться на эту переменную.
- Table.RowCount(AddIndex): то же самое. Предварительно ("снаружи") посчитали это число 1 раз , присвоили значение какой-то переменной и потом ссылаетесь на эту переменную в запросе.
В первой и последней строках в этом случае будет ошибка, так как Индекс выйдет за пределы диапозона. Можно конечно, Индекс или Кол-во строк в каком -либо столбце исходной таблицы загнать в буфер и считать обращаясь к нему. НО... пока я этой необхоимости не вижу.
Мне больше по идейному смыслу нравится подход с использованием метаданных столбцов. Это реально может ускорить обработку
Я со сторонней помощью как-то использовал этот подход. Но я не очень в этом силен
UPD1 Посчитал, что может быть логическая неувязка с тем, что вначале я вычисляю значение первой и последней строки. Сделал по порядку: вначале первая строка, потом иду до последней и завершаю вычислением последней строки. Нет эффекта
UPD2 ввел переменные до конструкции if then else. Ни вижу эффекта.
Изменено: AzatKukanov - 19.04.2023 13:48:36
 
 
 
#6
19.04.2023 14:28:05
Цитата
AzatKukanov написал:
ввел переменные до конструкции if then else
вам переменные надо вводить до этого шага, а не внутри него
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Табл1"]}[Content],
    #"Добавлен пользовательский объект" = Table.AddColumn(Источник, "Заголовки", each if ([Сумма оплаты по наряду]=null and [Сотрудник]=null and [Номер наряда]<>null and [Операция]<>null) then "Заголовок" else "Детали"),
    AddIndex = Table.AddIndexColumn(#"Добавлен пользовательский объект", "Index", 0, 1, Int64.Type),
    max = Table.RowCount(AddIndex),
    zago = List.Buffer(AddIndex[Заголовки]),
    Combine =Table.AddColumn(AddIndex, "Combine", each if [Index]=0 then [Заголовки]  
    else if  [Index]= max - 1  then  [Заголовки] & " " & "Last"   
    else zago{[Index]-1} & ";" & [Заголовки] & ";" & zago{[Index]+1}),
    #"Заполнение вниз" = Table.FillDown(Combine,{"Номер наряда", "Операция"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Заполнение вниз", each ([Combine] <> "Детали;Заголовок;Детали" and [Combine] <> "Заголовок" and [Combine] <> "Заголовок;Заголовок;Детали"))
in
    #"Строки с примененным фильтром"

Цитата
AzatKukanov написал:
по идейному смыслу нравится подход с использованием метаданных столбцов
как интересно. И как же можно присвоить метаданные _столбцу_ таблицы?
Пришелец-прораб.
 
 
 
#7
20.04.2023 04:44:07

Вот что пишет по этому поводу Chat GPT.

Метаданные столбца - это информация о свойствах столбца в исходном источнике данных, таких как тип данных, длина, формат, индексы и другие метрики, которые помогают оптимизировать выполнение запросов. Эти метаданные могут быть использованы для автоматической оптимизации запросов, таких как уменьшение повторных обращений к данным, улучшение использования индексов или минимизация преобразований типов данных.

В Power Query метаданные могут быть получены из источника данных при подключении или в процессе выполнения запроса через вызов специальных функций, таких как Table.Schema(), которая возвращает схему таблицы, или Value.Metadata(), которая возвращает метаданные значения в столбце. Эти метаданные могут быть использованы для оптимизации последующих преобразований и сведения к минимуму повторных обращений к данным.

Прилагаю файл с различными кодами решения моей задачи. Вариант с использованием  List.Parallelize(rowIndexes, processRow) у меня не пошел, так как мой Excel из Office365 его не знает. Сейчас пытаюсь определить какой из них все же лучше.
 
 
 
#8
20.04.2023 04:51:31
Еще от ChatGPT по метаданным столбца:

Вот полный код, который использует метаданные столбца для улучшения производительности и уменьшения повторных обращений к данным:

let

   // Получаем таблицу

   Source = ...,

   

   // Получаем метаданные столбца ИД_Заголовок

   ColumnMetadata = Value.Metadata(Table.Column(Source, "ИД_Заголовок")),

   // Создаем функцию для объединения строк через разделитель

   CombineRows = (prevValue, currentValue, nextValue) =>

       if currentValue = "Заголовок" and prevValue = "Заголовок" and nextValue = "Заголовок" then

           "Заголовок;Заголовок;Заголовок"

       else if (currentValue = "ЗаголовокLast" and nextValue = null) then

           "ЗаголовокLast"

       else if currentValue = "Наряд" and prevValue = "Наряд" and nextValue = "Наряд" then

           "Наряд;Наряд;Наряд"

      else if (currentValue = "Наряд" and prevValue = "Наряд" and nextValue = "Заголовок") or

               (currentValue = "Наряд" and prevValue = "Заголовок" and nextValue = "Заголовок") or

               (currentValue = "Заголовок" and prevValue = "Наряд" and nextValue = "Наряд") then  "Наряд;Заголовок;Заголовок"

       else

           Text.Combine({prevValue, currentValue, nextValue}, ";"),

   // Добавляем новый столбец, используя метаданные столбца ИД_Заголовок

   AddCombinedColumn = Table.AddColumn(Source, "Combined", each List.Accumulate(

       Table.Range(ColumnMetadata, [Index] - 1, 3),  "",

       (state, current) => CombineRows(state, current{0}, current{1})

   ), type text, ColumnMetadata),

  // Проверяем значение столбца Combined и удаляем строки, для которых checkValue = 0

   RemoveRows = Table.SelectRows(AddCombinedColumn, each [Combined] <> null and [checkValue] = 1),

   // Удаляем столбец с метаданными

   RemoveMetadataColumn = Table.RemoveColumns(RemoveRows, "ИД_Заголовок.Metadata")

in

   RemoveMetadataColumn

В этом коде мы получаем метаданные столбца ИД_Заголовок с помощью функции Value.Metadata(), затем используем эти метаданные для определения типа данных и других характеристик столбца при добавлении нового столбца Combined. Это позволяет избежать повторных обращений к данным при выполнении последующих преобразований. Далее мы используем этот столбец для проверки условий и удаления строк, для которых checkValue равен 0.
 
 
 
#9
20.04.2023 06:41:16
Код
let
   Источник = Excel.CurrentWorkbook(){[Name="Табл1"]}[Content],
   #"Добавлен пользовательский объект" = Table.AddColumn(Источник, "Заголовки", each if [Номер наряда] <>null then "Заголовок" else "Детали"),
   AddIndex = Table.AddIndexColumn(#"Добавлен пользовательский объект", "Index", 1, 1, Int64.Type),
   max = Table.RowCount(AddIndex),
   zago = List.Buffer(AddIndex[Заголовки]),
   Combine =Table.AddColumn(AddIndex, "Combine", each 
      if [Index]=1 then 
         [Заголовки]  
      else 
         if [Index]=max  then
            [Заголовки] & " " & "Last"   
         else 
            zago{[Index]-2} & ";" & 
            [Заголовки] & ";" & 
            zago{[Index]}),
   #"Заполнение вниз" = Table.FillDown(Combine,{"Номер наряда", "Операция"}),
   #"Строки с примененным фильтром" = Table.SelectRows(#"Заполнение вниз", each ([Combine] <> "Детали;Заголовок;Детали" and [Combine] <> "Заголовок" and [Combine] <> "Заголовок;Заголовок;Детали"))
in
    #"Строки с примененным фильтром"
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
 
 
#10
20.04.2023 06:42:46
Цитата
AzatKukanov написал:
Вариант с использованием  List.Parallelize(rowIndexes, processRow) у меня не пошел
все. Расходимся, пацаны.
Пришелец-прораб.
 
 
 
#11
20.04.2023 08:14:11
Цитата
написал:
Код
    [URL=#]?[/URL]       1  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  18  19  20      let         Источник = Excel.CurrentWorkbook(){[Name=  "Табл1"  ]}[Content],         #  "Добавлен пользовательский объект"   = Table.AddColumn(Источник,   "Заголовки"  , each if [Номер наряда] <>null then   "Заголовок"   else   "Детали"  ),         AddIndex = Table.AddIndexColumn(#  "Добавлен пользовательский объект"  ,   "Index"  , 1, 1, Int64.Type),         max = Table.RowCount(AddIndex),         zago = List.Buffer(AddIndex[Заголовки]),         Combine =Table.AddColumn(AddIndex,   "Combine"  , each             if [Index]=1 then                [Заголовки]              else                if [Index]=max  then                  [Заголовки] &   " "   &   "Last"                 else                   zago{[Index]-2} &   ";"   &                   [Заголовки] &   ";"   &                   zago{[Index]}),         #  "Заполнение вниз"   = Table.FillDown(Combine,{  "Номер наряда"  ,   "Операция"  }),         #  "Строки с примененным фильтром"   = Table.SelectRows(#  "Заполнение вниз"  , each ([Combine] <>   "Детали;Заголовок;Детали"   and [Combine] <>   "Заголовок"   and [Combine] <>   "Заголовок;Заголовок;Детали"  ))    in          #  "Строки с примененным фильтром"  
 
Спасибо за код. Я его прописал, но это, к сожалению, не сильно ускоряет процесс. Создал функцию, которая  создает список значений  столбца "Заголовок", потом список индексов строк. Далее объединяет текст из трех строк функцие List.Transform и создает соответствующий столбец.  Потом проверяет соответсвие строк заданным условиям. Ускорение ощутимо, но все еще  долго, на мой взгляд: где-то около часа на загрузку 10К строк на лист Excel.
Сейчас я буду пробовать в Power BI предложение Chat GPT по распараллеливанию вычислений. По факту поделюсь как это реально работает.
P.S. Ноутбук с ОЗУ 8Гб, доступно 6Гб. Тактовая 4,1 ГГц. Кроме Excel и системных программ ничего в момент загрузки не используется.
Изменено: AzatKukanov - 20.04.2023 09:07:53 (Представлены данные о компьютере)
 
 
 
Страницы: 1
Читают тему
Наверх