Excel Business Intelligence: Power Query
2h 1mIntermediate2018-05-14
Authors
Chris Dutton
Certified Microsoft Excel Expert, Analytics Consultant
Course details
Microsoft Excel includes a powerful feature called Power Query—also known as Get & Transform or Get Data—which provides fast and powerful data gathering and cleansing capabilities. In this course—the first installment in the Excel Business Intelligence series—follow along with experienced Excel trainer Chris Dutton as he shows you the robust capabilities of Power Query. Chris kicks off the course by outlining the power Excel landscape and spelling out when to use business intelligence tools like Power Query, Power Pivot, and DAX. He then dives into Power Query, explaining how to leverage key query editing tools to connect and transform data. Learn about basic Power Query table transformations, text-specific query editing tools, how to merge and append queries, and more. Chris also shares best practices for working efficiently with Power Query.
Learning objectives
Determine the reason for errors when using Power Query.
Examine troubleshooting options for issues affecting Excel performance while using Power Query and Power Pivot.
Identify table transformation tools.
Describe the functionality of text-specific query editing tools.
Explain limitations of number-specific query editing tools.
Review pivoting and unpivoting in Power Pivot.
Learning objectives
Determine the reason for errors when using Power Query.
Examine troubleshooting options for issues affecting Excel performance while using Power Query and Power Pivot.
Identify table transformation tools.
Describe the functionality of text-specific query editing tools.
Explain limitations of number-specific query editing tools.
Review pivoting and unpivoting in Power Pivot.
Skills covered
SpreadsheetsMicrosoft ExcelData AnalysisData ScienceBusiness Analysis and StrategyBusiness Software and ToolsMicrosoftOne-Off
Concepts
0. Introduction
- 01 - Welcome
1. Getting Started
- 02 - Important - Versions and compatibility
- 03 - Download the course exercise files
- 04 - Set expectations
2. Intro to the Power Excel Series
- 05 - The power Excel workflow
- 06 - The best thing to happen to Excel in 20 years
- 07 - When to use Power Query and Power Pivot in Excel
3. Power Query
- 08 - Power Query introduction
- 09 - Meet Power Query aka Get & Transform
- 10 - The Query Editor
- 11 - Options for loading data in Excel
- 12 - Basic Power Query table transformations
- 13 - Text-specific query editing tools
- 14 - Number-specific query editing tools
- 15 - Date-specific query editing tools
- 16 - Create a rolling calendar with Power Query
- 17 - Add index and conditional columns with Power Query
- 18 - Group and aggregate data with Power Query
- 19 - Modify Excel workbook queries
- 20 - Merge queries
- 21 - Append queries
- 22 - Connect Excel to a folder of files
- 23 - Excel Power Query best practices
- 24 - Pivot and unpivot data with Power Query
Conclusion
- 25 - Next steps