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.
Video
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;
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.
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.
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.
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;
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
If you are in the Power Query Editor, this option is accessible directly in the home tab;
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.
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;
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.
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;
You can choose the query on the left side, and then go to the Navigation step and click on the settings;
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).
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: https://radacad.com/blog.