Consume Local File System As A Data Source In Power BI

Overview

We all know that Power BI is a very good business intelligence tool. Sometimes, there is a situation when we need to prepare a report using a file system. In this article, we will learn how.

Example

I want to prepare a report for all the available files in one of the specific folders from my local system. I also want to filter the files based on creation and modification dates and their extension.

Power BI provides a connector to consume the data from the folder structure. We can get all files from a specific folder but there is one limitation with this connector; it provides us a list of all files in a flat file structure instead of TreeView structure.

Now, let’s get started.

At the end of the article, you will be able to produce a similar kind of report. You can also download a report from here.

Step 1

Open Power BI Desktop.

Step 2

Click on Get Data > File > Folder. Then, click "Connect".

Step 3

It asks for local file system path. Click on "Browse" and select the folder for which you want to prepare the Power BI report.

In my case, I will select "Downloads" folder from the local file system.

Press OK. It will take some time to grab data from a file system.

Step 4

Now, our data source is ready. We get the following properties for our folder structure.

Date accessed
Date created
Date modified
Extension
Folder Path
Name

The below screenshot shows the data for download folder.

Step 5

Now, we will set all our visuals. First, let’s add slicer visual to filter the data based on creation date.

Note - You can format the visual as per your requirement.

Secondly, let’s add a slicer visual to filter the data based on the date modified.

Step 6

I want to show file count by extension. So, I will use the table visual.

I have added the following columns to the table.

Extension
Extension > Right click > count = So It will become Count of Extension.

The table will look like this.

Step 7

I want to add a file name with description. So, I will use table visual again.

I have added the following columns to this table.

Name
Folder Path

Now, our table will look like this.

Step 8

Now, I want to represent the file count by extension in a graphical form with a pie chart. So, I will add a Pie chart and add the following columns.

In Legend field, I will add > Extension

In Values field, I will add > Count of Extension

So, my Pie Chart will look like this.

When I hover on any visual, it will show the file extension with file count.

Example

In this case, it is showing .xml extension is having 274 files in the "downloads" folder.

Step 9

Now, I want to show the total number of files available in a folder. For that, I will use Card from visual library.

Select "Name" from the right side, check on it, and check "Count (Distinct)".

It will look like this.

Step 10

Now, I want to display the total file count by extension. So, I will use the card.

Step 11

Now, let’s filter based on file extension. I want to see the list of .pdf files only. I will select .pdf extension from pie chart.

My data is filtered based on the .pdf extension.

Step 12

Let’s filter data based on created date and modified date. Drag the slicer or select date from date picker.

Data is filtered based on the selected slicer.

Conclusion

This is how we can prepare Power BI report using file system data source.

1 Comment

Join the conversation! Your thoughts help the community grow.

do do6y ago
Hi,Microsoft Office Documents do have built-in properties, such as Title, Subject, Company, Author, Manager. It migth be great to have them available in PowerBI. They are available even in the File Explorer, so it shouldn't be that hard to grab them for PowerBi. In addition the Advanced Properties (aka Custom Properties) are also of interest, but let us start with the built in and PowerBi. Do you have a great solution or evenbetter a .pbix file as an example? Thank you for your expertise, support and response :-) Do