- •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
Creating a query from a folder 49
In the APPLIED STEPS section, you will see some activities mapped as a result of Extract Table Using Examples performed previously.
How it works...
Power Query, thanks to these file connectors, allows users to connect to single files and perform some pre-transformation tasks allowing them to load just relevant data in the usual interface. However, these connectors – Excel, TXT/CSV, and also Parquet file are related to single files. We will see in the following recipe how to connect to multiple files.
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
50Connecting to Fetch Data
•The ExcelFiles folder containing the following 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
Creating a query from a folder 51
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
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.
52Connecting to Fetch Data
3.Click on Transform Data and you will see the following columns:
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
Creating a query from a folder 53
b) Expand the Attributes column with some relevant information:
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
Data combination is a concept that will be widely explored in Chapter 5, Combining Queries for Efficiency.
54 Connecting to Fetch Data
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
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