Getting Data into Power BI
The course is part of this learning path
Getting Data into Power BI from Different Sources looks at connecting to data sources on-premise, in the Azure cloud, and Power platform data sources. The course is focused on how various data sources can impact your data model's performance, rather than looking at every single source that Power BI accommodates, as there are literally dozens and dozens of them. Apart from typical database and file data sources we look at Dataverse, a power platform repository and data flow an online ETL service.
- How to connect to a database and file data source on-premises and in the Azure cloud
- How to change the location of a data source without reimporting the data
- Look at the pros and cons of each Power BI data storage mode
- User Power Platform's Dataverse product as a data source
- How to and why you would use a Data Flow as a data source
- Setting up and connecting to an XMLA endpoint
This course is intended for anyone who wants to get data into Power BI.
- An understanding of Power BI desktop and power BI online
- A Power BI premium account for XMLA endpoint, one of the more specialized data connection topics
Database and file resources
When developing a data model, you often do it with non-production data or a cut-down or smaller version of the final data source. Instead of creating a new data source and going through the re-import process, you can just change the location of a data source if its structure is the same as the current model.
In Power BI desktop, select data source settings under the transform data menu. The data source setting dialog shows you all the data sources of your current model. You can change a data source with the change source button. I'll change the location of the branch targets Excel workbook from a local file to Azure blob storage and the database from Azure SQL to a local SQL Server. As an aside, you can specify the default SQL Server instance on your own computer with either localhost, 127.0.0.1, the name of your PC, or, as I've done here, a simple".". That's how you change data sources. It's easy to do but a manual process that requires users to have access to the data source functionality. You can use parameters in your connection strings to make changing data sources much easier. The main consideration when implementing a dynamic data source is that the format or schema of the data is identical in each source.
I'm going to change the source of my branch targets data from a spreadsheet to an SQL Server table in another database. Let's head over to Power Query Editor via the Transform data menu. The first thing I'm going to do is create a new query that retrieves the target data from the new source. The database is called reporting, and I'll use this simple SQL select statement to retrieve the data I'm interested in. Once the connection has been established, I'll open the advanced editor and copy the connection code between the let and the in keywords. Next, I'll select the branch targets data and open the advanced editor. At the end of each line, you can see a comma, so I'll place a comma after the last line before the in keyword and then paste the SQL database connection string. Then I'll create a variable that will be assigned the query's result. I can't use source as that's already being used for the Excel data.
As of right now, we won't see any difference in the data as the changed type data before the SQL source variable is the one still being used by the in section. Next, I'll define a target source text parameter and give it the current value of excel in lowercase. Keep in mind parameters are case-sensitive in Power Query editor. Now that I've defined the TargetSource parameter, the value is currently set to excel. Let's go back to the advanced editor for BranchTargets' source query.
I'll place an if statement after the SQL data source that says if the target source is equal to excel, then use the "changed type" data, otherwise use the SQL source data. All I have to do now is return targets instead of changed type. I don't need Query1 anymore, so I'll delete it. And don't forget to apply the changes. Back in Power BI desktop, when I go to transform data, the parameters option is now available. I'll change the TargetSource from excel to SQL and apply the changes.
I can also use parameters to change the database server. I'll create another text parameter called DatabaseSource and give the value localhost. Going back to the advanced editor for branch targets, I can replace the "." In the database connection string with the DatabaseSource parameter. No syntax errors have been detected, which is good, but I do have to give permission for the query to run on the local database. Connection strings can be long and prone to typos, so instead of a text parameter, I'll change the DatabaseSource into a list, specifying the possible database servers.
Because I haven't run any queries against this database before, I need to approve the SQL statement, and then close and apply. Back in Power BI, I'll change the DatabaseSource from PowerSales on Azure to localhost. There is no change to the report as both databases are identical. I'll quickly go into SQL Server management studio and delete some records from the branch targets table on Azure. Back in Power BI desktop, I'll change the DatabaseSource from localhost to powersales.database.windows.net, revealing the new value.
There is another way to achieve the same result without involving the advanced editor. The first thing you need to do is enable "Always allow parameterization in data source and transformation dialogs." You can do this through the view ribbon in Power Query Editor or the options and settings window within Power BI. Before I do this, I'll set all the data sources to a bogus server. When I open up data source settings, we can see the incorrect connection string at the bottom of the list. Click change source and under server change the value type from text to parameter.
Next, select the appropriate parameter from the drop-down list. When I click OK, you can see the warning icons have changed back to table icons, and when I click apply, data replaces the error messages. Looking at the source step for any table, we see that the hardcoded "." has been replaced by the DatabaseSource parameter. Changing the DatabaseSource parameter value and clicking apply correctly refreshes the data from the new source.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.