Scrolling to End of CSV Dataset Takes Long Time

[Shawn_Eary]

I have a small imported dataset of about 15 columns and 8331 rows with a file size of 1.27MB. I am not using DirectQuery.

When I'm in the Power Query Editor, it takes me a long time to get to the last row of the dataset. 50 Page Down requests seem to take about 10 seconds to process. So to use Page Down to get to the end of my 8331 row Data Set, I'm maybe looking at approximately 50 seconds to get to the last row:

If I increase my dataset 10x to approximately 80,000 rows, then I guess my system would take about 8 minutes to get to the last row if I tried using the Page Down command in the Power Query Editor.

Unfortunately, Ctrl+End does not seem to work in the Power Query Editor to take me to the last row like it does in SQL Server Management Studio (SSMS), Azure Data Studio and Visual Studio Code (Raw unimported File CSV Only) [1]. When I use SSMS and Azure Data Studio to query an underlying view that closely resembles the test data, I get responsive results and Ctrl+End works as I would expect [2].

Since I presume Visual Studio Code and Azure Data Studio are both written in JavaScript [3][4], I don't understand why the Power Query Editor of Power BI is so much slower than them.

Is there some way to speed up the paging of the Power Query Editor in Power BI or at least make Ctrl+End work like it does in other similar tools?

OTHER: If the Power BI interface could be rewritten in JavaScript like Visual Studio Code and Azure Data Studio, it would likely be a trivial matter for Microsoft to enable Power BI support on GNU\Linux platforms without using Wine.

[1] - SSMS and Azure Data Studio are used against CSV data that was already imported into an MS SQL Server DB. Visual Studio Code is used on raw CSV that was exported from an MS SQL Server DB before it was imported into Power BI.

[2] - Page Down is highly responsive in both SSMS and Azure Data Studio against the test data. Unfortunately, while I can constantly hold page down to rapidly scroll to the last row in SSMS, Azure Data Studio prevents me from doing that "trick" even if Page Down is still responsive. I do not consider Page Down to be responsive in the Power Query Editor of Power BI.

[3] - https://en.wikipedia.org/wiki/Visual_Studio_Code

[4] - https://cloudblogs.microsoft.com/sqlserver/2022/07/27/azure-data-studio-july-release-with-vs-code-up...

[Daryl-Lynch-Bzy]

Hi @Shawn_Eary - I am wondering why you want to scroll through the Power Query table.  It is relatively small csv so why not open the file in Excel Power Query and load it into a Worksheet.  If you have SSMS you can connect to the local Power BI instance to query the Dataset (Using SSMS with Power BI (dbakevlar.com).

[Shawn_Eary]

@Daryl-Lynch-Bzy- Thanks for the help 😀

---

I am wondering why you want to scroll through the Power Query table.

---

Bad habit I suppose. As mentioned, I'm used to just hitting Ctrl+End to jump to the end of SQL Queries in various SQL platforms. My memory isn't that great these days, but I think that even used to work in the Java based Oracle Developer Studio.

Paging down is just something I often do in an SQL Query tool to scan through the data. The problem here is my old "bad habit" seems kind of slow in the Power Query Editor.

---

It is relatively small csv so why not open the file in Excel Power Query and load it into a Worksheet.

---

Yes, the data is already in MS SQL Server Express, so I can query it that way. I originally used DirectQuery to access the data from Power BI. Unfortunately, despite the fact that T-SQL is way easier than DAX, I think my team would rather I just import the data and process it directly in Power BI instead of using an MS SQL Server Express database. I have other tables in this dataset so having the SQL Server DB around is useful, but I need team approval to keep that SQL Server DB live.

Yes, I could probably look at this data in Excel. That might be more performant than using the Power Query Editor in Power BI. I also think there is something called Rainbow CSV for Visual Studio Code in which I can use Rainbow Query Language while looking directly at the original CSV file.

---

If you have SSMS you can connect to the local Power BI instance to query the Dataset (Using SSMS with Power BI (dbakevlar.com).

---

That approach sounded really cool at first, but when I tried to run a basic query, I got an error:

Error: Either the user, '[myUsername]', does not have permission to access the referenced mining model, '[theTable_I_wantedToQuery]', or the object does not exist.

After looking at the article, I noticed there seems to be a caveat:
"As you can see above, the Execute button is missing from the toolbar and is missing, no matter if you open an XMLA, DAX, MDX and for the SQL Query, you can’t connect to a database engine. The database isn’t a full SQL Server or even appear to be a Windows Internal Database, (WID)." - dbakevlar - Pot’Vin-Gorman

What I would really like to see is SSMS/T-SQL query like functionality in Power BI to replace or supplement Power Query M and DAX. Users could import a bunch of CSV files and then pretend Power BI was their own little local SQL Server instance. Then they wouldn't have to have team approval to setup an SQL Server Express instance since Power BI is often considered to be a pre-authorized tool in many organizations/companies.

I guess the problem I run into in these forums is that I often feel the need to present hypothetical, reduced set or abstracted problems for "security" reasons. While I often work public knowledge type tasks, I don't want to overextend myself by accidentally posting something sensitive someday.

My question is perhaps a question of scalability. What happens to the Power Query Editor when we have two or three tables that need to be joined and we have 10 or 100 times as many rows? My guess is that SSMS and Azure Developer Studio will handle that situation much more gracefully. Of course, I must admit the Power Query Editor seems (to me) more advanced than SSMS or Azure Developer Studio. For that reason we should expect Power Query Editor to be a little slower and take up more RAM, but even with its superior feature set, I currently feel the Power Query Editor is slower than it should be.

[Shawn_Eary]

I guess the workarounds for this are to use the Transform -> Count Rows operation and to sort/filter via columns (as needed), but it would be nice to see the desktop client act a bit more snappy and for Ctrl+End to work like it does in other Microsoft applications.

Right now with the Power Query Editor menu open on a Query, Power BI is eating up 971 MB of my RAM. SSMS on the same machine is looking at a similar query with all rows processed and is only using 111 MB. Finally, a similar query with all rows processed on Azure Data Studio seems to be taking up 144 MB on a GNU\Linux machine.