OpenCourser brand icon
Sign in
We may earn an affiliate commission when you visit our partners.

Microsoft Excel - Data Analytics Power Query and PivotTables

Kyle Pew and Office Newb

Microsoft Excel's Power Query tool is the biggest feature Microsoft has added to Excel since PivotTables. Seriously, no joke. Read on to learn more about Excel Power Query and how you'll save loads of time as you take advantage of this powerful feature.

In Excel (Power Query is NOT available on the MAC release of Excel)

Power Query is available on Excel 2010 and 2013 through a free download from Microsoft as described in the course.

Read more

Microsoft Excel's Power Query tool is the biggest feature Microsoft has added to Excel since PivotTables. Seriously, no joke. Read on to learn more about Excel Power Query and how you'll save loads of time as you take advantage of this powerful feature.

In Excel (Power Query is NOT available on the MAC release of Excel)

Power Query is available on Excel 2010 and 2013 through a free download from Microsoft as described in the course.

Lets face it, retrieving, cleaning and transforming data to report on can take hours of precious time. With Excel Power Query you can eliminate repetitive tasks freeing your time for other important tasks, like lunch and going home on time. Excel Power Query removes the hassle and complex formulas of manual tasks such as:

  • Finding the Data each time you need a report

  • Cleaning Columns of Data

    • Splitting or Joining Column Values

    • Removing unnecessary characters and extra spaces

    • Formatting data correctly

  • Filtering data needed for the report

  • Combining multiple Datasets into a master list

  • Manipulating the data layout to work with other tools, i.e. Excel PivotTables and Charts

In just 3 easy steps, you'll have a final report ready for presentation and your next raise.

  1. Get Data

  2. Transform/Clean Data

  3. Report on Data

Once you've got it all setup with Excel Power Query all you need to do is hit the Refresh button to update the report with next weeks data. Microsoft Power Query remembers all the steps you performed to get, transform and clean the data all you do is refresh and your report is updated.

Sounds all too easy, right? Enroll now and let me guide you through Excel Power Query and you'll quickly be on your way to harnessing the power of managing and reporting on data with Excel Power Query.

This Excel Power Query Course Includes:

  • 6+ hours of video content

  • Downloadable exercise files to follow along

  • Access to the instructor through the QA section

Enroll now

What's inside

Learning objectives

  • Create connections and discover external data sources
  • Clean, transform and prepare data sets for reporting within excel
  • Transform raw data with power query calculations
  • Create consistentcy in data with power querys powerful toolset
  • Streamline data work by automating with power query steps
  • Create clean data interfaces with power query parameters and excel variables

Syllabus

