Excel 365 Power Query - Get and Transform Data like a Pro

Learn how to get and transform data like a pro using Excels Power Query

4.40 (67 reviews)
Udemy
platform
English
language
Microsoft
category
2,334
students
4 hours
content
May 2020
last update
$44.99
regular price

What you will learn

Learn how to transform and clean data using Excels Power Query

Learn how to navigate Power Query and Connect to data sources

Learn how to work with column, row and table transformations

Get an introduction to M

Learn how to use parameters and custom functions

Description

Are you ready to take your data analysis skills in excel to the next level? Do you want to learn how to connect to and transform data like a pro?

This course has been designed to take you from a beginner Power Query user to an advanced user in no time.  Power Up on your Power Query Skills today.

In this course, we will first give you Your first look at Power Query and connecting to data sources. From there we will move into combining data and simple transformations, such a merge and append. We will look in detail at row and column transformations and then we will deep dive into table transformations.

After this, we will move on to a more advanced power query. We will learn how to use Power Query to add new columns to our data and we will then Connect and Transform More Complex Data Structures. Finally, we will then introduce you to the powerful M code that runs power query and you will learn how to use parameters and create custom functions

So if you are interested in learning more, why don’t you hop into the preview section and take a look.

This course is a hands-on practical course with workbooks to download and activities to complete

Content

Getting started with Power Query - Connecting to data and simple transformations

Getting started introduction
Module 1 Intro
Before you forget
What is Power Query
What is Power Query?
How to connect to Excel
Connect to an Excel workbook
How do I find my way around Power Query
Power Query Orientation
How do I connect to CSV or Txt file?
Connect to a CSV or Text file
How do I connect to web data
Connect to webdata
Activity
Activity Feedback
Quiz
Moving on
Combining Files and Simple data Transformations.
What are query setting properties and applied steps
Applied Steps
Why and how do I append queries?
How to append queries
Why and how to I merge queries?
How to merge queries
What do the different merge join types do?
The impact of merge types
How do I combine a folder containing Excel Files
Activity
Activity solution
Quiz

Working with Columns and Rows in Power Query tables

Module 2 Introduction
Working with Columns and Rows in Power Query tables
How do I manage and work with columns in a power query table
Managing Columns
How do I replace values and fill data
How to fill up and fill down
How do I use filter or reduce rows
How to reduce rows
What are the data types and how do I change them
Data types
Activity
Activity feedback
Quiz
Moving on
Working with table transformations
How can I work with Text columns?
Text Columns
Grouping Data
Grouping
How and when would you use Transpose
How can I pivot and unpivot data?
Pivot, unpivot and Transpose
Putting it together
Putting it all together
Activity
Activity Solution
Quiz

Advanced Data connections and more transformations

Module 3 Introduction
adding columns
How do I add columns by example
How do I add custom columns
Adding columns by example
How do I add conditional columns
Adding Conditional Columns
How do I add index columns
Can I add other column types
Adding other columns
Activity
Activity solution
Activity 2 solution
Quiz
Moving On
more complex connections
How to I work with Records and Lists contained within columns of a dataset?
List Records
What is JSON Data?
How to connect to JSON Data from Excel
Connecting to JSON
How do I parse JSON Data in Power Query
Parse JSON
How can I get a list of file names and file details from a folder?
File names and details
How can I duplicate or Reference a query and how can I change the source data?
Removing duplicates
Activity 1
activity solution
Activity 2
Activity solution

M is for Mashup, the code of Power Query

Module 4 introduction
An introduction to M
What is M and the Advanced editor
An introduction to M
How do I Read and Write M?
How do I create Lists and Records in M
How do I read and write M and how do I create lists and records
How can I use M on custom columns
activity
Activity solution
activity
activity solution
Quiz
Moving on
parameters and custom functions
What are parameters in Power query
How can I use parameters to filter a query
Filter parameters
What is a custom function in Power Query
Custom Function
How can I use a parameter table in Excel to drive queries
Parameters in Excel
Activity
Activity solution
Conculsion

Screenshots

Excel 365 Power Query - Get and Transform Data like a Pro - Screenshot_01Excel 365 Power Query - Get and Transform Data like a Pro - Screenshot_02Excel 365 Power Query - Get and Transform Data like a Pro - Screenshot_03Excel 365 Power Query - Get and Transform Data like a Pro - Screenshot_04

Reviews

Edward
October 13, 2023
The ideas are good but the links don't work or there are missing files on some of the modules. No attempt has been made to update for 2 years or maybe longer.
Omar
October 1, 2022
The instructor is very organized, the delivery is very well planned and the activities are becoming more interesting and challenging.
Donald
May 28, 2020
Could not access the bitcoin database. Received an error saying it was gone. The JSON section was very difficult to master due to not being able to pull in external data from the weblinks. Otherwise this was a fantastic course and I learned a lot of very valuable information.
Copaci
May 9, 2020
Hello Paula, Finally I did a good course. I can't wait to do your exl course. I rarely give 5 stars but you deserve them. The only negative thing for this course is too short. Many thanks for this course!
Bill
May 8, 2020
Excellent introduction to Power Query. Still feel there is much more to learn, so I wish it were broken down into two or even three separate courses, but I feel I have a decent basic understanding on which to build.
Keith
May 4, 2020
This was a perfect level for me - having done some Power BI courses this reinforced a lot of the learning but also highlighted some functionality I hadn't come across such as Column by Example and Custom Functions so very useful
Efe
March 1, 2020
I want to get good at using power query and i think this course is helping me to do so so far at this early point of the course.
Антон
December 31, 2019
I learned a lot of tricks and tips. I wanted more information on M, but in any case this is a great course!
Arnab
November 19, 2019
Some features are not available on Excel 2016 but in video, the options are coming and for a newcomer it is a good course to take up
John
October 22, 2019
It is definitely not beginner for someone unfamiliar with the basic concepts who is using Excel 2016. Although I was able to follow along in 2016, I often have to use different tabs. This course should be advertised for Office 365 users, not for 2016 and below.

Charts

Price

Excel 365 Power Query - Get and Transform Data like a Pro - Price chart

Rating

Excel 365 Power Query - Get and Transform Data like a Pro - Ratings chart

Enrollment distribution

Excel 365 Power Query - Get and Transform Data like a Pro - Distribution chart
2505600
udemy ID
8/12/2019
course created date
10/8/2019
course indexed date
Bot
course submited by