Добрый день! Кто может подсказать: почему запрос 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
#"Сгруппированные строки"
То есть сам файл .xlsx сам по себе является архивом? А, кажется понял. Т.е. то, что при загрузке объём больше, чем размер файла, это нормально, так и должно быть ?)
Да здравствует Государь-Император Франц Иосиф Первый!
Да точно - похоже что вы правы!! Файл .xlsx можно распаковать с помощью 7-Zip, и в результате получается папка, весящая в несколько раз больше! Вы просто открыли для меня другой мир
Да здравствует Государь-Император Франц Иосиф Первый!
Можно распаковать, посмотреть файлы, изменить... Кстати иногда бывает полезно посмотреть что за значения на самом деле там в прописаны для ячеек, когда Эксель ведёт себя непойми как...
Сделал небольшой пример. Во вложении таблица исходных данных сокращённая ("Источник данных.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
#"Сгруппированные строки"
После ввода шагов "Столбец Сведения" и "Группировка" почему-то начинает заново грузиться файл-источник, как будто он не запоминается в буфере обмена. И далее, при внесении новых шагов, тоже почему-то исходный файл каждый раз грузится с нуля и предварительный просмотр обновляется очень-очень долго. Как можно изменить код так, чтобы исходный файл загрузился только один раз(при открытии запроса), и далее редактирование запроса работало моментально?)
Также может быть: тут дело не в коде запроса, а нужно что-то поменять в параметрах? Подскажите плиз кто разбирается
Йозеф Швейк, Антон Вам правильно говорит. Нафиг уберите группировку:) Если проводите аналитику потом в PP, то вообще pivot не делайте. И null на 0 меняется для эстетического оргазма при предпросмотре?
для чего вам Table.Group, если вы уже все сделали в Table.Pivot ?
Цитата
И null на 0 меняется для эстетического оргазма при предпросмотре?
Группировка потом делается по другому набор столбцов, нежели Pivot. В примере приведены не все столбцы, но сути это не меняет. Null на 0 меняю, чтобы делать потом в запросе математические операции с этими столбцами. Есть ли способы ускорить быстродействие запроса, кроме как удалять из него шаги ?)
Да здравствует Государь-Император Франц Иосиф Первый!
написал: Причём, загрузка требуется каждый раз заново как бы "с нуля" при добавлении некоторых шагов в запросе,
Можно в копии источника оставить 1000 строк и разработать полностью запрос с этим источником Когда запрос будет полностью готов останется заменить источник
Да не используйте кнопку цитирования, когда цитата - как собаке пятая лапа! [МОДЕРАТОР]
Да это вариант! Хотя есть минус - придётся делать эту копию каждый раз при разработке нового запроса. Протестировал также сохранение источника в формате csv - ускорение несущественное. Нашёл, что обсуждалась ранее какая-то функция Table.Buffer, но я так и не понял: как её применять, и ускоряет ли она в итоге выполнение запроса, или наоборот тормозит ?) Кто в курсе про Table.Buffer ?)
На самом деле это две разных проблемы в PQ и разрешаются они по-разному:
Цитата
Йозеф Швейк написал: гружу из файла размером 100 Мб, а запрос загружает из него аж 300 мегабайт!
Когда видите такое - поздравляю, PQ считывает ваш источник 3 раза в рамках одного запроса. Это связано с особенностями движка выполняющего вычисления. Конкретно в вашем запросе такое поведение это следствие использования функции Table.Pivot совместно с List.Distinct. Избавиться от этого можно либо переделав код, либо помещением таблицы в буфер перед подобным преобразованием.
Цитата
Йозеф Швейк написал: загрузка требуется каждый раз заново как бы "с нуля" при добавлении некоторых шагов в запросе
Это особенности редактора запросов, да, он регулярно пересчитывает ваши шаги. Часть он пытается кэшировать на диск, но механизм кэширования в Экселе немножко корявый и при работе с объемными источниками получается долгий простой, так что тут только создать себе "песочницу" путем обрезания источника для разработки запроса, а потом в релиз уже запускать полный источник, предварительно все протестировав на нем.
написал: это следствие использования функции 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
#"Сгруппированные строки"
Но почему-то не помогло - запрос так же грузит в несколько раз больше размера исходного файла, и время загрузки не изменилось! Подскажите плиз: что я делаю не так? Как надо?) Может надо предварительно как-то специально чистить кэш?)