Introduction
Excel Power Query Course Introduction
How this Excel Power Query Course is Structured
Excel Power Query Exercise Files (DOWNLOAD)
Read more
Installing Power Query (if Needed)
Introduction to Excel Power Query
What is Power Query
The Power of Excel Power Query
EXERCISE 01: Reuse Power Query Steps
Introduction to Power Query's Interface
Introduction to Retrieving Data with Power Query
Import Data from Excel File with Power Query
Import Data from Excel Table with Power Query
Import Data from Text File with Power Query
Import Data from Database with Power Query
Import Data from Folder with Power Query
Import Data from Web with Power Query
Transforming and Cleaning Data with Excels Power Query
Editing Queries Created with Power Query
Editing Column Headers in Power Query
Power Query Data Types
Splitting Column Data with Power Query
EXERCISE 03: Spiltting Power Query Columns
SOLUTION DEMO FOR EXERCISE 00: Splitting Power Query Columns
Power Query Transforming Text Values
Replacing Data in Excel Power Query
Sorting Data with Power Query
Multi-Level Sorting in Power Query
Filtering Data with Power Query
Removing Duplicates from List in Power Query
EXERCISE 04: Clean Up Product Data Query
Using the Fill Command In Excel Power Query
Loading Power Query Data to Destinations
Loading Power Query Data to Workbook
Loading Power Query Data to Data Model
Excel Power Query Load Settings
Power Query Refresh Settings
Deleting Power Query Queries
Excel Power Query - Pivot and Unpivot Column Data
Excel Power Query Transpose Query Data
Excel Power Query Pivot Column Command in Action
EXERCISE 05: Order Info Pivot Column
Excel Power Query Unpivot Columns Command in Action
EXERCISE 06: Monthly Sales Unpivot Column
Grouping Power Query Data
Create a Duplicate Query
Group and Summarize Power Query Data
Power Query Advanced Data Grouping
EXERCISE 07: Power Query Grouping Data
Creating Custom Columns with Calculations in Power Query
Creating Custom Columns in Power Query
Creating Conditional Columns with Power Query
Creating Calculated Columns Based on Dates
Power Query Name of Day Date Calculation
Creating a Custom Column Calculation
Creating Index Columns in Power Query
EXERCISE 08: Creating Calculated Columns
Excel PivotTables and Power Query Data
Creating a PivotTable based on Power Query Data
Grouping Data with Excel PivotTables
Grouping Date Data with Excel PivotTables
Modify Power Query Source to Include Additional Data
Modify Excel PivotTable Calculations
Analyzing PivotTable Data with PivotCharts
Inserting PivotTable Slicers to Filter Data
Inserting PivotTable Timeline Slicers to work with Dates
Updating Excel Source Data and Refreshing PivotTable Data
EXERCISE 09: Reporting on Power Query Data with an Excel PivotTable
Working with Multiple Sources in Power Query
Mutiple Excel Tables in Power Query
Understanding Table Relationships in Power Query
Merging Queries with Excel Power Query
Appending Queries with Excel Power Query
EXERCISE 10: Merge Data Sources with Excel Power Query
Excel Power Query Complex Data Clean Up
The Importance of Clean, Organized Data
Excel Power Query Convert Column of Data to Rows
Excel Power Query Clean up Nested Data
EXERCISE 11: Excel Power Query Columns to Rows
Excel Power Query Parameters
Understanding Power Query Data Parameters
Working with Power Query Parameters
Manage Power Query Parameters
Excel Power Query List Parameters
Excel Table Variables and Power Query
Working with Excel Table Variables in Power Query
Excel Table Variable Filtering Query
Automatic Refresh of Data on Country Change
Excel Macro Enabled Workbook Save As...
Congratulations -- You've Completed the Excel Power Query Course
Congratulations

Good to know

Know what's good
, what to watch for
, and possible dealbreakers
Focuses on Power Query, a powerful Excel tool for data retrieval, cleaning, and transformation, which can significantly reduce time spent on repetitive data tasks
Teaches how to automate data workflows with Power Query steps, which can streamline data work and improve efficiency in reporting
Explores techniques for combining multiple datasets into a master list, which is essential for comprehensive data analysis and reporting
Requires Excel 2010 or 2013 and a free Power Query download from Microsoft, which may present an extra step for some users
Emphasizes using Power Query with PivotTables and Charts, which are standard tools for data summarization and visualization in Excel
Covers creating clean data interfaces with Power Query parameters and Excel variables, which enhances the usability and maintainability of data reports

Save this course

Save Microsoft Excel - Data Analytics Power Query and PivotTables to your list so you can find it easily later:
Save

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in Microsoft Excel - Data Analytics Power Query and PivotTables with these activities:
Review Excel Fundamentals
Solidify your understanding of core Excel concepts to better grasp Power Query and PivotTable functionalities.
Browse courses on Excel Formulas
Show steps
  • Review basic Excel functions like SUM, AVERAGE, and COUNT.
  • Practice creating and formatting tables.
  • Familiarize yourself with cell referencing (relative, absolute, mixed).
