-
Book Overview & Buying
-
Table Of Contents
-
Feedback & Rating

Power Query Cookbook
By :

This recipe shows how to connect to a database and how tables and views are displayed while selecting which tables to display and work with in Power Query.
You have two generic options:
In this recipe, in order to connect to a SQL database, you need to have an Azure SQL Database instance with AdventureWorks data, database credentials, or access through Azure Active Directory authentication (log in with your Microsoft account).
Once you open the Power BI Desktop application, you are ready to perform the following steps:
Figure 2.32 - Azure Connectors section
a) Server: Server name
b) Database: Adventureworks
c) Data Connectivity mode: Import
d) SQL statement: This is a SQL view executed using the data source based on two tables in the database. The view is built as a SQL join between FactResellerSales
and DimSalesTerritory
:
SELECT s.[ProductKey] ,s.[SalesTerritoryKey] ,s.[SalesOrderNumber] ,s.[SalesOrderLineNumber] ,s.[RevisionNumber] ,s.[OrderQuantity] ,s.[UnitPrice] ,s.[ExtendedAmount] ,s.[UnitPriceDiscountPct] ,s.[DiscountAmount] ,s.[ProductStandardCost] ,s.[TotalProductCost] ,s.[SalesAmount] ,s.[OrderDate] ,p.[SalesTerritoryRegion] ,p.[SalesTerritoryCountry] ,p.[SalesTerritoryGroup] FROM [dbo].[FactResellerSalesXL_CCI] s LEFT OUTER JOIN [dbo].[DimSalesTerritory] p ON s.[SalesTerritoryKey] = [p.SalesTerritoryKey]
Figure 2.33 – SQL Server database
Figure 2.34 – SQL Server database authentication
Figure 2.35 – Table preview
Figure 2.36 – Data preview in Power Query
Figure 2.37 – SQL Server database connector
Figure 2.38 – SQL Database Navigator
Figure 2.39 – Data preview in Power Query
If you open Advanced Editor for both types, you will notice that if you need to change the SQL code or you have to change the table name, you can do that directly from the Advanced Editor window:
a) The SalesData table's Advanced Editor code where you can see the details of the query run against the data source:
Figure 2.40 – Advanced Editor code for a SalesData query
b) The DimDate table's Advanced Editor code where you can see the details of the connection Source
, the database retrieved from the server, Adventureworks
, and the table selected from the data source DimDate
:
Figure 2.41 – Advanced Editor code for the DimDate Query
The Azure SQL Database connector also reflects how other database connectors work. If you connect to Amazon Redshift or an Oracle database, the experience will be very similar. Power Query provides a wide range of options for relational data sources and some of them may need the installation of specific drivers. For example, if you connect to SAP or Oracle, you have to install additional components (for example, in Oracle, the additional components will be the Oracle Data Access Components (ODAC)).