Adding New Queries Takes Forever

[ddbaker]

Hi Everyone,

To begin, I'm fairly new to Power BI, and my background is in Finance not Data Science. I recognize that building good models requires proper training and technique, however, I have undertaken the task to build our a financial dashboard for the company I work for. Because of my lack of knowledge, I'm certain that I have used very inefficient techniques to load our financial data into one Power BI file, and quite frankly I just don't know any other way.

The model I created aggregates all of our monthly financial data (PnL, Balance Sheet, Budgets, Forecasts, etc.). We have uploaded around 200 queries into Power Query (and manipulated the queries to be in the format we need) and then appended around 150 of them into one big query that we use for our visuals. We link all of our queries to OneDrive, so they update whenever anyone in our organization makes a change. One major problem that we're having is anytime we want to add a new query (i.e. The financials for a new month), it takes anywhere from 30 mins to an hour, and most often results in our computers running out of memory. 

I'm certain there's a better way to organize our data, but I lack the training and experience to know of a different way. Do you guys organize your dataset outside of Power BI first and then import it? If so, what programs do you use? Any other tips for improving the performance of our model would be greatly appreciated.

[v-yingjl]

Hi @ddbaker ,

As @ edhans mentioned, adding multi queries for each file in power query is inefficient. Since you are using Onedrive, you can try to sotre these files in a folder of Onedrive and connect to Onedrive folder to combine necessary files and load into power bi, refer: Power BI Get Data from Multiple Files in a Folder on OneDrive for Business, No Gateway Needed 

In addition, for improving data model performance, you can also refer these documents and viedos that could help you:

1. Power Query Performance Optimization 
2. Optimization guide for Power BI 
3. Power BI Performance Optimization Tips 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

It sounds like @ddbaker you are adding a new query for each file. This is very inefficient. You should instead be using the Combine Files function which will let Power Query connect to 1 or 1,000 files in a single folder (or range of folders if in SharePoint) that mean as new files are added, Power Query automatically picks them up. There are more details on this process here.

Ideally, you'd stick these files in a SharePoint site in one folder, then user SharePoint.Contents to consume them. You can use SharePoint.Files (the default SharePoint folder connection) but it scans the entire SP site. SharePoint.Contents only looks at one folder, and it is my go-to connection for SharePoint files.

You can also use Azure Data Lake for Excel and CSV files, which has even better performance, but that would require a bit of assitance form someone in your IT department to get a subscription, set up a Datalake site, and a process to upload and process files.

[ddbaker]

Hi @edhans, thank you so much for the response. I apologize for the delay in getting back to you. I didn't know about the Combine feature in Power Query, so thank you for telling me about it. I've been trying it out this morning and doing some research, and I'm running into a couple of issues. First, the files I download are CSV exports of our Profit and Loss and Balance Sheets from QuickBooks. Each export goes through the exact same steps to transform it into "flat" data (i.e. Unpivoting, renaming columns, etc.) that can be used for analysis in PBI. Hence my first approach of transforming each individual export (by copying and pasting the code and changing the source) and then appending into a single query. If I were to use the "Combine" feature, would I have to transform each individual file (in Excel Power Query or something similar) and then upload each transformed file into a folder that can be used to Combine all the data? I'm willing to take the time to do this, but I want to make sure there isn't a better way before I spend the time doing it. I appreciate your help!

[edhans · Accepted solution]

Hi @ddbaker  - no, you can do the transformations in Power Query.

1. Do the combine operation as normal.
2. You will have a large listing of data. That needs to be cleaned up.
3. Go to the "Sample Query" that Power Query created as part of the combine operation. From there, do the individual unpivots, renaming, etc. So you are transforming one file there.
4. Power Query will then use that logic and apply to every file and drop it in your final combined query.

[ddbaker]

@edhans Thank you for all your help. I got all that to work, now my only struggle is connecting to SharePoint. I've read several articles and watched a couple YouTube videos, and I keep getting the error: DataSource.Error: SharePoint: Request failed: The remote server returned an error: (400) Bad Request. (Bad Request). I've never had this error whenever I connect to single files via SharePoint. Any idea what I'm doing wrong? Do you perhaps have a good article you can link to that will explain how to do combines with SharePoint?

[edhans]

This article @ddbaker will walk you through the process. It is very simple, so if you are getting a bad request, either you are combining files that cannot be combined (word files for example), or your SharePoint admin has some security setting set that you need to discuss with them and get it fixed. It is not anything you are doing wrong in Power Query itself as long as it is Excel, PDF, JSON, XML, or CSV files.

[DeonDP]

Thank you for your post @edhans.  I have the same issue where it takes forever to process any change.  Will most surely investigate what you propose.

On the othe hand - @ddbaker , my laptop was also running out of RAM because I only had 8Gb of RAM.  I upgraded the RAM to 16GB and my computer RAM runs at 50-60% most of the time - problem solved, but it is still taking forever to process changes, hence why I will investigate what @ddbaker suggests.

[DeonDP]

By the way @ddbaker , I am also new to Power BI and on a huge learning journey.  I give credit to the masters on this forum for sharing - it has certainly help me a lot.