Skill 1.1: Get data from different data sources
No matter what your data source is, you need to get data in Power BI before you can work with it. Power BI can connect to a wide variety of data sources, and the number of supported data sources grows every month. Furthermore, Power BI allows you to create your own connectors, making it possible to connect to virtually any data source.
The data consumption process begins with an understanding of business requirements and data sources available to you. For instance, if you need to work with near-real-time data, your data consumption process is going to be different compared to working with data that is going to be periodically refreshed. As you’ll see later in the chapter, different data sources support different connectivity modes.
This skill covers how to:
- Identify and connect to a data source
- Change data source settings
- Select a shared dataset or create a local dataset
- Select a storage mode
- Use Microsoft Dataverse
- Change the value in a parameter
- Connect to a dataflow
Identify and connect to a data source
There are over 100 native connectors in Power BI Desktop, and the Power BI team is regularly making new connectors available. When connecting to data in Power BI, the most common data sources are files, databases, and web services.
Need More Review? Data Sources In Power BI
The full list of data sources available in Power BI can be found at https://docs.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources.
To choose the right connector, you must know what your data sources are. For example, you cannot use the Oracle database connector to connect to a SQL Server database, even though both are database connectors.
Note Companion Files
In our examples, we are going to use this book’s companion files, which are based on a fictitious company called Wide World Importers. Subsequent instructions assume that you placed all companion files in the C:\PL-300 folder.
To review the skills needed to get data from different data sources, let’s start by connecting to the WideWorldImporters.xlsx file from this book’s companion files:
On the Home tab, select Excel workbook.
In the Open window, navigate to the WideWorldImporters.xlsx file and select Open.
In the Navigator window, select all eight check boxes on the left; the window should look similar to Figure 1-1.
FIGURE 1-1 The Navigator window
Select Transform Data.
After you complete these steps, the Power Query Editor window opens automatically; you can see it in Figure 1-2.
FIGURE 1-2 Power Query Editor
If in the Navigator window you chose Load, the Power Query Editor window would not open, and all Excel sheets you selected would be loaded as is.
Note that the Navigator window shows you a preview of the objects you selected. For example, in Figure 1-1 we see the preview of the Targets for 2022 sheet; its shape suggests we need to apply some transformations to our data before loading it, because it has some extraneous information in its first few rows.
Note Data Preview Recentness
To make query editing experience more fluid, Power Query caches data previews. Therefore, if your data changes often, you may not see the latest data in Power Query Editor. To refresh a preview, you can select Home > Refresh Preview. To refresh previews of all queries, you should select Home > Refresh Preview > Refresh All.
The Navigator window is not unique to the Excel connector; indeed, you will see the same window when connecting to a complex data source like a database, for instance.
We are going to transform our data later in this chapter. Before we do that, let’s connect to another data source: a folder. While you are in Power Query Editor:
On the Home tab, select New Source. If you select the button label instead of the button, select More.
In the Get data window, select Folder and then Connect.
Select Browse, navigate to C:\PL-300\Targets, and select OK twice. At this stage, you should see the list of files in the folder like in Figure 1-3.
FIGURE 1-3 List of files in C:\PL-300\Targets
Select Combine & Transform Data.
In the Combine Files window, select OK without changing any settings.
At this stage, you have connected to two data sources: an Excel file and a folder, which contained several comma-separated values (CSV) files.
Although we did not specify the file type when connecting to a folder, Power Query automatically determined the type of files and applied the transformations it deemed appropriate. In addition to Excel and CSV files, Power BI can connect to several other file types, including JSON, XML, PDF, and Access database.
Important Format Consistency It is important that the format of the files in a folder be consistent—otherwise, you may run into issues. Power Query applies the same transformations to each file in a folder, and it decides which transformations are necessary based on the sample file you choose in the Combine Files window.