Sign In Start Free Trial
Account

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying Power Query Cookbook
  • Table Of Contents Toc
  • Feedback & Rating feedback
Power Query Cookbook

Power Query Cookbook

By : Janicijevic
4.5 (14)
close
close
Power Query Cookbook

Power Query Cookbook

4.5 (14)
By: Janicijevic

Overview of this book

Power Query is a data preparation tool that enables data engineers and business users to connect, reshape, enrich, and transform their data to facilitate relevant business insights and analysis. With Power Query's wide range of features, you can perform no-code transformations and complex M code functions at the same time to get the most out of your data. This Power Query book will help you to connect to data sources, achieve intuitive transformations, and get to grips with preparation practices. Starting with a general overview of Power Query and what it can do, the book advances to cover more complex topics such as M code and performance optimization. You'll learn how to extend these capabilities by gradually stepping away from the Power Query GUI and into the M programming language. Additionally, the book also shows you how to use Power Query Online within Power BI Dataflows. By the end of the book, you'll be able to leverage your source data, understand your data better, and enrich it with a full stack of no-code and custom features that you'll learn to design by yourself for your business requirements.
Table of Contents (12 chapters)
close
close

Creating a query from a database

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:

  • Select tables or views as you would see them with a database viewer such as SQL Server Management Studio.
  • Retrieve tables by writing SQL statements in a specific section that will pop up.

Getting ready

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).

How to do it...

Once you open the Power BI Desktop application, you are ready to perform the following steps:

  1. Go to Get data, click on More, and browse for Azure SQL database:
    Figure 2.32 - Azure Connectors section

    Figure 2.32 - Azure Connectors section

  2. Enter the following information (expanding Advanced options):

    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]
  3. Copy and paste the code in the SQL statement section in order to get this view as the output table you will work on in Power Query:
    Figure 2.33 – SQL Server database

    Figure 2.33 – SQL Server database

  4. Enter authentication details:
    Figure 2.34 – SQL Server database authentication

    Figure 2.34 – SQL Server database authentication

  5. After signing in, the output of the SQL statement will pop up as follows:
    Figure 2.35 – Table preview

    Figure 2.35 – Table preview

  6. Click on Transform Data in order to open the Power Query interface:
    Figure 2.36 – Data preview in Power Query

    Figure 2.36 – Data preview in Power Query

  7. Click on Get data and select the connector Azure SQL Database. In this case, we won't enter a SQL statement, but we will select an existing table in the database:
    Figure 2.37 – SQL Server database connector

    Figure 2.37 – SQL Server database connector

  8. After signing in, a preview interface will appear, and you will be able to select the tables that you want to open in Power Query after clicking on OK:
    Figure 2.38 – SQL Database Navigator

    Figure 2.38 – SQL Database Navigator

  9. You will see on the left a set of queries as an output of connecting directly to the database tables and writing a SQL statement querying the database as you would do with any other database viewing tool:
Figure 2.39 – Data preview in Power Query

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

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

Figure 2.41 – Advanced Editor code for the DimDate Query

How it works...

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)).

Create a Note

Modal Close icon
You need to login to use this feature.
notes
bookmark search playlist download font-size

Change the font size

margin-width

Change margin width

day-mode

Change background colour

Close icon Search
Country selected

Close icon Your notes and bookmarks

Delete Bookmark

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete

Delete Note

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete

Edit Note

Modal Close icon
Write a note (max 255 characters)
Cancel
Update Note

Confirmation

Modal Close icon
claim successful

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY