Fundamentals
In Power Query And M
From Raw Data to Refined Insights
Power Query & M: The Dynamic Duo
Power Query: Microsoft's ETL tool, allowing users to extract,
transform, and load data with ease.
M: The powerful scripting language behind Power Query, enabling
advanced data manipulations.
Applications: Essential for Excel and Power BI users to clean and
shape data.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Connecting to Data
Variety: Connect to databases, online services, Excel, and
more.
Direct vs. Import: Understand the difference and when to use
each.
Refreshing Data: Keeping your data up-to-date.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Navigating the Query Editor
Interface: Familiarize with the ribbon, formula bar, and side
panes.
Steps Pane: Track and modify transformation steps.
Preview Pane: View your data as you transform it.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Getting Started with Transformations
Filtering: Remove unnecessary rows.
Sorting: Order your data for clarity.
Renaming & Reordering: Organize your columns.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Power Query in Power BI
Shared Engine: Power Query's role in Power BI.
Direct Query vs. Import: Data connection strategies.
Scheduled Refresh: Keeping data up-to-date in Power BI Service.
Power Query is integral to the Power BI data modeling process.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Building Blocks of Power Query
Lists: Ordered collections of items. Think of them as simple
columns of data.
Records: Named values, similar to rows in a table with field
names and values.
Tables: Collections of records. Essentially, what we see as tables
in Excel or databases.
Queries: Sequences of steps to transform source data into a
desired shape.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Mastering Tables in M
Basics: Tables are structured as lists of records, each representing
a row.
Creation: Use the hash or pound table function to create tables
from columns and records.
Manipulation: Functions like table.selectRows and
table.addColumn allow for table transformations.
Tables are central to most data transformation tasks in Power
Query.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
The Power of Records
Definition: Records are sets of fields with a unique name (field name)
and an associated value.
Usage: Useful for representing individual rows in a table or specific
data points.
Nested Records: Records can contain other records, allowing for
hierarchical data structures.
Records provide flexibility in handling data, especially when dealing
with complex data structures.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Functions & Operations
Unary Functions: Functions that operate on a single input. E.g.,
table.selectRows.
Combining Data: Use the ampersand (&) to append tables or
combine lists.
Case Sensitivity: M is case-sensitive, so "Table" and "table" are
treated differently.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Data Quality & Distribution
Column Quality: Visual indicators in Power Query show the health
of your data column.
Distribution Insights: Understand how data is spread across
unique values.
Error Handling: Power Query provides tools to identify and rectify
errors in data.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Getting to Know Your Data
Column Profiles: Dive deep into individual columns.
Distribution: Understand value distribution.
Quality Metrics: Spot errors, missing values, and outliers.
Data profiling is the first step to clean and reliable data.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Elevate Your Power Query Game
"Let" Expression: Use "let" for clarity and to break down complex
transformations.
Parameterize Queries: Make your queries dynamic by using
parameters.
Comments: Use '//' to add comments in M code for clarity and
documentation.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Dive Deeper into Data Shaping
Pivot & Unpivot: Change data structure for analysis.
Grouping: Aggregate data based on criteria.
Merging & Appending: Combine multiple datasets.
Advanced transformations unlock deeper insights.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Manipulating Text Data
Splitting: Divide text into multiple columns.
Trimming: Remove unwanted spaces.
Changing Case: Convert text to UPPER, lower, or Proper Case.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Working with Date & Time
Extracting: Get year, month, day, etc.
Duration: Calculate the difference between dates/times.
Time Zones: Adjust and convert time zones.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Adding Conditions to
Transformations
"If" Statements: Apply logic based on conditions.
Switch Cases: Handle multiple conditions.
Error Handling: Use conditions to manage errors.
Conditional logic adds dynamic capabilities to your transformations.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Custom Columns & M Formulas
Adding Columns: Create new data based on existing columns.
M Formulas: Use M language for custom calculations.
Parameters: Make your formulas dynamic.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Managing Data Errors
Identifying Errors: Spot issues in your data.
Replacing Errors: Substitute errors with meaningful values.
Removing Errors: Cleanse your data of problematic rows.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Making Your Queries Efficient
Simplify models: Reduce complexity for better performance.
Use descriptive names: E.g., "Total Sales" instead of "TS".
Document measures and changes: Maintain a changelog or
notes.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Power Query Magic
Merging and appending queries: Combine data from multiple
sources.
Grouping and aggregating data: Summarize data before loading.
Error handling and cleaning: Remove duplicates, handle missing
values.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Power Query Best Practices
Documentation: Comment your M code.
Modularity: Break complex queries into simpler parts.
Version Control: Track changes and maintain versions.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Data Types in M
Primitive Types: Text, Number, Date, Time, etc.
Structured Types: List, Record, Table.
Special Types: Binary, Any, Optional, and more.
Knowing data types is crucial for effective data transformation.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Visualizing Query Dependencies
Dependency View: Understand how queries relate to each other.
Benefits: Identify bottlenecks, optimize load times.
Managing Dependencies: Best practices for a clean workflow.
A clear view of dependencies ensures efficient data loading.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Dynamic Data Loading
What are Parameters?: Dynamic values in queries.
Use Cases: Different data sources, variable filters.
Benefits: Reusable queries, user-driven reports.
Parameters make your queries adaptable and user-friendly.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Creating Custom Functions
Why Custom Functions?: Reuse complex logic.
Defining & Invoking: How to write and use your functions.
Examples: Date difference, custom aggregations.
Custom functions enhance reusability and reduce redundancy.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Keeping Your Data Safe
Privacy Levels: Public, organizational, private data.
Data Masking: Hide sensitive data in shared queries.
Query Diagnostics: Understand and optimize data access.
Security and privacy are paramount in data operations.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Never Stop Learning
Microsoft Documentation: The official source for all things Power
Query and M.
Community Forums: Engage with experts and enthusiasts
Blogs & Tutorials: Check out our handful of trainings and resources!
Continuous learning is key to staying updated and mastering
Power Query.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
Conclusion & Further Learning
The best way to learn is by doing.
Power Query & M evolve – stay updated.
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
FUTURE-PROOF YOUR CARREER MASTER DATA SKILLS UPSKILL IN AI
ENTERPRISEDNA.CO