Read 'Excel Power Query Recipes'
Explore advanced Power Query techniques and real-world examples to enhance your data manipulation skills.
View Alter Ego: A Novel on Amazon
Show steps
  • Select chapters relevant to your data challenges.
  • Follow the recipes and adapt them to your own datasets.
  • Experiment with different Power Query functions.
Data Cleaning Challenges
Sharpen your data cleaning skills by working through a series of progressively challenging datasets.
Show steps
  • Find datasets online with common data quality issues.
  • Use Power Query to clean and transform the data.
  • Compare your results with solutions or best practices.
Four other activities
Expand to see all activities and additional details
Show all seven activities
Advanced Power Query Techniques
Explore advanced Power Query topics like custom functions and error handling through online tutorials.
Show steps
  • Search for tutorials on specific Power Query functions or techniques.
  • Follow the tutorials step-by-step and apply them to your own data.
  • Experiment with different parameters and settings.
Automated Report Dashboard
Build a fully automated report dashboard using Power Query and PivotTables to showcase your skills.
Show steps
  • Identify a data source that requires regular reporting.
  • Use Power Query to connect to the data source and clean the data.
  • Create PivotTables and PivotCharts to visualize the data.
  • Automate the refresh process to update the dashboard with new data.
Read 'M is for Data Monkey'
Deepen your understanding of the M language to unlock the full potential of Power Query.
View M Is for (Data) Monkey: A Guide to the M... on Amazon
Show steps
  • Focus on chapters related to custom functions and advanced transformations.
  • Practice writing M code and testing it in Power Query.
  • Refer to the book as a reference when building complex queries.
Analyze Public Datasets
Apply your Power Query and PivotTable skills to analyze publicly available datasets and extract meaningful insights.
Show steps
  • Find a public dataset that interests you (e.g., government data, economic indicators).
  • Use Power Query to import, clean, and transform the data.
  • Create PivotTables and PivotCharts to explore the data and identify trends.
  • Document your findings and present them in a clear and concise manner.

Career center

