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.