Power BI is fast becoming the go-to tool for most data analysts all over the world, with its robust capacity and multiple data connectors. Microsoft Power BI is a business intelligence (BI), data visualization, and reporting tool used by enterprises and individuals for their data analysis and insight generation.
The Power BI Work Process
The Microsoft Power BI Desktop supports the ETL (Extract, Transform, and Load) process used in most data analysis. Data can be extracted from numerous sources, transformed to meet the desired schema or structure, and then loaded into a target data model for reporting and analysis as part of Microsoft Power BI.
Extract/Ingestion
This is the first step in the whole ETL process, it involves extracting data from a variety of sources, including cloud-based sources like SharePoint Online, Azure Storage, and OneDrive. The process also supports extracting data from on-premises sources like PostgreSQL, SQL Server, Excel, CSV, and other formats.
Get Data
In your Power BI Desktop at the Home tab click on Get Data which can be found in the top left corner. This shows you all the available data connections in Power BI.
Data Transformation
This is the 2nd step of the Power BI process. Power BI has an inbuilt data cleaning and transformation tool called PowerQuery. After data has been extracted, the Power Query Editor is used in changing the data format to the right schema or structure. Power Query allows you to filter data, eliminate duplicates, split columns, merge tables, and do other data transformation activities.
Power Query is a tool for data transformation and preparation engines. This data transformation engine comes in the form of a graphical interface for performing the whole ETL process. You can read more on Microsoft Power BI data transformation using Power Query Editor.
Load
This is the last step of the whole ETL process, after transforming the data, you are expected to load back the data into the Power BI expected target model. The information you want to analyze is represented by a set of tables, columns, and relationships called a data model. Using Power BI Desktop, you can develop the data model and publish it to Power BI Service, where it can be shared with others.
Power BI Connectors
One major advantage of Microsoft Power BI is the ability to connect to multiple data sources using different connectors.
Power BI connectors are software components that enable Power BI, to connect to a variety of data sources, such as databases, cloud services, and other data platforms. Power BI is equipt in handling different formats of data either on your local machine or cloud-based data.
Different Power BI Connectors
Microsoft Power BI has several connectors made available, with each built to connect to a certain kind of data source. Some of the most popular Power BI connectors are listed below:
There are several configuration options and requirements for each Power BI connector. To ensure that you get the most out of your data, it is crucial to pick the appropriate connector for your data source and set it up properly.
Connect Power BI to Different Data Sources
Let’s connect Power BI to multiple data sources.
Connect Power BI to SharePoint Folder
SharePoint is a web-based collaboration and document management platform that allows organizations to share and manage information and documents with coworkers, customers, and partners.
Below are the steps for connecting the Power BI desktop to SharePoint Folder:
Step 1: Get the SharePoint URL link
In your Microsoft SharePoint site, navigate to your site where the file you want to connect to is located. The file to be used is in the Document folder, select the document tab and copy the URL of the site above.
Note: You are not expected to copy all the links, just to the point of your organization name.
Step 2: Power BI Get SharePoint Folder
In your Power BI Desktop Get Data, search for the SharePoint folder and click on connect.
In the new tab opened, you must paste the SharePoint URL link copied from the SharePoint site, then click OK.
Step 3: SharePoint Security
For first-time users connecting to the SharePoint site, you are expected to provide your security credential and login detail to your SharePoint account. After all security checks are passed, click “connect”, this should take you to the Power Query Navigator where you will see all available data in the folder.
Step 4: Data Transformation
The Power Query preview window opens, this gives you a preview of the data available in the SharePoint folder. For this project, we will be working on the sales_target data.
In the Power Query editor, click on “Remove Rows” and select the top rows.
Repeat the remove row transformation process again, but this time remove the bottom row and remove the last 2 rows.
Step 5: Expand Data
Click on the “Combine files” dropdown button, this help expand the real data.
Remove the source name column from the remove column icon in the home tab.
Connect Power BI to Azure Blob Storage
Azure Blob is a cloud-based storage design to store massive amounts of unstructured data such as text or binary data, in the cloud.
Below are the steps for connecting the Power BI desktop to Azure Blob Storage:
Step 1: Get Storage URL
In your Azure blob storage, navigate to your blob container and click on the file “sales_target”. This should open another environment giving you an overview of the data.
In the data overview, you are expected to copy the property URL to your clipboard.
Step 2: Power BI Get Data From Azure Blob Storage
Still, in your Power Query editor, click on “New Source” then navigate and select “Azure Blob Storage”.
A new window will appear where you’re expected to paste the Azure Blob storage URL. The image below explains the Azure blob storage URL.
Note: You are expected only to copy the URL and ignore the file name.
Step 3: Transform Data
You are expected to repeat the same data by removing the unwanted rows and expanding the data by clicking the “combine files”.
Connect Power BI to PostgreSQL
This is an open-source relational database management system (RDBMS) for handling and maintaining massive volumes of data.
Below are the steps for connecting the Power BI desktop to PostgreSQL:
Step 1: Get the Server Name and Database Name
In your pgAdmin, login into the app and go to the database you want to use. For this project, I will be using the “Calendar_Table” database, and the “lovedamini” table.
Now, that we have gotten the database and table we want to use, let now head back to our Power BI Desktop and connect to PostgreSQL.
Step 2: PostgreSQL Credentials
Since the PostgreSQL is on our local machine, the server name will be the “localhost”, for the Database we will be using the “Calendar_Table”.
Data Connectivity Mode:
- Import Mode: Power BI imports data from the data source and stores it in memory. All data conversions, calculations, and modeling are thus carried out within Power BI, and the finished dataset is saved as a pbix file. This method is suitable for small or medium-sized datasets that can be loaded into the Power BI memory easily.
- DirectQuery Mode: Power BI connects directly to the data source and sends a query to retrieve the needed data. For this method, data is not imported directly into Power BI but rather queried in real-time from the data source. This mode is appropriate for bigger datasets that are difficult to put into memory and need immediate access to the data. It is also useful when the data frequently changes, thereby ensuring data is always up-to-date.
For this post, we will be using the Import mode since the data is “small” in size.
Next, you need to input the PostgreSQL database’s username and password. To get the username, head to your pgAdmin app, at the top right corner you will see the username “postgres”.
Put in your username and password then click on the connect button. This automatically connects your Power BI to your PostgreSQL database.
Step 3: Data Transformation
In your, Power Query Navigator select the table you want to transform and click on OK. This should take you to the Power Query Editor.
Connect Power BI to OneDrive
A cloud-based file hosting and synchronization service provided by Microsoft. Users can access their files and documents from any location with an internet connection by storing them in the cloud.
Below are the steps for connecting the Power BI desktop to OneDrive:
Step 1: Get the File Path
In your OneDrive from Business click on the 3 dots and scroll down to the “details”. In the Sale_Target file detail copy the file path and head back to your Power BI desktop.
Step 2: Get Data
In your Power BI desktop select get data from the web and paste the URL you just copied from OneDrive here.
After clicking the OK button, a security credential will pop up. Select the “Organizational account”, sign in with the right account information for the OneDrive account, and connect.
Step 3: Load Data
Now, that we are done importing all data, we can now select the Close & Apply button. This loads the entire data back to the Power BI desktop.
Change Data Sources
Having a dynamic way of changing data sources from one form to another is very important. This comes in handy in situations where a certain data source is down or not functioning at a given period of time.
Power Query Parameter
The Power Query Parameter allows for the creation of dynamic and reusable queries that can be updated by changing the value.
Parameters are a very important feature in Power Query which can be used in different scenarios. One common use case for Parameters in Power Query is the changing of data sources from multiple formats.
Changing Sources
Let’s start by changing the data source between two tables, the Excel On-premise and OneDrive sources.
Advanced Editor
A component of Microsoft Power Query called Power Query Advanced Editor enables users to change the M code that is produced when creating data transformation queries using the Power Query user interface. For more sophisticated data transformations that are difficult to complete using the user interface alone, the Advanced Editor offers a more thorough and adaptable view of the query code than the user interface.
The “Let” and “In” keywords of the M language are used in Power Query to design custom functions and build reusable queries.
“Let” is used to define a variable, which can hold a value or a function. Later on in the query, this variable can be used to condense expressions and improve readability.
The “In” keyword in the Advanced Editor indicates the end of the expression that starts with “Let”. Usually, the “In” keyword represents the output of the Input statement which is the “Let”.
Step 1: Get the Input Variable
Let’s start by selecting the Advanced Editor in the Home tab of the Power Query Editor, this should open a new window. In the new window copy the entire “Let” steps M code and close the window.
Step 2: Add Step to Excel Table
Repeat the same process for the Excel Table and copy the steps for OneDrive after the last command of the Excel table.
Note: You may experience some errors but don’t worry this is only due to variables, change the variable name and all errors will disappear, then click on Done.
Step 3: Create a List Parameter
Let’s create a list parameter that will enable us to easily change data sources between Excel and OneDrive.
In your Home tab click on “Manage Parameter” and then select “New Parameter”. In the New Parameter, you are expected to fill in the following information.
- Name: This is the Manage Parameter name given.
- Description: This is optional.
- Suggested Value: We will be using the List of values.
Step 4: Change Data Using New Parameter
Lastly, we need to create a condition that will enable the Power Query Editor to switch between data sources. We will be using a conditional IF Statement inside of the Advanced Editor.
The video below explains how to switch between multiple data sources using the Advanced Editor with Parameters.
Conclusion
In this article, you have learned how to connect multiple data sources to Microsoft Power BI both On-premises and cloud-based data. We also covered how to dynamically change multiple data sources using the Manage Parameter and Advanced Editor.
To learn more about Power BI, you should sign up for our classes, keep up with the blog, and subscribe to our YouTube channel.