Millington Offices (10).png

How to connect to Folders without the hassle of predefined Functions/Parameters

Updated: Dec 1, 2020

Using Power BI Folder Connector can be a real pain. Whenever you use it Power BI will automatically create Parameters, Sample Files and Functions that sometimes can make your model refresh run incredibly slow (I'm sure if you're reading this you feel my pain). In this tutorial I will show you how to optimise the queries so that you don't need to use all those components.

Let's start!

1. On the Home ribbon click on the Get Data button. Select Folder on the right side and click Connect.

2. Next select the folder path that you want to get the files from, Click OK to continue.

3. Next we are presented with the loaded files within our selected folder. Click Transform Data at the bottom of the screen. The Query Editor window will open. You will see a lot of different columns with information related to the files. We won't need most of those columns so we can just delete them. Select the columns Content and Name. With those two columns selected right click on the header and select Remove Other Columns.

4. We are now getting to the juicy part of this tutorial. We will add a new column that will get the contents of our xlsx files. Click the Add Column ribbon and press the Add Custom Column on the left side of the ribbon.

5. We will name this new column DataLoad and will add the Excel.Workbook function. This function is the trick that will allow us to get the contents of our files without having to use functions or parameters. After inserting the function correctly click OK.