Learners who complete Microsoft Excel - Data Analytics Power Query and PivotTables will develop knowledge and skills that may be useful to these careers:
Reporting Analyst
Reporting analysts are responsible for generating reports that summarize key performance indicators and other business metrics. The course can be immediately applied by reporting analysts to automate the process of data preparation in Excel. The course teaches how to use Power Query to connect to various data sources, clean and transform data, and create consistent reports. The sections on automating tasks and creating parameters are crucial for streamlining the reporting process and ensuring accuracy. The skills acquired in this course help free up time for reporting analysts to focus on interpreting the data and providing insights.
See salaries and explore the career path for Reporting Analyst
Data Analyst
A data analyst gathers, cleans, and analyzes data to identify trends and insights. They use this information to help organizations make better decisions. This course helps data analysts automate data retrieval, cleaning, and transformation using Excel Power Query. Power Query allows for repeatable processes, so that the data analyst can focus on analysis rather than data preparation. The course's modules on transforming text values, filtering data, and removing duplicates are directly applicable to a data analyst's daily tasks. Learning to utilize Power Query parameters can also significantly improve efficiency.
See salaries and explore the career path for Data Analyst
Market Research Analyst
Market research analysts study market conditions to examine potential sales of a product or service. A market research analyst may find that this course provides a way to enhance their data management skills. The course provides instruction in data cleaning, transformation, and preparation for reporting within Excel. The Power Query toolset may be useful for maintaining data consistency when working with multiple or raw data sources. With Power Query, market research analysts can spend less time on data preparation and more time on analyzing market trends.
See salaries and explore the career path for Market Research Analyst
Project Manager
Project managers plan, execute, and close projects, ensuring they are completed on time and within budget. A project manager can use this course to enhance project reporting capabilities within Excel. The course teaches how to use Power Query to consolidate data from various sources, clean and transform it, and create reports that provide insights into project status. Project managers can apply the concepts from the course to streamline the reporting process and ensure that project stakeholders have access to accurate and timely information. The sections on reporting on data with PivotTables will be especially useful.
See salaries and explore the career path for Project Manager
Business Intelligence Analyst
Business intelligence analysts examine market trends and a company's performance to advise leaders on the best courses of action. This course may come in handy for business intelligence analysts who need to work with Excel. The course provides a thorough grounding in using Power Query to import, clean, and transform data from various sources, an essential skill for creating meaningful reports. Business intelligence analysts can use the knowledge gained from this course to streamline data preparation and focus on delivering actionable insights. The sections on merging and appending queries from multiple sources will be particularly relevant.
See salaries and explore the career path for Business Intelligence Analyst
Data Visualization Specialist
Data visualization specialists create visual representations of data to communicate insights effectively. This course helps data visualization specialists who rely on Excel as part of their workflow. The course may come in handy for preparing data for visualization using Excel's Power Query tool. The data visualization specialist will learn to clean, transform, and structure data effectively, ensuring that their visualizations are based on accurate and well-prepared information. The sections on data transformation and cleaning will be particularly relevant.
See salaries and explore the career path for Data Visualization Specialist
Financial Analyst
Financial analysts provide guidance to businesses and individuals making investment decisions. This course may be useful in helping financial analysts who need to work with Excel. They can leverage Power Query to efficiently import financial data, clean it, and prepare it for analysis. The course's focus on data transformation and cleaning helps financial analysts automate repetitive tasks, such as formatting data and removing unnecessary characters, thus saving time and improving accuracy. The modules on creating custom columns with calculations will also be highly relevant.
See salaries and explore the career path for Financial Analyst
Business Systems Analyst
Business systems analysts evaluate a company's systems and processes to identify areas for improvement. This course may be helpful for business systems analysts who use Excel to analyze data and create reports. The business systems analyst can learn to use Power Query to automate data extraction, cleaning, and transformation tasks, which frees up time to focus on system analysis and process improvement. The sections on connecting to various data sources and automating repetitive tasks are most applicable.
See salaries and explore the career path for Business Systems Analyst
Database Administrator
Database administrators maintain and secure databases, ensuring data integrity and availability. This course may appeal to database administrators who want to improve their skills in Excel. While their primary focus is on database management systems, they can use Excel and Power Query to extract, transform, and load data for reporting or analysis purposes. The course helps database administrators understand how to connect Excel to databases, clean and transform data using Power Query, and load the data into Excel for further analysis or reporting. The sections on importing data from databases and transforming data will be most useful.
See salaries and explore the career path for Database Administrator
Management Consultant
Management consultants advise organizations on how to improve their efficiency and effectiveness. This course may be useful for a management consultant who uses Excel to analyze data. The course provides a foundation for using Power Query to streamline data preparation, allowing them to quickly gather, clean, and transform data from different sources for analysis. The course material about automating repetitive tasks and creating parameters can save time and improve efficiency when working on consulting projects.
See salaries and explore the career path for Management Consultant
Actuary
An actuary analyzes risk and uncertainty and often works in the insurance and finance industries. A course on Excel Power Query and PivotTables may be useful for an actuary because they can use data analysis tools to find patterns in data. This course may help the actuary manipulate data and generate reports to present to their stakeholders. Actuaries may want to become more efficient in their data analysis, to spend more time on interpreting the data.
See salaries and explore the career path for Actuary
Statistician
Statisticians collect, analyze, and interpret data to identify trends and relationships. This course may be useful for statisticians who need to work with data in Excel. The course may provide ways to clean, transform, and prepare data using Excel's Power Query. This can streamline the data preparation process, allowing statisticians to focus on statistical analysis and modeling. The sections on transforming and cleaning data will be particularly relevant.
See salaries and explore the career path for Statistician
Economist
Economists study the production and distribution of resources, goods, and services by collecting and analyzing data, researching trends, and evaluating economic issues. This course, because of its orientation toward using Excel as a tool, is typically not oriented toward dedicated economists. The economist may find the process of using Excel as a data analysis tool to be extremely limiting. Instead, economists may prefer to use other computational or statistical tools that have less limitations and offer more freedom.
See salaries and explore the career path for Economist
Researcher
Researchers design and conduct studies to investigate various phenomena and contribute to the body of knowledge in their field. This course, because of its orientation toward using Excel as a tool, is typically not oriented toward those who conduct dedicated research. Dedicated researchers may find the process of using Excel as a data analysis tool to be extremely limiting. Instead, researchers may prefer to use other computational or statistical tools that have less limitations and offer more freedom.
See salaries and explore the career path for Researcher
Data Engineer
Data engineers build and maintain the infrastructure for data storage and processing. This role typically requires an advanced degree. This course, because of its orientation toward using Excel as a tool, is typically not oriented toward data engineers. Data engineers spend much of their time writing code and maintaining database systems. They are typically more interested in the back-end of data analysis rather than the front-end with Excel. Data engineers typically maintain and build the infrastructure and databases that data analysts use.
See salaries and explore the career path for Data Engineer

