.png)
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.
=Excel.Workbook([Content])6. We will now have a new column called DataLoad. Next click the Expand button located at the right of our newly added column. Unselect the option Use original column name as prefix and Click OK.
7. The data contained in our excel files is on the Data Column. In the previous step we added a bunch of other columns we don't actually need so we are no going to remove them and make everything more tidy.
Select the columns Name and Data and right click on the column header, then select Remove Other Columns.
8. We have everything tidy up now! If you click in any of the rows in the Data column you will realise the headers are not being promoted. To fix this we are going to add a new custom column.
On the Add Column ribbon click Add Custom Column again. Name the column PromoteHeaders and enter the Table.Promote Headers function. Click OK.
=Table.PromoteHeaders([Data])9. There is just one last part for us to have all our data files combined. Click the Expand Button, unselect the Use original column name as prefix option and click OK.
And there you go! Same results as the standard get files from folder connector but without the functions, parameters and sample files.
In this tutorial I used Excel files as an example. You can also do the same thing with CSV files. The DataLoad function will be slightly different though.
For CSV files...
1. Instead of using the Excel.Workbook() function in step 5, use the function CSV.Document()
=CSV.Document([Content])2. Because this time we are trying to load CSV files, you can jump directly from step 5 to step 7. Step 6 will no longer apply in the case you're using folders containing CSV files.
Hope you enjoyed this tutorial! Leave your comments, ideas, bellow. Subscribe to get the latest posts about Power BI.
Kommentare