Udemy
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
Development
Web Development Data Science Mobile Development Programming Languages Game Development Database Design & Development Software Testing Software Engineering Development Tools No-Code Development
Business
Entrepreneurship Communications Management Sales Business Strategy Operations Project Management Business Law Business Analytics & Intelligence Human Resources Industry E-Commerce Media Real Estate Other Business
Finance & Accounting
Accounting & Bookkeeping Compliance Cryptocurrency & Blockchain Economics Finance Finance Cert & Exam Prep Financial Modeling & Analysis Investing & Trading Money Management Tools Taxes Other Finance & Accounting
IT & Software
IT Certification Network & Security Hardware Operating Systems Other IT & Software
Office Productivity
Microsoft Apple Google SAP Oracle Other Office Productivity
Personal Development
Personal Transformation Personal Productivity Leadership Career Development Parenting & Relationships Happiness Esoteric Practices Religion & Spirituality Personal Brand Building Creativity Influence Self Esteem & Confidence Stress Management Memory & Study Skills Motivation Other Personal Development
Design
Web Design Graphic Design & Illustration Design Tools User Experience Design Game Design Design Thinking 3D & Animation Fashion Design Architectural Design Interior Design Other Design
Marketing
Digital Marketing Search Engine Optimization Social Media Marketing Branding Marketing Fundamentals Marketing Analytics & Automation Public Relations Advertising Video & Mobile Marketing Content Marketing Growth Hacking Affiliate Marketing Product Marketing Other Marketing
Lifestyle
Arts & Crafts Beauty & Makeup Esoteric Practices Food & Beverage Gaming Home Improvement Pet Care & Training Travel Other Lifestyle
Photography & Video
Digital Photography Photography Portrait Photography Photography Tools Commercial Photography Video Design Other Photography & Video
Health & Fitness
Fitness General Health Sports Nutrition Yoga Mental Health Dieting Self Defense Safety & First Aid Dance Meditation Other Health & Fitness
Music
Instruments Music Production Music Fundamentals Vocal Music Techniques Music Software Other Music
Teaching & Academics
Engineering Humanities Math Science Online Education Social Science Language Teacher Training Test Prep Other Teaching & Academics
AWS Certification Microsoft Certification AWS Certified Solutions Architect - Associate AWS Certified Cloud Practitioner CompTIA A+ Cisco CCNA Amazon AWS CompTIA Security+ AWS Certified Developer - Associate
Graphic Design Photoshop Adobe Illustrator Drawing Digital Painting InDesign Character Design Canva Figure Drawing
Life Coach Training Neuro-Linguistic Programming Mindfulness Personal Development Meditation Personal Transformation Life Purpose Coaching Neuroscience
Web Development JavaScript React CSS Angular PHP WordPress Node.Js Python
Google Flutter Android Development iOS Development Swift React Native Dart Programming Language Mobile Development Kotlin SwiftUI
Digital Marketing Google Ads (Adwords) Social Media Marketing Google Ads (AdWords) Certification Marketing Strategy Internet Marketing YouTube Marketing Email Marketing Retargeting
SQL Microsoft Power BI Tableau Business Analysis Business Intelligence MySQL Data Analysis Data Modeling Big Data
Business Fundamentals Entrepreneurship Fundamentals Business Strategy Online Business Business Plan Startup Freelancing Blogging Home Business
Unity Game Development Fundamentals Unreal Engine C# 3D Game Development C++ 2D Game Development Unreal Engine Blueprints Blender
30-Day Money-Back Guarantee

This course includes:

  • 3 hours on-demand video
  • 18 articles
  • 37 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
Office Productivity Microsoft Power Query

Introducing: Get and Transform / PowerQuery

Do everything in Excel faster, easier, and more efficiently with PowerQuery in your toolbox.
Rating: 3.6 out of 53.6 (25 ratings)
129 students
Created by Explainly Io, Philip Trick
Last updated 6/2020
English
English [Auto]
30-Day Money-Back Guarantee

What you'll learn

  • Connect multiple datasets together in meaningful ways.
  • Quickly create informative and responsive reports.
  • Build powerful data manipulation queries without requiring any database code.
  • Perform common Excel tasks faster and more accurately.

Requirements

  • Excel 2016 / Excel 2019 / Excel 365
  • Excel 2010-2013 with PowerQuery Add-In Installed (described in course).
  • New to Excel / Excel Beginner
  • No code or formula background required.

Description

Incorporate PowerQuery into your toolset to revolutionize how you use Excel.

Microsoft's PowerQuery (Get and Transform in Excel 2016) offers a wide variety of tools ideal for accessing local and external data sets,  cleaning and re-framing information into usable structures, working across multiple data tables to perform lookups and combinations, and building informational and powerful reports.

This course is for anyone new to PowerQuery and applicable to any level of Excel user. You will become a capable query creator and editor with no coding or Excel formula knowledge required.

Access the world beyond Excel's boundaries

Ever copy and paste data from one workbook into another?

What about from a webpage?

Have you ever asked your database administrator for a data report to work with, and gone through a dozen iterations (and multiple weeks) before having something you could adequately use in Excel?

Ever abandoned a project because automating data from an external source seemed too daunting or might have required VBA?

PowerQuery can solve all of these issues. You will build data requests against these alternative sources quickly and easily, while incorporating data standardization and cleaning components into that same request.

Replace many tedious and error-prone Excel formula-based approaches.