Reading list

We've selected two books that we think will supplement your learning. Use these to develop background knowledge, enrich your coursework, and gain a deeper understanding of the topics covered in Microsoft Excel - Data Analytics Power Query and PivotTables.
Cover image
M Is for (Data) Monkey
Save
Comprehensive guide to the M language used in Excel Power Query. It delves into the syntax, functions, and capabilities of M, enabling users to create advanced data transformations. It is particularly useful for those looking to go beyond the basics of Power Query and automate complex data cleaning and shaping tasks. This book provides a deeper understanding of the engine behind Power Query.
M Is for (Data) Monkey: A Guide to the M Language...
Kindle Edition
$$
Cover image
Alter Ego
Save
Provides practical solutions to common data cleaning and transformation challenges using Power Query. It serves as a valuable reference for tackling real-world data scenarios. The book expands on the course material by offering a wider range of Power Query techniques and use cases. It useful reference tool for intermediate to advanced Excel users.
Alter Ego: A Novel
Paperback
$$

Share

Help others find this course page by sharing it with your friends and followers:
Facebook
LinkedIn
Reddit
X
Link
Email

Similar courses

Similar courses are unavailable at this time. Please try again later.
Effort
6 total hours
Via
Udemy
Instructors
Kyle Pew
Office Newb
Language
English
Good to know
Focuses on Power Query, a powerful Excel tool for data retrieval, cleaning, and transformation, which can significantly reduce time spent on repetitive data tasks
Teaches how to automate data workflows with Power Query steps, which can streamline data work and improve efficiency in reporting
Explores techniques for combining multiple datasets into a master list, which is essential for comprehensive data analysis and reporting
Requires Excel 2010 or 2013 and a free Power Query download from Microsoft, which may present an extra step for some users
Emphasizes using Power Query with PivotTables and Charts, which are standard tools for data summarization and visualization in Excel
Covers creating clean data interfaces with Power Query parameters and Excel variables, which enhances the usability and maintainability of data reports
Share and help others discover this course.
Facebook LinkedIn X Reddit Link Email
Don't miss out
Enroll today to gain access to this course
Enroll in this course
Our mission

OpenCourser helps millions of learners each year. People visit us to learn workspace skills, ace their exams, and nurture their curiosity.

Our extensive catalog contains over 50,000 courses and twice as many books. Browse by search, by topic, or even by career interests. We'll match you to the right resources quickly.

Find this site helpful? Tell a friend about us.

Affiliate disclosure

We're supported by our community of learners. When you purchase or subscribe to courses and programs or purchase books, we may earn a commission from our partners.

Your purchases help us maintain our catalog and keep our servers humming without ads.

Thank you for supporting OpenCourser.

© 2016 - 2025 OpenCourser