In this series of blog posts, we will be examining some of the key features of Power BI Desktop, Power Query and the Power BI Service needed to pass exam DA-100: Analyzing Data with Microsoft Power BI.

In this Power BI training post, we will examine DA-100 exam – Get data from different data sources: Use or create a PBIDS file.

Use or create a PBIDS file

For this topic, we have a folder called PBIDS, which contains a few examples of PBIDS files.

Web Connector PBIDS File

However, we do not want to launch these files in Power BI Desktop; we want to look at the text inside the file. So, instead of double clicking, let us right-click, and choose Open with Notepad, and of course, if you do not see Notepad in the list, just click on Choose Another App. If necessary, look at More Apps, and then find Notepad, or any text or code editor that you have on your machine.

This is what a PBIDS file looks like.

PBIDS files are used to encapsulate connections to data sources. We can assume that PBIDS stands for Power BI data source. PBIDS files are useful for standardising the data source connection experience, and this is particularly useful for new users of Power BI; and also to make sure that everyone is doing things in exactly the same way: everyone is connecting to the version of the file that they are supposed to connect to, or to a particular database that they are supposed to connect to, etc.

In spite of the file extension that is used, a PBIDS file is essentially a JSON file, and inside the JSON file, we have two top level elements, version, and connections. Then inside connections, we have details, options, and mode, and details is the node which contains the key information, the protocol.

For a web connection, the protocol is "http". Then we have the address, i.e., the URL that we are connecting to; authentication, which for this data source is "null" and then query, which again, for this data source is "null".

So, that is what a PBIDS file is. But how do you create them? Well, since it is a JSON file, you could just write it from scratch. However, a more convenient method is simply to export a PBIDS file from any solution which contains the data source in question.

So, let us open the file called "09-Use or create a PBIDS file.pbix", and here we have a basic mini report, which is based on the same data source that we were looking at inside the PBIDS file just a moment ago.

To generate a PBIDS file, we simply go into Transform data, and click on Data Source settings. Then, we just click on Export PBIDS.

Now let us go inside the Data > PBIDS folder, and then let us just overwrite the file "1-Web.PBIDS" which we opened a minute ago, by double clicking the filename then clicking "Yes" to replace the original.

This, then, is by far the most convenient method of generating a PBIDS file; simply open up a file that has the connection to the data source in question, and then export a PBIDS file from that data source.

Text/CSV PBIDS File

Now let us return to our PBIDS folder and look at a few different connections. So, we have had a look at web. Let us look at a file connection. So, let us right-click on CSV.pbids, and then choose Open with Notepad, and you can see that we have the same basic format as we saw when we looked at the web example. The protocol this time is "file" instead of "web", and instead of URL we now have a path node.

When entering a file path containing backslashes in a JSON file, the backslashes must be escaped. We do that simply by doubling them up; i.e., we convert all single backslashes to double backslashes. In Notepad, we can do that by typing Ctrl H (Edit > Replace), replace one backslash with two, and then click Replace All.

When we double click on CSV.pbids, it will connect to the specified CSV file and show us the CSV Navigator dialog; and, of course, with a CSV file, we are taken straight into a preview of the data. Because the CSV file is the data, there is no further navigation required; you see the data straight away.

Excel PBIDS File

The format of an Excel PBIDS file is almost identical to that of a Text/CSV one. The only subtle difference is that, instead of a path to a CSV file, we have the path to an Excel file. But all the other parameters are identical.

This time when we open up the PBI DS file, we will not be shown data. As per usual, we will be shown the contents of the workbook. So, using the same protocols as for a CSV file, we have successfully connected to an Excel file. But this time of course, because the Excel workbook is a container, it is up to us to navigate to one or more of the items inside that container, and if you remember, Power BI recognises tables, worksheets and named ranges.

Folder PBIDS File

Next, let us have a look at connecting to a folder using a PBIDS file, and the only difference between connecting to a file and connected to a folder is the protocol. Instead of "file", we have "folder". However, the location of the folder is also specified as a path.

When we double click to open "Folder.pbids", the Navigator dialog starts by showing a preview of the contents in the specified folder, and then from here, you would click on Combine And Transform.

Then you are given an option to specify a sample file, which will determine the columns that are retrieved from each file.

Let us look at our final example which is connecting to SQL Server. So, here we have two key elements: the name of the server, and optionally, the name of the database.

You will also see that we use a protocol of "tds". Then for address, instead of URL or path, we have our server and our database.

Then, we have an example of one of the options that you can have, "CreateNavigationProperties", which is set to "false".

Then, finally, the "mode", and there are three options here. We can have "null", in which case, the default will kick in, and the default is import mode. We can also have "DirectQuery" as we have here, or we could explicitly type "import".

When we open the PBIDS file, we are taken straight into the Navigator, and we can then specify which views or tables we want to bring in.

So, for this demo, let us just going to connect to the pbi.Clients view, and click on Load, just to test that we do in fact, end up with DirectQuery mode.

Sure enough, in the bottom right of the screen, you will see storage mode DirectQuery.

And of course, if we go into model view, highlight the client stable that we brought in. In the advanced section. We will of course see Storage mode is set to DirectQuery.

So hopefully this gives you a flavour of what PBIDS files are all about.