- •Contributors
- •Table of Contents
- •Preface
- •Technical requirements
- •Installing a Power BI gateway
- •Getting ready
- •How it works
- •Authentication to data sources
- •Getting ready
- •How it works
- •Main challenges that Power Query solves
- •Getting ready
- •Technical requirements
- •Getting data and connector navigation
- •Getting ready
- •Creating a query from files
- •Getting ready
- •How it works...
- •Creating a query from a folder
- •Getting ready
- •How it works...
- •Creating a query from a database
- •Getting ready
- •How it works...
- •Creating a query from a website
- •Getting ready
- •How it works...
- •Technical requirements
- •Exploring Power Query Editor
- •Getting ready
- •Managing columns
- •Getting ready
- •Using data profiling tools
- •Getting ready
- •Using Queries pane shortcuts
- •Getting ready
- •Using Query Settings pane shortcuts
- •Getting ready
- •Using Schema view and Diagram view
- •Getting ready
- •Technical requirements
- •Formatting data types
- •Getting ready
- •Using first rows as headers
- •Getting ready
- •Grouping data
- •Getting ready
- •Unpivoting and pivoting columns
- •Getting ready
- •Filling empty rows
- •Getting ready
- •Splitting columns
- •Getting ready
- •Extracting data
- •Getting ready
- •Parsing JSON or XML
- •Getting ready
- •Exploring artificial intelligence insights
- •Getting ready
- •Technical requirements
- •Merging queries
- •Getting ready
- •Joining methods
- •Getting ready
- •Appending queries
- •Getting ready
- •Combining multiple files
- •Getting ready
- •Using the Query Dependencies view
- •Getting ready
- •Technical requirements
- •Setting up parameters
- •Getting ready
- •Filtering with parameters
- •Getting ready
- •Folding queries
- •Getting ready
- •Leveraging incremental refresh and folding
- •Getting ready
- •Disabling query load
- •Getting ready
- •Technical requirements
- •Using M syntax and the Advanced Editor
- •Getting ready
- •Using M and DAX – differences
- •Getting ready
- •Using M on existing queries
- •Getting ready
- •Writing queries with M
- •Getting ready
- •Creating tables in M
- •Getting ready
- •Leveraging M – tips and tricks
- •Getting ready
- •Technical requirements
- •Adding columns from examples
- •Getting ready
- •Adding conditional columns
- •Getting ready
- •Adding custom columns
- •Getting ready
- •Invoking custom functions
- •Getting ready
- •Clustering values
- •Getting ready
- •Technical requirements
- •Using Power BI dataflows
- •Getting ready
- •Centralizing ETL with dataflows
- •Getting ready
- •Building dataflows with Power BI Premium capabilities
- •Getting ready
- •Understanding dataflow best practices
- •Getting ready
- •Technical requirements
- •Exploring diagnostics options
- •Getting ready
- •Managing a diagnostics session
- •Getting ready
- •Designing a report with diagnostics results
- •Getting ready
- •There's more…
- •Using Diagnose as a Power Query step
- •Getting ready
- •Other Books You May Enjoy
- •Index
150 Combining Queries for Efficiency
Technical requirements
For this chapter, you will be using Power BI Desktop (https://www.microsoft. com/en-us/download/details.aspx?id=58494).
The minimum requirements for installation are listed here:
•.NET Framework 4.6 (Gateway release August 2019 and earlier)
•.NET Framework 4.7.2 (Gateway release September 2019 and later)
•A 64-bit version of Windows 8 or a 64-bit version of Windows Server 2012 R2 with current Transport Layer Security (TLS) 1.2 and cipher suites
•4 gigabytes (GB) disk space for performance monitoring logs
You can find the data resources referred to in this chapter at https://github.com/ PacktPublishing/Power-Query-Cookbook/tree/main/Chapter05.
Merging queries
Users usually need to merge data horizontally and enrich a table with additional columns that are not available within the main query when it is loaded from a data source.
In this recipe, you will see how to perform this merging and which steps to consider in order to get a successful result.
Getting ready
For this recipe, you need to download the following files:
•FactInternetSales CSV file
•DimTerritory CSV file
In this example, we will refer to the C:\Data folder.
Merging queries 151
How to do it…
Once you open your Power BI Desktop application, you are ready to perform the following steps:
1.Click on Get Data and select the Text/CSV connector.
2.Browse to your local folder where you downloaded the FactInternetSales CSV file and open it. The following window with a preview of the data will pop up; click on Transform Data:
Figure 5.1 – CSV data preview
152Combining Queries for Efficiency
3.Repeat these steps for the DimTerritory CSV file in order to end up with two queries in the Power Query user interface (UI)—FactInternetSales and
DimTerritory:
Figure 5.2 – Power Query UI
4.In this case, we want to enrich the FactInternetSales table with some columns coming from the DimTerritory table in order to get details of the geographical location of sales transactions. For this, you need to browse to the end of the Home tab and click on Combine and then Merge Queries:
Figure 5.3 – Merge Queries button
Merging queries 153
5.A Merge window will pop up. Select the SalesTerritoryKey column and select the DimTerritory table from the drop-down menu, as shown in the following screenshot:
Figure 5.4 – Merge window
154Combining Queries for Efficiency
6.In the second table, select the SalesTerritoryKey column. Select Left Outer (all from first, matching from second) for the Join Kind field. Leave the other options as they are and click on OK:
Figure 5.5 – Merge window columns selected
7.After you click OK, you will see that a new column will be added to the
FactInternetSales query:
Figure 5.6 – New column added
Merging queries 155
8.Click on the Expand icon on the right of the DimTerritory column, select
SalesTerritoryRegion and SalesTerritoryCountry, and click on OK:
Figure 5.7 – New columns selection
9.You will see two new columns coming from the other query (DimTerritory), matching the rows of the main table:
Figure 5.8 – Expanded columns