Страницы: 1
RSS
Запрос Power Query грузит из файла в 2-3 раза больше мегабайт, чем весит сам файл-источник
 
Добрый день!
Кто может подсказать: почему запрос Power Query к книге excel (расширение .xlsx) может грузить из файла объём больший, чем вести сам файл-источник? Например, гружу из файла размером 100 Мб, а запрос загружает из него аж 300 мегабайт! Причём, загрузка требуется каждый раз заново как бы "с нуля" при добавлении некоторых шагов в запросе, таких как, например, группировка или сведённый столбец. При этом фоновое обновление в параметрах отключено.
И можно ли как-то уменьшить объём загрузки, чтобы, соответственно, ускорить выполнение запроса?
Заранее благодарен тем кто ответит!  :)

Для примера: приложен файл ексель, код запроса к нему такой:
Код
let
    Источник = Excel.Workbook(File.Contents("C:\Рабочий стол\Источник данных.xlsx"), null, true),
    Источник_Sheet = Источник{[Item="Источник",Kind="Sheet"]}[Data],
    #"Повышенные заголовки" = Table.PromoteHeaders(Источник_Sheet, [PromoteAllScalars=true]),
    #"Измененный тип" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"Договоры", type text}, {"Тип договора", type text}, {"Столбец 2", type text}, {"Показатель", type number}}),
    #"Сведенный столбец" = Table.Pivot(#"Измененный тип", List.Distinct(#"Измененный тип"[#"Тип договора"]), "Тип договора", "Показатель", List.Sum),
    #"Замененное значение" = Table.ReplaceValue(#"Сведенный столбец",null,0,Replacer.ReplaceValue,{"Долгосрочный", "Среднесрочный", "Краткосрочный"}),
    #"Сгруппированные строки" = Table.Group(#"Замененное значение", {"Договоры", "Столбец 2"}, {{"Долгосрочные", each List.Sum([Долгосрочный]), type nullable number}, {"Среднесрочные", each List.Sum([Среднесрочный]), type nullable number}, {"Краткосрочные", each List.Sum([Краткосрочный]), type nullable number}})
in
    #"Сгруппированные строки"
Изменено: Йозеф Швейк - 13.11.2021 16:57:03
Да здравствует Государь-Император Франц Иосиф Первый!
 
Ну файл ведь наверное архив?
 
Цитата
написал:
Ну файл ведь наверное архив?
Файл .xlsx. Сорри, забыл указать в вопросе.
Да здравствует Государь-Император Франц Иосиф Первый!
 
Ну да, архив.
 
То есть сам файл .xlsx сам по себе является архивом? А, кажется понял. Т.е. то, что при загрузке объём больше, чем размер файла, это нормально, так и должно быть ?)
Да здравствует Государь-Император Франц Иосиф Первый!
 
Xlsx - это Zip архив
 
Да точно - похоже что вы правы!! Файл .xlsx можно распаковать с помощью 7-Zip, и в результате получается папка, весящая в несколько раз больше!
Вы просто открыли для меня другой мир   :)  
Да здравствует Государь-Император Франц Иосиф Первый!
 
Можно распаковать, посмотреть файлы, изменить... Кстати иногда бывает полезно посмотреть что за значения на самом деле там в прописаны для ячеек, когда Эксель ведёт себя непойми как...
 
А что касается другого пункта моего вопроса:
Цитата
написал:
Причём, загрузка требуется каждый раз заново как бы "с нуля" при добавлении некоторых шагов в запросе
Можно ли как-то сделать так, чтобы при обновлении предварительного просмотра после добавления шагов файл не прогружался заново с нуля? )
Да здравствует Государь-Император Франц Иосиф Первый!
 
Цитата
Йозеф Швейк написал:
И можно ли как-то уменьшить объём загрузки, чтобы, соответственно, ускорить выполнение запроса?
Можно. Как и для форума, сделайте для редактирования кода файл-пример с небольшим объемом данных.
Цитата
Йозеф Швейк написал:
Файл .xlsx.
Если есть возможность, используйте txt/csv для хранения данных, это сильно ускорит загрузку. К примеру, отчеты 1С сохраняйте именно в txt. Загрузка
Цитата
Йозеф Швейк написал:
гружу из файла размером 100 Мб, а запрос загружает из него аж 300 мегабайт!
Опять у Вас тема без примера. Невозможно, не увидев код, сказать, в чем дело. Возможны варианты, когда источник будет загружаться несколько раз.
Цитата
Йозеф Швейк написал:
при обновлении предварительного просмотра после добавления шагов файл не прогружался заново с нуля?
В режиме редактора изменение кода приводит к пересчету результата запроса. Выход описан в ответе на первую цитату.
Изменено: surkenny - 13.11.2021 15:56:15
 
Сделал небольшой пример. Во вложении таблица исходных данных сокращённая ("Источник данных.xlsx"). Предположим, что в ней не пара тысяч строк, а около миллиона, и весит > 100Мб)
Из другого файла я к ней применяю следующий запрос:
Код
let
    Источник = Excel.Workbook(File.Contents("C:\Рабочий стол\Источник данных.xlsx"), null, true),
    Источник_Sheet = Источник{[Item="Источник",Kind="Sheet"]}[Data],
    #"Повышенные заголовки" = Table.PromoteHeaders(Источник_Sheet, [PromoteAllScalars=true]),
    #"Измененный тип" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"Договоры", type text}, {"Тип договора", type text}, {"Столбец 2", type text}, {"Показатель", type number}}),
    #"Сведенный столбец" = Table.Pivot(#"Измененный тип", List.Distinct(#"Измененный тип"[#"Тип договора"]), "Тип договора", "Показатель", List.Sum),
    #"Замененное значение" = Table.ReplaceValue(#"Сведенный столбец",null,0,Replacer.ReplaceValue,{"Долгосрочный", "Среднесрочный", "Краткосрочный"}),
    #"Сгруппированные строки" = Table.Group(#"Замененное значение", {"Договоры", "Столбец 2"}, {{"Долгосрочные", each List.Sum([Долгосрочный]), type nullable number}, {"Среднесрочные", each List.Sum([Среднесрочный]), type nullable number}, {"Краткосрочные", each List.Sum([Краткосрочный]), type nullable number}})
in
    #"Сгруппированные строки"

После ввода шагов "Столбец Сведения" и "Группировка" почему-то начинает заново грузиться файл-источник, как будто он не запоминается в буфере обмена. И далее, при внесении новых шагов, тоже почему-то исходный файл каждый раз грузится с нуля и предварительный просмотр обновляется очень-очень долго.
Как можно изменить код так, чтобы исходный файл загрузился только один раз(при открытии запроса), и далее редактирование запроса работало моментально?)

Также может быть: тут дело не в коде запроса, а нужно что-то поменять в параметрах? Подскажите плиз кто разбирается
Да здравствует Государь-Император Франц Иосиф Первый!
 
Йозеф Швейк, а для чего вам Table.Group, если вы уже все сделали в Table.Pivot ?
 
Йозеф Швейк, Антон Вам правильно говорит. Нафиг уберите группировку:)
Если проводите аналитику потом в PP, то вообще pivot не делайте.
И null на 0 меняется для эстетического оргазма при предпросмотре?:)
Изменено: surkenny - 13.11.2021 18:01:14
 
Цитата
для чего вам Table.Group, если вы уже все сделали в Table.Pivot ?
Цитата
И null на 0 меняется для эстетического оргазма при предпросмотре?
Группировка потом делается по другому набор столбцов, нежели Pivot. В примере приведены не все столбцы, но сути это не меняет.
Null на 0 меняю, чтобы делать потом  в запросе математические операции с этими столбцами.
Есть ли способы ускорить быстродействие запроса, кроме как удалять из него шаги ?)
Да здравствует Государь-Император Франц Иосиф Первый!
 
