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 folder

After playing with single files as the previous recipe showed, you need to load more files as their analytical workloads grow. If files are organized in folders, users can leverage a folder connector to load multiple files. Imagine having a collection of CSV files where each contains sales data for a specific day. What if we want to connect to a folder that contains these files, and we want to load them in Power Query as a single table? The way to go is to leverage the folder connector.

In this recipe, we will see how to connect to a folder with sales data in CSV format and a folder with finance data in Excel format (each file contains multiple sheets).

Getting ready

In this recipe, in order to test different types of file connectors, you need to download the following folders, each containing a set of files:

  • The CSVFiles folder containing the following CSV files:
Figure 2.21 – Local folder with CSV files

Figure 2.21 – Local folder with CSV files

  • The ExcelFiles folder containing the following Excel files:
Figure 2.22 – Local folder with Excel files

Figure 2.22 – Local folder with Excel files

In this example, I will refer to the following paths:

a) C:\Data\ExcelFiles

b) C:\Data\CSVFiles

You can find the folders and the related files referred to in this chapter at https://github.com/PacktPublishing/Power-Query-Cookbook/tree/main/Chapter02/ExcelFiles and https://github.com/PacktPublishing/Power-Query-Cookbook/tree/main/Chapter02/CSVFiles.

How to do it...

Open the Power BI Desktop application and perform the following steps:

  1. Go to Get data, click on Folder, and the following window will pop up. You can directly enter your folder path or click on Browse… and select it from the usual browsing section of your machine:
    Figure 2.23 – Folder connector

    Figure 2.23 – Folder connector

  2. Once you click on OK, you will see the following section with a list of files contained in the folder:
    Figure 2.24 – How files from the folder are displayed

    Figure 2.24 – How files from the folder are displayed

    At the bottom right, you can see some actions to perform:

    a) Combine & Transform Data: You can combine data by appending existing data at this phase and open Power Query.

    b) Combine & Load: You can append tables, load them, and start creating reports or analyzing data with Excel.

    c) Load: Load this list into the Power BI dataset as it is.

    d) Transform Data: This opens the Power Query interface and allows you to do custom transformations.

  3. Click on Transform Data and you will see the following columns:
Figure 2.25 – List of files in the Power Query view

Figure 2.25 – List of files in the Power Query view

From here, you can do one of these actions:

a) Expand a single CSV by clicking on Binary in the Content column:

Figure 2.26 – Expanded table

Figure 2.26 – Expanded table

b) Expand the Attributes column with some relevant information:

Figure 2.27 – Expand the Attributes column

Figure 2.27 – Expand the Attributes column

c) Combine files by clicking on the icon on the right, which means Combine:

Figure 2.28 – The Combine icon on the Content column

Figure 2.28 – The Combine icon on the Content column

Data combination is a concept that will be widely explored in Chapter 5, Combining Queries for Efficiency.

Now we will repeat the same steps but with the other folder containing Excel files:

  1. The view that opens is the following:
Figure 2.29 – List of Excel files in the Power Query interface

Figure 2.29 – List of Excel files in the Power Query interface

It is very similar to the one we saw previously because you can perform the following actions:

a) If you click on Binary in row 1, you will end up with a table with a list of the sheets contained in the Excel file FinanceData-OnlineChannel. If you click on Table in row 1, you will expand the sheet Sales:

Figure 2.30 – First level of the expanded table

Figure 2.30 – First level of the expanded table

b) If you click on Combine, the following window will pop up:

Figure 2.31 – Table preview during the Combine step

Figure 2.31 – Table preview during the Combine step

This built-in combine function will allow you to append the Sales sheets from three different Excel files. This topic will be widely explored in Chapter 5, Combining Queries for Efficiency.

How it works...

The idea of this recipe was to show you the potential of the folder connector because often users end up connecting multiple times to single files and then perform an append step. This takes time, and it is difficult to maintain when the number of files becomes bigger.

The folder connector allows you to refresh your data and perform all Power Query operations automatically. If you add a file in your folder and click on refresh, you will end up with a final table enriched with data coming from this last file.

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