PL-300: Power BI Exam Prep: Connecting to data sources

PL-300: Power BI Exam Prep: Connecting to data sources

If you want to pass the PL-300 exam, there are some areas in that your skills will be assessed, and one of those is identifying and connecting to the data source and changing data source settings. Power BI Supports over 140 data sources, each data source has some connection configurations. Below you will learn the steps required for connecting to a data source.


Identify the data source

When you want to build a Power BI report, think of all the data sources that you need to populate the data from it. An Excel file that includes the monthly budget can be a data source, or the data stored in a data warehouse in an Oracle database can be the source for example. Sometimes your data isn’t directly accessible. You have the data in an operational system, which you cannot access directly, so you may have a few options here, such as finding out how to connect to the data source of that operating system or searching for an API that provides such information to you (usually provided by the provider of the operating system), or you can just export data as CSV or text files from the operating system to use that as a source for Power BI.

Connecting to Data Source: Get Data in Power BI Desktop

No matter where the data is coming from, your point to connect to that data source is usually from the Power BI Desktop using the Get Data option;

Get data in Power BI Desktop

The data source you want might be already listed here, which in that case you just select it from the list (such as an Excel workbook). Or if the data source isn’t listed here, you can click on More and that will list all the possible data sources categorized, which you can also search for anything in there.

All the Power BI data sources

Once you have selected the data source, you will have the option to set up the connection. For example, for an Excel file, this will be the location of the file. For a SQL Server database, this would be the server and the database connection details.

Setting up the data source connection details in Power BI

As you see in the above screenshot, you can specify the server address and the database name for the SQL Server database. Some of the data sources might have the option to choose the connection type of Import or DirectQuery, which we will discuss in a later chapter.

Once you set the connection details, depending on the data source type, you may be asked for the credentials to log in to the source system. For example, for a SQL Server database, the credentials to connect to the database and server will be asked.

Setting the credentials to connect to the data source from Power BI Desktop

Note that the data source credential is separated from the data source connector details.

The data source connector details will be stored as part of the Power BI file and if you open the file in another system, you will still have the connection details stored in it. However, the credentials are stored on each system separately. This is done for security purposes. so that if you send the file to someone else, they should be able to refresh the data only if they have the credential to connect to the data source. The same rule also applies when you publish the file to the service. The credentials have to be set in the service separately.

Once you connect to a data source, you will be able to see all the data entities under that source, and can choose the table(s) to get data from;

Select data tables to import into Power BI

Data Source Settings

If you ever want to change the setting of a data source, then you can go to the Data source settings. This option is available in the Power BI Desktop under Transform Data

Finding Data Source Settings from the Power BI Desktop

If you are in the Power Query Editor, this option is accessible directly in the home tab;

Finding Data Source Settings from the Power Query Editor

In the Data Source Settings, you will be able to see all the data sources that you are connected to in this Power BI file.

Data Source Settings in Power BI

If you want to change the source, for example, from one SQL Server database to another SQL Server database on a different server, then you can choose Change Source, and then specify the details of the new source;

Changing the data source in Power BI

If you just want to change the credential to access the data source, then you can either Edit Permissions Or Clear Permissions and then re-enter it.

Changing the permissions or credentials of a data source in Power BI

If within the data source, you want to change the data table or the spreadsheet that the data is populated from, then you can do this in the Power Query Editor. Use the Transform Data in the Power BI Desktop to get to the Power Query Editor;

Transform Data in Power BI Desktop

You can choose the query on the left side, and then go to the Navigation step and click on the settings;

Navigation settings in a query in Power Query Editor

Depending on the data source, you can see a navigation UI asking you to choose the table you want (The below screenshot), or you may just change that in the Formula bar (The above screenshot).

Navigation changes in the Power BI and Power Query

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog:

Leave a Reply