Цитата
написал:
Причём, загрузка требуется каждый раз заново как бы "с нуля" при добавлении некоторых шагов в запросе,
Можно в копии источника оставить 1000 строк и разработать полностью запрос с этим источником
Когда запрос будет полностью готов останется заменить источник
 
Да не используйте кнопку цитирования, когда цитата - как собаке пятая лапа! [МОДЕРАТОР]

Да это вариант! Хотя есть минус - придётся делать эту копию каждый раз при разработке нового запроса.
Протестировал также сохранение источника в формате csv - ускорение несущественное.
Нашёл, что обсуждалась ранее какая-то функция Table.Buffer, но я так и не понял: как её применять, и ускоряет ли она в итоге выполнение запроса, или наоборот тормозит ?) Кто в курсе про Table.Buffer ?)
Изменено: vikttur - 14.11.2021 13:53:39
Да здравствует Государь-Император Франц Иосиф Первый!
 
На самом деле это две разных проблемы в PQ и разрешаются они по-разному:
Цитата
Йозеф Швейк написал:
гружу из файла размером 100 Мб, а запрос загружает из него аж 300 мегабайт!
Когда видите такое - поздравляю, PQ считывает ваш источник 3 раза в рамках одного запроса. Это связано с особенностями движка выполняющего вычисления. Конкретно в вашем запросе такое поведение это следствие использования функции Table.Pivot совместно с List.Distinct. Избавиться от этого можно либо переделав код, либо помещением таблицы в буфер перед подобным преобразованием.
Цитата
Йозеф Швейк написал:
загрузка требуется каждый раз заново как бы "с нуля" при добавлении некоторых шагов в запросе
Это особенности редактора запросов, да, он регулярно пересчитывает ваши шаги. Часть он пытается кэшировать на диск, но механизм кэширования в Экселе немножко корявый и при работе с объемными источниками получается долгий простой, так что тут только создать себе "песочницу" путем обрезания источника для разработки запроса, а потом в релиз уже запускать полный источник, предварительно все протестировав на нем.
Изменено: PooHkrd - 15.11.2021 09:52:33
Вот горшок пустой, он предмет простой...
 
Цитата
написал:
это следствие использования функции Table.Pivot совместно с List.Distinct
Спасибо - сразу видно профессионала!  :)
Я попробовал переделать код так:
Код
let    
    Источник = Excel.Workbook(File.Contents("C:\Рабочий стол\Источник данных.xlsx"), null, true),    Источник_Sheet = Источник{[Item="Источник",Kind="Sheet"]}[Data],
    #"Повышенные заголовки" = Table.PromoteHeaders(Источник_Sheet, [PromoteAllScalars=true]),
    #"Измененный тип" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"Договоры", type text}, {"Тип договора", type text}, {"Столбец 2", type text}, {"Показатель", type number}}),
    TheList = List.Buffer(#"Измененный тип"[#"Тип договора"]),
    TheTable = Table.Buffer(#"Измененный тип"),
    #"Сведенный столбец" = Table.Pivot(TheTable, List.Distinct(TheList), "Тип договора", "Показатель", List.Sum),
    #"Замененное значение" = Table.ReplaceValue(#"Сведенный столбец",null,0,Replacer.ReplaceValue,{"Долгосрочный", "Среднесрочный", "Краткосрочный"}),
    #"Сгруппированные строки" = Table.Group(#"Замененное значение", {"Договоры", "Столбец 2"}, {{"Долгосрочные", each List.Sum([Долгосрочный]), type nullable number}, {"Среднесрочные", each List.Sum([Среднесрочный]), type nullable number}, {"Краткосрочные", each List.Sum([Краткосрочный]), type nullable number}})
in
    #"Сгруппированные строки"
Но почему-то не помогло - запрос так же грузит в несколько раз больше размера исходного файла, и время загрузки не изменилось! Подскажите плиз: что я делаю не так? Как надо?) Может надо предварительно как-то специально чистить кэш?)
Изменено: Йозеф Швейк - 16.11.2021 00:54:04
Да здравствует Государь-Император Франц Иосиф Первый!
Страницы: 1
Наверх