Power Query and Power Pivot in Excel

Power Query and Power Pivot in Excel
Outline:	Outline
Training Material:	Material Lab Files.zip

Day	Modules	Sharing
1	Exploring the Limits of Excel and How Databases Help Getting to Know Database Terminology Understanding Relationships Understanding the Power Pivot Internal Data Model Linking Excel Tables to Power Pivot Introducing the Pivot Table Defining the Four Areas of a Pivot Table Creating Your First Pivot Table Customizing Pivot Table Reports Understanding Slicers Creating a Standard Slicer Getting Fancy with Slicer Customizations Controlling Multiple Pivot Tables with One Slicer Creating a Timeline Slicer Loading Data from Relational Databases Loading Data from Flat Files Loading Data from Other Data Sources Refreshing and Managing External Data Connections Directly Feeding the Internal Data Model Managing Relationships in the Internal Data Model Managing Queries and Connections Creating a New Pivot Table Using the Internal Data Model Filling the Internal Data Model with Multiple External Data Tables	Slides: Introduction Introducing Power Pivot The Pivotal Pivot Table Using External Data with Power Pivot Working Directly with the Internal Data Model Demo Materials: Day1.zip
2	Enhancing Power Pivot Data with Calculated Columns Utilizing DAX to Create Calculated Columns Understanding Calculated Measures Free Your Data with Cube Functions DAX Language Fundamentals Understanding Filter Context Power Query Basics Understanding Column-Level Actions Understanding Table Actions Importing Data from Files Importing Data from Database Systems Managing Data Source Settings Data Profiling with Power Query	Slides: Adding Formulas to Power Pivot Diving into DAX Introducing Power Query Power Query Connection Types Demo Materials: Day2.zip
3	Completing Common Transformation Tasks Creating Custom Columns Grouping and Aggregating Data Working with Custom Data Types Reusing Query Steps Understanding the Append Feature Understanding the Merge Feature Understanding Fuzzy Match Creating and Using a Basic Custom Function Creating a Function to Merge Data from Multiple Excel Files Creating Parameter Queries Limit the Number of Rows and Columns in Your Data Model Tables Use Views Instead of Tables Avoid Multi-Level Relationships Let the Back-End Database Servers Do the Crunching Beware of Columns with Many Unique Values Limit the Number of Slicers in a Report Create Slicers Only on Dimension Fields Disable the Cross-Filter Behavior for Certain Slicers Use Calculated Measures Instead of Calculated Columns Upgrade to 64-Bit Excel Getting Quick Information from the Queries & Connections Pane Organizing Queries in Groups Selecting Columns in Queries Faster Renaming Query Steps Quickly Creating Reference Tables Viewing Query Dependencies Setting a Default Load Behavior Preventing Automatic Data Type Changes Disabling Privacy Settings to Improve Performance Disabling Relationship Detection	Slides: Transforming Your Way to Better Data Making Queries Work Together Extending Power Query with Custom Functions Ten Ways to Improve Power Pivot Performance Ten Tips for Working with Power Query Demo Materials: Day3.zip Useful Links: 10 Skills to Learn to Become a Business Intelligence Analyst SQL Tutorial

Recommanded Books
	Title	Collect, Combine, and Transform Data Using Power Query in Power BI and Excel, Second Edition
	ISBN	978-0-138-11586-9
	Author	Daniil Maslyuk; Gil Raviv
	Year	2025
	Publisher	Microsoft Press
	Title	The Definitive Guide to Power Query (M): Mastering Complex Data Transformation with Power Query
	ISBN	978-1-835-08972-9
	Author	Gregory Deckler, Rick de Groot, Melissa de Korte
	Year	2024
	Publisher	Packt Publishing
	Title	Mastering Power Query in Power BI and Excel: Learning real-world Power Query and M Techniques for a better data analysis
	ISBN	B09DTLC4S2
	Author	Reza Rad, Leila Etaati
	Year	2021
	Publisher	RADACAD Systems Limited
	Title	Collect, Combine, and Transform Data Using Power Query in Excel and Power BI
	ISBN	978-1-509-30795-1
	Author	Gil Raviv
	Year	2018
	Publisher	Microsoft Press
	Title	Excel Power Pivot and Power Query for dummies
	ISBN	978-1-119-84448-8
	Author	Michael Alexander
	Year	2022
	Publisher	For Dummies; 2nd edition
	Title	Pro Data Mashup for Power BI: Powering Up with Power Query and the M Language to Find, Load, and Transform Data
	ISBN	978-1-484-28577-0
	Author	Adam Asprin
	Year	2022
	Publisher	Apress
	Title	Power BI Data Modeling: Build Interactive Visualizations, Learn DAX, Power Query, and Develop BI Models (English Edition)
	ISBN	978-9-389-32883-7
	Author	Nisal Mihiranga
	Year	2022
	Publisher	BPB Publications
	Title	Power Query Cookbook: Use effective and powerful queries in Power BI Desktop and Dataflows to prepare and transform your data
	ISBN	978-1-800-56948-5
	Author	Andrea Janicijevic
	Year	2021
	Publisher	Packt Publishing
	Title	Power Pivot and Power BI. The Excel User’s Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016
	ISBN	978-1-615-47039-6
	Author	Rob Collie, Avichal Singh
	Year	2021
	Publisher	Holy Macro
	Title	Learn Power Query: A low-code approach to connect and transform data from multiple sources for Power BI and Excel
	ISBN	978-1-839-21971-9
	Author	Linda Foulkes, Warren Sparrow
	Year	2020
	Publisher	Packt Publishing
	Title	Data Mashup with Microsoft Excel Using Power Query and M: Finding, Transforming, and Loading Data from External Sources
	ISBN	978-1-484-26017-3
	Author	Adam Aspin
	Year	2020
	Publisher	Apress
	Title	Master Your Data with Power Query in Excel and Power BI
	ISBN	978-1-615-47358-8
	Author	Ken Puls;Miguel Escobar
	Year	2019
	Publisher	Holy Macro! Books
	Title	Cleaning Excel Data With Power Query Straight to the Point
	ISBN	978-1-615-47149-2
	Author	Du Soleil, Oz
	Year	2019
	Publisher	Independent Publishers Group
	Title	Collect, Transform and Combine Data using Power BI and Power Query in Excel
	ISBN	978-1-509-30795-1
	Author	Gil Raviv
	Year	2018
	Publisher	Microsoft Press
	Title	PowerPivot for advanced reporting and dashboards
	ISBN	978-1-849-69868-9
	Author	Bosco, Robert J
	Year	2014
	Publisher	Packt Publishing
	Title	PowerPivot Alchemy: Patterns and Techniques for Excel
	ISBN	978-1-615-47021-1
	Author	Jelen B., Collie R.
	Year	2014
	Publisher	Holy Macro! Books
	Title	Dashboarding and Reporting with Power Pivot and Excel: How to Design and Create a Financial Dashboard with PowerPivot – End to End
	ISBN	978-1-615-47027-3
	Author	Jonge, Kasper de
	Year	2014
	Publisher	Holy Macro! Books
	Title	Using Microsoft Excel 365, Stocks Data Type, Power Query, Pivot Charts, REST Web Services, And JSON To Web Scrape Market, Financial and Economic Data
	ISBN
	Author	Lee, John
	Year	2020
	Publisher