Как увеличить скорость работы в Power Query?
Бывают такие ситуации, когда в редактор Power BI вы загружаете большие объемы данных, например, таблицы с миллионами строк. Если у вас в редакторе запросов (Power Query) таких запросов много, и вы пытаетесь производить с ними определенные действия: обрабатывать, объединять, добавлять, то Power Query может «подтормаживать», долго обрабатывать информацию, тем более если ваш компьютер или ноутбук слабоваты и это в целом ведет к увеличению времени работы.
На этот процесс можно повлиять, и я хочу поделиться таким советом: нужно временно ограничить число строк в конкретном запросе.
Например, в вашем запросе миллион строк, вам же нужно временно оставить в этом запросе 100 строк. После того, как вы ограничите количество строк до 100, сделайте все необходимые преобразования запросов и в заключение удалите тот шаг, в котором вы ограничивали количество строк. Тем самым вы ускорите весь процесс работы с запросами.
Вот как это реализовывается на практике:
Редактор запросов Power Query:
На главной странице нажимаем «Изменить запросы»
В данной таблице 996 строк. Для примера ограничим таблицу 20 строками.
В основном поле, в левом верхнем углу правой кнопкой мыши нажимаем на значок на пересечении столбцов и строк.
В появившемся меню нажимаем «Сохранить верхние строки…».
Затем в открывшемся окне указываем цифру 20.
Наша таблица уменьшилась до 20 строк.
Теперь мы можем работать с этим запросом (чистить, модернизировать, соединять с другими запросами и т.д.) и эти действия будут проводиться достаточно быстро, потому что количество строк уменьшилось до 20.
Для примера, создадим шаг, состоящий из нового столбца.
На вкладке «Добавление столбца» нажимаем «Настраиваемый столбец»
Как ускорить работу power query
I saw an interesting post the other day on the Power Query Technet forum which showed how the List.Buffer() function could be used to improve calculation performance. This is something I’d seen hinted at in other places so I thought it was worth a bit of investigation.
Consider the following query:
It gets the first 2000 rows from the FactInternetSales table in the Adventure Works DW database, removes most of the columns, and adds a custom column that shows the rank of the current row based on its Sales Amount.
On my laptop it takes around 35 seconds to run this query – pretty slow, in my opinion, given the amount of data in this table.
However, using the List.Buffer() function in the RankValues step like so:
Makes the query run in just 2 seconds. The List.Buffer() function stores the sorted list of values used to calculate the rank in memory which means it will only be evaluated once; in the original query it seems as though this step and those before it are being evaluated multiple times. Curt Hagenlocher’s comment (on this thread) on what List.Buffer() does for a similar calculation is telling:
The reason for this is that M is both functional and lazy, so unless we buffer the output of List.Select, we’re really just building a query that needs to be evaluated over and over. This is similar to the Enumerable functions in LINQ, if you’re familiar with those.
Table.Buffer() and Binary.Buffer() functions also exist, and do similar things.
A few other points to make:
- This is not necessarily the optimal way to calculate ranks in Power Query – it’s just an example of how List.Buffer() can be used.
- In the first query above, query folding is not taking place. If it had been it’s likely that performance would have been better. Since using List.Buffer() explicitly prevents query folding from taking place, it could make performance worse rather than better because of this in many cases.
- I’m 100% certain you’ll get much better performance for a rank calculation by loading the table to the Excel Data Model/Power Pivot and writing the calculation in DAX. You should only really do calculations like this in Power Query if they are needed for other transformations in your query.
Share this:
Like this:
21 responses
Hm, that’s pretty cool. I’d wonder when exactly to use this though — pity PQ doesn’t really show much of what’s going on under the hood.
So here you’d conclude you should buffer the result knowing you’re referring to an idempotent List function in an iteration?
I’d wonder why only List/Table/Binary though — as if “M is both functional and lazy” would apply to those types.
I suffered an issue of uncontrolled reloading when trying to use Power Query for scraping. Time to see if this’d solve that..
The Load function I’d written previously to load functions from text files also suffered constant reloading, and may well benefit from Buffer.Binary() as well.
Then again, with Excel 2016 exposing PQ to VBA, one could also just use VBA to import all PQ functions from some master spreadsheet. Hm, or to batch import queries from different files for that matter… I’m seeing some possibilities here.
Lists, Tables and Binary values are the only types of values which can stream. For records, we need to know the keys up front. All other values are scalar.
Okay, I see… Thanks for clarifying!
This is a topic that should have been better documented, as buffering lists (tables also) can have a significant positive impact like you’ve shown here and as shown in Curt’s example. I’ve written a lot of custom functions that takes one or more lists as inputs, and buffering these lists prior to passing to the functions can improve performance by several orders of magnitude.
From what I can tell, buffering does two things: 1) Evaluates the list, table, or binary expression eagerly, 2) persists the value in memory so that so that it doesn’t have to be re-evaluated on subsequent calls. I think that stopping query folding on tables is a side effect of buffering, rather than being its main role.
Thanks for the excellent post!
To add a bit to informal documentation here… I tried using a buffer, found an error in my results due to bad input in a cell, then tried refreshing the query. Didn’t work — the buffer kept the bad value in. I then tried saving and reopening the sheet, after which a query refresh did succeed.
So buffers persist through query refreshes, though not through workbook closes. The latter was expected I guess.
Makes it a bit tricky though, when to buffer — trying to avoid unneeded recalculation, yet having the ability to recalculate all the parts that need a fix to go through while still aiming to avoid other unneeded recalculation. I suppose it only really becomes a relevant question in bigger data processing systems based on M though, which might not be a common use-case yet…
I think this buffering should probably help address a lot of the issues I experienced with Power Query though, so as far as I’m concerned this will help push things toward bigger projects.
This question of allowing the user more control over buffers is kind of interesting though. Then there’s the thing about persistence (across closes) as well though — it’s actually making me think if having M interface with something like Redis might help for that. Considering all it’d need would be HTTP requests and (de)serialization, that actually seems feasible to me too…
On a similar note, I suppose using HTTP requests to communicate with a (local) server might actually also enable (indirectly) interfacing with other languages, COM APIs, message queues… I’m barely starting to fathom the extent of possibilities here.
I used one of the buffer commands (I can’t remember which now and it’s late :-)) when doing an oData query to an Excel spreadsheet stored on a Office 365 SharePoint Document Library. Without the buffer command it was excruciatingly slow (like minutes to execute a few lines of M against about 30 lines of data in the spreadsheet). With the Buffer command it executed in seconds.
Hi. Might be interesting. I’m curious as to how I could implement it for what I’m currently doing. Here’s my situation :
I’m using PowerQuery to prep and merge fairly large DataSet (10 M rows, 30 cols, .csv feed). I’m using good hardware with 32Go RAM, yet I’m experiencing some difficulties.
When loading either to PPVT or spreadsheet, excel freezes, takes humongous amount of time (10h +), or simply won’t finish very simple queries and the reason eludes me.
RAM is not jammed at all, CPU is at about 30%, and it simply looks like the thing is not happening.
Any advice on how to do merges / prep with such files in PQ ? (query folding isn’t an option since inputs are flat files on my laptop.
Are you using the 32-bit version of Excel or the 64-bit version? You’ll need the latter if you are working with large amounts of data.
I use CSV files for a lot of feeds into our reporting (doncha just love Sage Line 50 :-)) and it seems to me that you need 64-bit even for fairly small datasets, particularly if you are pulling data from SharePoint Lists as well.
I’m using 64-bit version. I’m a PowerQuery fan for the GUI (saves a lot of time and avoids many mistakes), but i’m really astonished at how poorly M performs compared to say Pandas (python library).
I did a quick benchmark on the same laptop for a simple task: Merging two 7M rows local .csv files on 4 columns, leaving aside the fact that you can’t write back to .csv in PowerQuery for large files (I hope they implement that soon).
– Pandas merges and writes back to csv in about 2 min.
– PowerQuery merges and exports it to PPVT in 20+ min, and you’re often left wondering if it will ever finish (especially when you do a filtering step somewhere).
1/ Is there something I don’t get about this?
2/ How come M’s native functions can’t even outperform a low performance lib like pandas?
3/ Why can’t M just load both tables in RAM and be done with it? (raising the limit in cache doesn’t seem to do anything)
I’m still gonna stick with M for dataprep since I make very few mistakes thanks to the GUI, but it performs really poorly for some tasks.
@AC_6_9: To be fair, I don’t think Pandas really counts as low performance since Numpy just offloads most of the heavy lifting to BLAS, which is meticulously optimized native performance.
More cynically speaking, I’d regard M as a toy language for its lack of performance profiling tools. Realistically, you’re gonna have no clue what the heck is going on under the hood, and to what extent it’s being silly recalculating the same things a bunch of times (which was my experience when inspecting network requests, IIRC not fixed after adding Buffer methods).
All evidence so far points to you being right. I guess I just don’t understand how you can reach such underwhelming performances after devoting MS-like resources to developing a language specifically design for data prep.
Was this an unavoidable trade-off for the GUI? Or is the language just not mature enough?
@AC_6_9: When I used Power Query, I’d gotten to the point of just writing M instead of using the GUI. Which is probably further than most.
I ended up feeling the language was a huge pain to write manually (having to swap around names and commas each time you add a line or swap too around), and made a comment to the devs on the official support forum to that extent. They don’t care.
This lead me to believe that they themselves don’t take M seriously enough, and just see the GUI as the main attraction here — understandable, since arguably that was the main added value.
AFAIK SQL Server hasn’t support M yet either, which is probably another sign, though at least Power BI did start using it.
I’m gonna guess the main thing here is that Microsoft themselves probably do little using M though, which absolves them from really experiencing its pain points themselves. And I believe they did indeed market this whole thing as “self-service BI”.
In my ears that does kind of translate to “this is not real enough for us to use in our own critical corporate projects, but hey, we’re marketing it to you since you probably can’t hire enough smart people to do real code”.
I’ll have to concede my personal take-away is that language design and all of this is freaking hard, and hence they’re sticking with they’re own niche of “well, it can do a few things through a GUI”. By hard, I mean having to decide when to (re)calculate things (as apparently M feels the need to do), as well as order of execution w.r.t. side effects in functional languages.
I’m not sure if you’re familiar with Haskell, but it’s one of the only other languages that can decide its own order of execution like M, but unlike it has also solved those questions w.r.t how to address order of execution in cases where it does matter. That learning curve though is probably why it hasn’t gained great traction.
So as to maturity, I’m gonna go ahead and guess things ain’t about to change.
You’ll wonder what I’m doing on this blog, since I’ve stopped using it. This was just one of a couple threads I just so turned out to still get reply notifications for.
In your scenario, for tasks that you’d estimate would take too long to run on M, you’d probably be faster off just writing them out in Python instead.
I can’t speak for the Power Query/Power BI dev team, but I thought I’d add some comments here…
Certainly there are lots of issues with performance that need to be addressed, but as far as I can see they are being worked on. One of the key features of Power Query – query folding, or the ability to translate transformations back into something the data source can understand, like SQL – is getting better with each monthly release. I know this isn’t relevant for text files, but it is relevant for many other scenarios. At the end of the day storing your data in something like a relational database and making M into a universal query generator is going to lead to better overall scalability than any approach which tries to solve performance problems on the desktop.
As far as the dev environment goes I agree it’s painful. I have heard rumours that there will be improvements in the coming month but as Tycho says, the UI is the main selling point here, not the underlying language. Indeed it’s clear that Microsoft is promoting Python and R as languages for data analysis quite heavily (eg with their integration into Visual Studio, Azure Machine Learning etc) and M isn’t seen in the same way. Why would Microsoft invest in M as a language for data analysis and manipulation when R and Python already exist? M came before the UI, it’s true, and it’s important to have it available for situations where you need to do more complex things than the UI currently allows, but the UI is what allows a much broader range of users to work with data and that mass market is where Microsoft traditionally has most success.
Предупреждение о производительности Power Query для Power BI
Имея большой опыт работы с преобразованиями Power Query, мы хотели бы поделиться с вами одним простым, но важным предостережением. Если вы часто используете Power Query, этот совет может значительно улучшить производительность вашего преобразования. Количество шагов, которые вы добавляете в запрос, зависит от производительности вашего преобразования данных (если у вас слишком много шагов). Мы продемонстрируем это вам на примере
Слишком много переменных
Это пример файла Power Query, в котором я делаю очень простое преобразование. Оно добавляет один к существующему числу. Однако в этом примере мы делаем это за тысячи шагов! один шаг за раз, мы добавляем тысячи к числу. Основная причина сделать это таким образом — показать вам, какова производительность, которую вы получаете, когда у вас слишком много переменных (или, допустим, шагов) в Power Query.
Вот наш пример запроса:
. // each variable used in the next variable with a plus one
Вышеуказанный запрос занимает 15 минут для запуска на моей машине Surface Book 2 с процессором Core i7 и памятью 16 ГБ! 15 минут, в течение которых вы не можете докоснуться до Power BI. Он не будет реагировать на ваши действия, вам придется подождать, чтобы увидеть результат после выполнения запроса.
Ой! Это действительно долгое время для запроса, который просто добавляет одно значение на каждом шагу, не так ли? Посмотрим, как потребление ресурсов в системе работает с Power BI Desktop и Power Query. Здесь он только на полпути:
Э-э! Даже с простым вычислением, например, добавлением одного к номеру, я имел более 10 ГБ памяти и 70% использования ЦП в течение длительного периода! Как вы думаете, что это вызвало? Конечно, число переменных. В этом запросе нет ничего другого.
Слишком много переменных или слишком много шагов вызывают проблемы с производительностью. Power Query выделяет память для каждой переменной, а потребление памяти значительно увеличивается. Процессор также занимает много времени для обработки этого количества переменных с помощью Power Query Engine.
Конечно, этот пример был преувеличенным примером слишком большого числа переменных. У вас никогда не было бы 1800 переменных. Однако этот пример также был для переменной с единственным простым числовым значением. В большинстве случаев ваши переменные представляют собой таблицы со многими строками и столбцами данных. Таким образом, это может произойти в реальном сценарии для вас даже с помощью сотен шагов или переменных. другими словами, вам нужно посмотреть количество переменных.
Слишком много переменных вызовет проблемы с производительностью!
Каково решение?
Ну, если число переменных является причиной проблемы, в первую очередь можно уменьшить количество переменных. Если я изменю запрос выше на тот, что представлен ниже, я получу результат менее чем за секунду!
Вышеуказанный запрос выполняется менее чем за секунду, по сравнению с предыдущим запросом, который занял 15 минут! Единственное различие — это число переменных. Итак, вот вот выдержка для этого поста:
Посмотрите количество переменных (шагов) в Power Query: если переменных слишком много, то лучше всего объединить некоторые из них.
Важное примечание. Не подумайте, что вам нужно объединить все ваши переменные. Обычно вы не видите проблемы с производительностью при менее ста переменных, эта проблема начинает появляться, когда число выходит за рамки этого. Один из важных моментов, который вам нужно учитывать, заключается в том, что если вы комбинируете переменные вместе, то отладка, устранение неполадок или обслуживание кода будет сложнее. Поэтому используйте это решение только в случае необходимости.
Power Query Ускорение запроса №1. Удаляем лишние шаги
Подписчик попросил у меня совета по ускорению выполнения запроса. Оказалось, что в запросе очень много лишних шагов. Эти ошибки совершают очень многие начинающие пользователи Power Query, а именно совершают очень много лишних бессмысленных шагов.
Удалим лишние шаги. На рисунке ниже слева показаны шаги автора, а справа исправленный запрос. Мы удалим 15 шагов из 21.
Решение
Сначала мы удалим повторяющиеся шаги «Изменить тип». Можно всего лишь 1 раз изменить тип для всех нужных столбцов.
Дальше мы исправим шаги, которые фильтруют таблицу. Таких шагов 2. Во-первых, фильтрацию таблицы нужно делать как можно раньше, а во-вторых, нужно эти шаги объединить, ведь функция Table.SelectRows позволяет нам ввести намного больше одного условия.
Потом мы удалим лишние шаги «Переупорядоченные столбцы». В большинстве случаев порядок столбцов вообще не нужно никак трогать, а тем более делать это несколько раз.