Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Оптимизация запроса Power Query
 
#1
15.09.2020 08:55:06
Привет Всем!
У меня на листе есть коды продуктов, и каждому продукту на сети (в разной глубине подпапок) лежат по несколько картинок (в формате pdf, jpg, png). Наименование картинок не очень хорошее  :) . Мне необходимо получить напротив кода путь к одной из его картинок. (любой)
Благодаря данному форуму я собрал запрос и он работает, но очень долго обновляется  :(
Подскажите пожалуйста, его возможно оптимизировать? И если да, то как?
Возможно, кто-то даст советы по оптимизации на будущее?
Код
let
    BOM = Excel.CurrentWorkbook(){[Name="BOM"]}[Content],
    BOM.УдалитьСтолбцы = Table.SelectColumns(BOM,{"3", "4", "11", "12", "22"}),
    BOM.Фильтр = Table.SelectRows(BOM.УдалитьСтолбцы, each ([3] = 71398)),
    BOM.ИзмененТип = Table.TransformColumnTypes(BOM.Фильтр,{{"11", type text}}),
    BOM.Переименовать = Table.RenameColumns(BOM.ИзмененТип,{{"3", "SKU"}, {"4", "Наименование SKU"}, {"11", "Код материала"}, {"12", "Наименование материала"}, {"22", "Комментарии к изменениям"}}),
    
    Materials = Folder.Files(Excel.CurrentWorkbook(){[Name="tPath2"]}[Content]{0}[Path]),
    Materials.Фильтр = Table.SelectRows(Materials, each Text.Contains([Extension], ".pdf") or Text.Contains([Extension], ".jpg") or Text.Contains([Extension], ".jpeg") or Text.Contains([Extension], ".png")),
    Materials.Фильтр2 = Table.SelectRows(Materials.Фильтр, each not Text.Contains([Extension], "~$")),
    Materials.Путь = Table.AddColumn(Materials.Фильтр2, "Ссылка на дизайн", each [Folder Path]&[Name]),
    Materials.УдалитьСтолбцы = Table.SelectColumns(Materials.Путь,{"Name", "Ссылка на дизайн"}),
    Materials.Join     = Table.Join(BOM.Переименовать,{},Materials.УдалитьСтолбцы,{}),
    Materials.Merge    = Table.SelectRows(Materials.Join, each Text.Contains([Name],[Код материала]))&Materials.УдалитьСтолбцы,
    Materials.Filtr = Table.SelectRows(Materials.Merge, each [Код материала] <> null),
    Materials.Dubl = Table.Distinct(Materials.Filtr, {"Код материала"}),

    Materials2 = Folder.Files(Excel.CurrentWorkbook(){[Name="tPath2"]}[Content]{1}[Path]),
    Materials2.Фильтр = Table.SelectRows(Materials2, each Text.Contains([Extension], ".pdf") or Text.Contains([Extension], ".jpg") or Text.Contains([Extension], ".jpeg") or Text.Contains([Extension], ".png")),
    Materials2.Фильтр2 = Table.SelectRows(Materials2.Фильтр, each not Text.Contains([Extension], "~$")),
    Materials2.Путь = Table.AddColumn(Materials2.Фильтр2, "Ссылка на дизайн", each [Folder Path]&[Name]),
    Materials2.УдалитьСтолбцы = Table.SelectColumns(Materials2.Путь,{"Name", "Ссылка на дизайн"}),
    Materials2.Join     = Table.Join(BOM.Переименовать,{},Materials2.УдалитьСтолбцы,{}),
    Materials2.Merge    = Table.SelectRows(Materials2.Join, each Text.Contains([Name],[Код материала]))&Materials2.УдалитьСтолбцы,
    Materials2.Filtr = Table.SelectRows(Materials2.Merge, each [Код материала] <> null),
    Materials2.Dubl = Table.Distinct(Materials2.Filtr, {"Код материала"}),

    Materials3 = Folder.Files(Excel.CurrentWorkbook(){[Name="tPath2"]}[Content]{2}[Path]),
    Materials3.Фильтр = Table.SelectRows(Materials3, each Text.Contains([Extension], ".pdf") or Text.Contains([Extension], ".jpg") or Text.Contains([Extension], ".jpeg") or Text.Contains([Extension], ".png")),
    Materials3.Фильтр2 = Table.SelectRows(Materials3.Фильтр, each not Text.Contains([Extension], "~$")),
    Materials3.Путь = Table.AddColumn(Materials3.Фильтр2, "Ссылка на дизайн", each [Folder Path]&[Name]),
    Materials3.УдалитьСтолбцы = Table.SelectColumns(Materials3.Путь,{"Name", "Ссылка на дизайн"}),
    Materials3.Join     = Table.Join(BOM.Переименовать,{},Materials3.УдалитьСтолбцы,{}),
    Materials3.Merge    = Table.SelectRows(Materials3.Join, each Text.Contains([Name],[Код материала]))&Materials3.УдалитьСтолбцы,
    Materials3.Filtr = Table.SelectRows(Materials3.Merge, each [Код материала] <> null),
    Materials3.Dubl = Table.Distinct(Materials3.Filtr, {"Код материала"}),

    Materials4 = Folder.Files(Excel.CurrentWorkbook(){[Name="tPath2"]}[Content]{3}[Path]),
    Materials4.Фильтр = Table.SelectRows(Materials4, each Text.Contains([Extension], ".pdf") or Text.Contains([Extension], ".jpg") or Text.Contains([Extension], ".jpeg") or Text.Contains([Extension], ".png")),
    Materials4.Фильтр2 = Table.SelectRows(Materials4.Фильтр, each not Text.Contains([Extension], "~$")),
    Materials4.Путь = Table.AddColumn(Materials4.Фильтр2, "Ссылка на дизайн", each [Folder Path]&[Name]),
    Materials4.УдалитьСтолбцы = Table.SelectColumns(Materials4.Путь,{"Name", "Ссылка на дизайн"}),
    Materials4.Join     = Table.Join(BOM.Переименовать,{},Materials4.УдалитьСтолбцы,{}),
    Materials4.Merge    = Table.SelectRows(Materials4.Join, each Text.Contains([Name],[Код материала]))&Materials4.УдалитьСтолбцы,
    Materials4.Filtr = Table.SelectRows(Materials4.Merge, each [Код материала] <> null),
    Materials4.Dubl = Table.Distinct(Materials4.Filtr, {"Код материала"}),
    
    Materials5 = Folder.Files(Excel.CurrentWorkbook(){[Name="tPath2"]}[Content]{4}[Path]),
    Materials5.Фильтр = Table.SelectRows(Materials5, each Text.Contains([Extension], ".pdf") or Text.Contains([Extension], ".jpg") or Text.Contains([Extension], ".jpeg") or Text.Contains([Extension], ".png")),
    Materials5.Фильтр2 = Table.SelectRows(Materials5.Фильтр, each not Text.Contains([Extension], "~$")),
    Materials5.Путь = Table.AddColumn(Materials5.Фильтр2, "Ссылка на дизайн", each [Folder Path]&[Name]),
    Materials5.УдалитьСтолбцы = Table.SelectColumns(Materials5.Путь,{"Name", "Ссылка на дизайн"}),
    Materials5.Join     = Table.Join(BOM.Переименовать,{},Materials5.УдалитьСтолбцы,{}),
    Materials5.Merge    = Table.SelectRows(Materials5.Join, each Text.Contains([Name],[Код материала]))&Materials5.УдалитьСтолбцы,
    Materials5.Filtr = Table.SelectRows(Materials5.Merge, each [Код материала] <> null),
    Materials5.Dubl = Table.Distinct(Materials5.Filtr, {"Код материала"}),

    Materials.Combine = Table.Combine({Materials.Dubl, Materials2.Dubl, Materials3.Dubl, Materials4.Dubl, Materials5.Dubl}),
    Materials.Combine2 = Table.Combine({Materials.Combine, BOM.Переименовать}),
    Materials.Duble= Table.Distinct(Materials.Combine2, {"Код материала"})
in
    Materials.Duble
Изменено: evgeniygeo - 15.09.2020 08:56:35
 
 
 
#2
15.09.2020 09:12:31
Во первых, если не нужно содержимое подпапок, то можно использовать Foder.Contents вместо Folder.Files. Это уже будет супер ускорение. Если же все таки содержимое подпапок требуется, то я бы сначала объединил в одну таблицу все обращения к папкам, а уже после этого крутил бы фильтры, джойны и вот это вот все.
Вот горшок пустой, он предмет простой...
 
 
 
#3
15.09.2020 09:14:51
PooHkrd,
нужно содержимое подпапок разной глубины
Цитата
PooHkrd написал:
не нужно содержимое подпапок
я пробовал, но получилось дольше.
сейчас попробую найти данную попытку
Цитата
PooHkrd написал:
сначала объединил в одну таблицу все обращения
Изменено: evgeniygeo - 15.09.2020 09:20:49
 
 
 
#4
15.09.2020 09:19:10
В таком случае лучше как-то научиться парсить имя файла, чтобы выделить из него код материала, и джойнить уже по ключевым столбцам, а не декартом.
Цитата
evgeniygeo написал:
я пробовал, но получилось дольше
А в буфер то таблицу после объединения помещали? Или так дальше и обрабатывали?
Изменено: PooHkrd - 15.09.2020 09:22:17
Вот горшок пустой, он предмет простой...
 
 
 
#5
15.09.2020 09:37:48
PooHkrd,
а Вы не подскажите как это возможно?
Цитата
PooHkrd написал:
парсить имя файла,
Эм...простите за глупый вопрос, но это как?
Цитата
PooHkrd написал:
А в буфер то таблицу после объединения помещали? Или так дальше и обрабатывали?
Вот, что у меня получилось при объединении. По скорости примерно одинаково.  :cry:
Код
let
    BOM = Excel.CurrentWorkbook(){[Name="BOM"]}[Content],
    BOM.УдалитьСтолбцы = Table.SelectColumns(BOM,{"3", "4", "11", "12", "22"}),
    BOM.Фильтр = Table.SelectRows(BOM.УдалитьСтолбцы, each ([3] = 71398)),
    BOM.ИзмененТип = Table.TransformColumnTypes(BOM.Фильтр,{{"11", type text}}),
    BOM.Переименовать = Table.RenameColumns(BOM.ИзмененТип,{{"3", "SKU"}, {"4", "Наименование SKU"}, {"11", "Код материала"}, {"12", "Наименование материала"}, {"22", "Комментарии к изменениям"}}),
    
    Materials = Folder.Files(Excel.CurrentWorkbook(){[Name="tPath2"]}[Content]{0}[Path]),
    Materials2 = Folder.Files(Excel.CurrentWorkbook(){[Name="tPath2"]}[Content]{1}[Path]),
    Materials3 = Folder.Files(Excel.CurrentWorkbook(){[Name="tPath2"]}[Content]{2}[Path]),
    Materials4 = Folder.Files(Excel.CurrentWorkbook(){[Name="tPath2"]}[Content]{3}[Path]),
    Materials5 = Folder.Files(Excel.CurrentWorkbook(){[Name="tPath2"]}[Content]{4}[Path]),

    Materials.Combine = Table.Combine({Materials, Materials2, Materials3, Materials4, Materials5}),
    Materials.Фильтр = Table.SelectRows(Materials.Combine, each Text.Contains([Extension], ".pdf") or Text.Contains([Extension], ".jpg") or Text.Contains([Extension], ".jpeg") or Text.Contains([Extension], ".png")),
    Materials.Фильтр2 = Table.SelectRows(Materials.Фильтр, each not Text.Contains([Extension], "~$")),
    Materials.Путь = Table.AddColumn(Materials.Фильтр2, "Ссылка на дизайн", each [Folder Path]&[Name]),
    Materials.УдалитьСтолбцы = Table.SelectColumns(Materials.Путь,{"Name", "Ссылка на дизайн"}),
    Materials.Join     = Table.Join(BOM.Переименовать,{},Materials.УдалитьСтолбцы,{}),
    Materials.Merge    = Table.SelectRows(Materials.Join, each Text.Contains([Name],[Код материала]))&Materials.УдалитьСтолбцы,
    Materials.Filtr = Table.SelectRows(Materials.Merge, each [Код материала] <> null),
    Materials.Dubl = Table.Distinct(Materials.Filtr, {"Код материала"}),

    Materials.Combine2 = Table.Combine({Materials.Dubl, BOM.Переименовать}),
    Materials.Duble= Table.Distinct(Materials.Combine2, {"Код материала"})
in
    Materials.Duble
Изменено: evgeniygeo - 15.09.2020 09:40:26
 
 
 
#6
15.09.2020 09:44:37
Цитата
evgeniygeo написал:
Эм...простите за глупый вопрос, но это как?
В строке 14 пишите так. Остальное в запросе не меняете.
Код
Materials.Combine = Table.Buffer( Table.Combine({Materials, Materials2, Materials3, Materials4, Materials5} ) ),

Пробуйте.
Можно регулярками, можно другими способами, тут главное увидеть все возможные наименования файлов, чтобы понимать как очищать их от "мусора" чтобы выделить код.
Изменено: PooHkrd - 15.09.2020 09:47:08
Вот горшок пустой, он предмет простой...
 
 
 
#7
15.09.2020 13:21:07
PooHkrd,
почти не помогло
Цитата
PooHkrd написал:
В строке 14 пишите так.
Сделал с помощью регулярного выражения, но почему-то при объединении не находит и работает еще дольше(((
Цитата
PooHkrd написал:
Можно  регулярками ,
Код
let   fx=(txt as text, regex as text, delim as text)=>
    Web.Page(
        "<script>
            var x = '" & txt & "';
            var delim = '" & delim & "';
            var pattern = /" & regex & "/gi;
            var result = x.match(pattern).join(delim);
            document.write(result);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}

in
fx
Код
let
    Materials = Folder.Files(Excel.CurrentWorkbook(){[Name="tPath2"]}[Content]{0}[Path]),
    Materials2 = Folder.Files(Excel.CurrentWorkbook(){[Name="tPath2"]}[Content]{1}[Path]),
    Materials3 = Folder.Files(Excel.CurrentWorkbook(){[Name="tPath2"]}[Content]{2}[Path]),
    Materials4 = Folder.Files(Excel.CurrentWorkbook(){[Name="tPath2"]}[Content]{3}[Path]),
    Materials5 = Folder.Files(Excel.CurrentWorkbook(){[Name="tPath2"]}[Content]{4}[Path]),

    Materials.Combine = Table.Buffer(Table.Combine({Materials, Materials2, Materials3, Materials4, Materials5})),
    #"Сохраненные первые строки" = Table.FirstN(Materials.Combine,5),
    Materials.Фильтр = Table.SelectRows(#"Сохраненные первые строки", each Text.Contains([Extension], ".pdf") or Text.Contains([Extension], ".jpg") or Text.Contains([Extension], ".jpeg") or Text.Contains([Extension], ".png")),
    Materials.Фильтр2 = Table.SelectRows(Materials.Фильтр, each not Text.Contains([Extension], "~$")),
    Materials.Регулярка = Table.AddColumn(Materials.Фильтр2, "fxRegExpExtract", each fxRegExpExtract([Name], "(\d{7,8})", ";")),
    #"Измененный тип" = Table.TransformColumnTypes(Materials.Регулярка,{{"fxRegExpExtract", Int64.Type}}),
    Materials.Путь = Table.AddColumn(Materials.Регулярка, "Путь", each [Folder Path]&[Name])
in
    Materials.Путь
Код
let
    BOM = Excel.CurrentWorkbook(){[Name="BOM"]}[Content],
    BOM.УдалитьСтолбцы = Table.SelectColumns(BOM,{"3", "4", "11", "12", "22"}),
    BOM.Фильтр = Table.SelectRows(BOM.УдалитьСтолбцы, each ([3] = 71398)),
    BOM.Переименовать = Table.RenameColumns(BOM.Фильтр,{{"3", "SKU"}, {"4", "Наименование SKU"}, {"11", "Код материала"}, {"12", "Наименование материала"}, {"22", "Комментарии к изменениям"}}),
    #"Измененный тип" = Table.TransformColumnTypes(BOM.Переименовать,{{"Код материала", Int64.Type}}),
    BOM.Объед = Table.NestedJoin(#"Измененный тип", {"Код материала"}, Запрос1, {"fxRegExpExtract"}, "Запрос1", JoinKind.LeftOuter),
    #"Развернутый элемент Запрос1" = Table.ExpandTableColumn(BOM.Объед, "Запрос1", {"Путь"}, {"Путь"})
in
    #"Развернутый элемент Запрос1"
Изменено: evgeniygeo - 15.09.2020 13:26:59
 
 
 
#8
15.09.2020 13:25:11
Запрос один-единственный и только его можно оптимизировать? Предложите название темы
 
 
 
#9
15.09.2020 13:30:17
vikttur,
понимаю. Я думал, что будет какой-то общий ответ, но видимо нет.
Тема: "Поиск путей к файлам по части названия с помощью PQ"
Изменено: evgeniygeo - 15.09.2020 13:31:35
 
 
 
Страницы: 1
Наверх