This course will teach you how to perform effective data analysis that takes a fraction of the time to put together. Replace your vast spreadsheets packed with VLOOKUPs, SUMIFS, SUM, COUNTIFS, and more with PowerQueries that can be created with your mouse via clean and incredibly easy to use interfaces.

These Excel functions provide some function of merging, linking, and aggregating. The PowerQuery tool includes mechanisms for performing these operations that make these functions seem quaint and outdated. Why write three columns of SUMIFS if you can get the same results with a single GROUP BY command without needing to write any formulas at all?

A brief glimpse of PowerQuery's usefulness and capabilities...

This course is designed for all Excel users since PowerQuery is designed for all users -- not just power users capable of building incredible Excel worksheets with functions, VBA, and mystical trickery. As such, the course focuses almost exclusively on using the PowerQuery ribbon to perform every action. We do not dive into the Advanced Editor to interact with the M Language at all.

In the last few lessons of the course, you will be introduced to the M Language through the custom column creation tool and introduced to the functions section of Microsoft's Power Query M Language Reference to assist you in building those custom calculations. These calculations are a great place to learn about how to interpret and use the functions in preparation for more advanced applications.

Expand YOUR horizons

This course will prepare you for more advanced PowerQuery courses and can serve as a good starting point for launching into PowerPivot and certain Power BI content. By the end of this course, you should feel comfortable in the PowerQuery Editor. This means that you'll be meaningfully capable of building data acquisition within Power BI by the end of this course, as the Excel PowerQuery Editor is simply the Power BI Query Editor minus a few advanced features.

Who this course is for:

  • Anyone who uses Excel to interact with or manipulate data.

Course content

7 sections • 41 lectures • 3h 9m total length

  • Preview00:34
  • Preview00:15
  • Preview00:27
  • Preview05:29

  • Preview06:27
  • Common External Data Sources
    08:17
  • Connecting to Data Exercise
    00:07
  • Connecting to Data Exercise: Walkthrough
    04:18
  • Connecting to Data Quiz
    5 questions

  • Section Notes
    00:21
  • Standard Organizational Commands
    08:55
  • Connecting Data Together - The Append Command
    06:30
  • Preview09:27
  • Aggregation Commands
    10:55
  • Expand and Contract Data - Column Pivots
    10:22
  • Linking and Organizing Data - Comprehensive Exercise
    00:28
  • Linking and Organizing Data - The Exercise Solution
    13:23

  • Introduction to Data Types
    11:25
  • Introduction to Data Types - Practice Set
    00:14
  • Text Data Types
    09:14
  • Text Data Types - Practice Set
    00:04
  • Number Data Types
    08:24
  • Number Data Types - Practice Set
    00:06
  • Number Data Types Quiz
    6 questions
  • Number Data Types Quiz Solutions
    03:03
  • Date/Time and Duration Data Types
    11:55
  • Date/Time and Duration Data Types - Practice Set
    00:08
  • Date/Time and Duration Data Types Quiz
    7 questions
  • Date/Time and Duration Data Types Quiz Solutions
    05:10

  • Adding New Columns
    06:42
  • Adding New Columns - Practice Set
    00:05
  • Adding New Columns - Quiz
    5 questions
  • Creating Conditional Columns
    04:29
  • Creating Conditional Columns - Practice Data
    00:04
  • Creating Conditional Columns - Quiz
    5 questions
  • PowerQuery Intellitext Support
    04:47
  • Creating Custom Columns
    07:22
  • Creating Custom Columns - Practice Data + Solutions
    00:11
  • G&T Blackjack Results
    7 questions
  • Custom Column Formula Functions
    06:02
  • Custom Column Formula Functions - Practice Set
    00:18
  • Try Using the M Language Reference - Quiz
    4 questions

  • Get and Transform: Practical Application Overview
    01:32
  • Data Cleansing and Preparation
    00:05
  • Merging, Linking, and Grouping Data
    00:12
  • Producing A Report
    00:12
  • Preview17:20

  • Wrap-Up
    03:51

Instructors

Explainly Io
Expert authors. Advanced topics. Clear explanations.
Explainly Io
  • 4.0 Instructor Rating
  • 85 Reviews
  • 9,019 Students
  • 3 Courses

Explainly offers online courses on powerful topics centered around the premise of instruction followed up with implementation through practical use.

Our founders, Philip Trick and Ahad Amdani, are experts in their respective fields and offer courses designed around their expertise with a history of success building online courses for other providers, including many popular Udemy publishers.

Philip Trick
Chief Actuary, Part-Time Developer, Course Creator
Philip Trick
  • 3.9 Instructor Rating
  • 60 Reviews
  • 318 Students
  • 2 Courses

I've been working as an actuarial consultant since completing my MA Economics at Boston University in 2006. In that time, I've become a bit of an Excel pro having built a wide variety of VBA and VSTO add-ins with a smattering of JavaScript add-ins thrown in to solve different issues I've encountered in my day-to-day work.

I've produced courses Excel and VBA courses for Simon Sez It and have since launched off on my own as part of the Explainly learning platform.

  • Udemy for Business
  • Teach on Udemy
  • Get the app
  • About us
  • Contact us
  • Careers
  • Blog
  • Help and Support
  • Affiliate
  • Terms
  • Privacy policy
  • Cookie settings
  • Sitemap
  • Featured courses
Udemy
© 2021 Udemy, Inc.