Power BI Power Query Essentials

£75.00£2,250.00

SKU: GCOL-PBI-PQ1 Category:

Description

Power Query is a robust data connection and transformation tool which is built into Power BI and which allows you to connect to data from a wide variety of sources and then transform that data into exactly the right shape and format for your reporting needs.

In this course, you’ll learn how to connect to data stored in CSV files, XML files and Excel worksheets and tables, as well as how to assemble multiple files stored within a folder into a single table.

You’ll also learn how to connect to web data and link data stored on the web to your own in-house data, even when there is no initial commonality between the two datasets.

You’ll learn how to connect to data in SQL Server databases, both in import and DirectQuery mode, and to perform transformations which leverage query folding, the process whereby Power Query commands are converted from the M language into SQL and pushed back to the server.

By the end of this course, you’ll also be comfortable using the Power Query interface to perform a wide variety of useful transformations on your data to make it fit for your purposes.

This course will show you how to split columns that contain separate pieces of information and different ways of combining several columns into one.

You’ll also learn how to deal with data produced by another system as a report and which contains reporting features such as subheadings embedded within the data and pivoted columns.

We’ll also discuss Append Queries and Merge Queries, Power Query’s versatile commands for stitching multiple tables together, vertically and horizontally.

And, since this is a course aimed at users who are new to Power BI, it also includes numerous Quick Practice sessions in which we will build mini reports using the data we have transformed.

So, by the end of the course, you will be totally at home in using Power Query to transform and cleanse your data, making it ready for use within a Power BI data model.

Course Curriculum

1. Welcome

Welcome to Power Query Essentials

2. Setting up

1. Installing SQL Server

2. Installing the Sample Database

3. Download the Course Files Here

3. Connecting to Text Files

1. Connecting to delimited text files

2. Using UTF-8 Encoding

3. Quick Practice

4. Connecting to Excel Data

1. Excel data objects

2. Hidden Sheets

3. Blank columns

4. Password Protected Workbooks

5. Total row

6. Quick Practice

5. Connecting to Folders

1. Connecting to a Folder of Data

2. Using a Sample File

3. Quick Practice

6. Connecting to Web Data

1. Connecting to a web URL

2. Connecting to web page data

3. Quick Practice

7. Connecting to SQL Server

1. Overview of Connecting to SQL Server

2. Importing SQL Server Views

3. Using SQL Statements

4. DirectQuery Mode

8. Splitting and Combining

1. Removing spaces by splitting

2. Splitting by character transition

3. Combining columns

9. Extracting text

1. Extracting character ranges

2. Text Between Delimiters

3. Text Before Delimiter

10. Transforming report data

1. Removing Unwanted Rows

2. Fill Down

3. Unpivot Columns

4. Quick Practice

11. Combining tables

1. Append Queries

2. Merge Queries

3. Merge Queries Left Anti

Additional information

Users

Single User, 2 Users, 5 Users, 10 Users, 25 Users, 50 Users, 100 Users