Power Query: A Complete Guide from a Data Analyst Integrating Enterprise Asset Data

Expert Insight by

Pooja Vishnu Chavan

Data Analyst, Toyota Lift Northeast

Data Analytics / Enterprise OperationsLinkedIn

Pooja has 5+ years of data analysis experience across enterprise operations, IT, and technology. She currently integrates asset, inventory, service, and customer data at Toyota Lift Northeast during a major enterprise merger, reducing reporting discrepancies by 20%. Previously, she built PostgreSQL analytics and automated data quality workflows at UMBC, improving decision turnaround by 25%. She holds an MS in Information Systems from UMBC and is an AWS Certified Cloud Practitioner.

Verified Expert

When I joined Toyota Lift Northeast during a major enterprise merger, I inherited datasets from two different systems that needed to be integrated: legacy asset data from the acquired company and target system data from Toyota Lift. Field names didn't match. Date formats were inconsistent. Some records were duplicated across systems while others had missing values.

I had three options: write SQL scripts to clean everything in the database, build Python notebooks for the transformation, or use Power Query to create a repeatable, visual workflow that anyone could understand and maintain.

I chose Power Query. Here's why it was the right call:

Power Query exists in two main places:

The interface and M language are nearly identical between them. Skills transfer directly.

I've worked with SQL, Python (Pandas), and Power Query across different organizations. Each has its place, but Power Query fills a unique gap that pure code solutions miss.

No-Code/Low-Code Transformation

Most Power Query tasks require zero coding. You click through a visual interface:

• Remove columns: Right-click → Remove
• Filter rows: Click the dropdown → filter values
• Change types: Right-click column → Change Type
• Merge tables: Home → Merge Queries

Every click generates M code automatically. You get the power of a programming language with the accessibility of a spreadsheet.

For analysts who aren't developers, this is transformative. You're not blocked waiting for IT or data engineering to clean data for you.

Reproducibility That Business Users Understand

When I wrote Python scripts at UMBC to clean incident data, the logic was technically documented — in code. But when I handed off the project, the next analyst struggled to understand what the transformations were doing.

Power Query's step-by-step interface changes this:

Applied Steps:
  Source
  Navigation
  Promoted Headers
  Changed Type
  Filtered Rows (Removed nulls in AssetID)
  Merged Queries (Joined with ServiceHistory)
  Expanded ServiceHistory
  Removed Duplicates
  Added Custom (Calculated DaysSinceService)

Each step has a plain-English name. Click any step to see the data at that point. Non-technical stakeholders can follow the logic.

Query Folding: Let the Database Do the Work

This is where Power Query gets clever. For supported data sources (SQL Server, PostgreSQL, Oracle, many others), Power Query translates your visual transformations into native database queries.

Direct Integration with Power BI

For analysts building dashboards, Power Query eliminates the gap between data prep and visualization:

1. Connect to your sources in Power Query
2. Transform data with visual steps
3. Load directly into Power BI data model
4. Build visualizations immediately
5. Schedule refresh to keep data current

No intermediate files. No manual exports. Changes in your transformation automatically flow to dashboards.

Let me walk you through a real transformation workflow. We'll start with a messy data file and create clean, analysis-ready output.

Connecting to Data

In Power BI Desktop (or Excel):

1. Click Home → Get Data
2. Choose your source type (Excel, CSV, Database, Web, etc.)
3. Navigate to your file or enter connection details
4. Select the tables/sheets you want

Basic Transformations

Here's the sequence I follow for most data cleaning tasks:

• Dates showing as text → Change to Date
• Numbers showing as text → Change to Decimal/Whole Number
• IDs that should stay as text → Keep as Text (avoid leading zero problems)

• Right-click column → Replace Values → Replace null with your default
• Or filter: Click column dropdown → uncheck (null)

Example: Cleaning Asset Data

Here's a real example from my Toyota Lift work. Raw asset data came like this:

Problems: duplicates, inconsistent date formats, inconsistent case, nulls, missing values.

1. Promoted Headers (first row had column names)
2. Changed Type: Set acq_date to Date (handles both formats)
3. Replaced Values: In loc column, replaced null and blank with "UNKNOWN"
4. Transformed: status column → Transform → Lowercase (standardizes case)
5. Filled Down: For asset_name, used Fill → Down to carry values into blank rows
6. Removed Duplicates: Selected asset_id → Remove Duplicates

Result:

Clean, consistent, deduplicated — ready for analysis.

One of Power Query's biggest strengths is its ability to connect to diverse data sources in a single workflow. Here's what I use in enterprise environments.

Databases

• Server name and port
• Database name
• Credentials (Windows or database authentication)

Home → Get Data → Database → PostgreSQL

Files

Get Data → Folder → [Select folder path]

Power Query creates a function that applies the same transformation to each file, then appends results. I use this for monthly report files that arrive with the same structure.

Cloud Sources

Get Data → Web → [Enter URL]

For APIs returning JSON, Power Query automatically parses the structure. You'll need to expand nested records and lists.

Combining Multiple Sources

The real power comes from combining sources. In my Toyota Lift work, I merged:

• PostgreSQL table: Current asset inventory
• Excel file: Legacy asset data from acquired company
• CSV export: Service history records

Steps to merge:

1. Create separate queries for each source
2. Clean each query independently
3. Use Home → Merge Queries to join on common keys
4. Select join type (Left, Inner, Full, etc.)
5. Expand the merged columns you need

This is where Power Query goes from useful to essential. Data quality issues — duplicates, nulls, type mismatches, orphaned records — kill report credibility. I build validation into every Power Query workflow.

Identifying Missing Values

Quick null check for any column:

1. Select column
2. View → Column Quality (shows % valid, error, empty)
3. View → Column Distribution (shows value counts including nulls)

For systematic validation, create a validation query:

// M code to count nulls per column
let
    Source = YourMainQuery,
    ColumnNames = Table.ColumnNames(Source),
    NullCounts = List.Transform(ColumnNames, each 
        [Column = _, NullCount = List.Count(List.Select(Table.Column(Source, _), each _ = null))]
    ),
    Result = Table.FromRecords(NullCounts)
in
    Result

This produces a table showing null counts by column — your data quality scorecard.

Catching Duplicates

Duplicates are subtle. A record might be truly duplicated (error) or legitimately appear multiple times (valid). Context matters.

1. Select your key column(s)
2. Home → Keep Rows → Keep Duplicates
3. Review the results

If you have duplicates with different values in non-key columns, decide your rule:

• Keep first? Sort first, then remove duplicates.
• Keep latest? Sort by date descending, then remove duplicates.
• Keep most complete? This requires custom M logic.

Data Type Validation

Type mismatches are silent killers. A date stored as text won't sort correctly. A number with leading zeros stored as a number loses the zeros.

1. Transform → Detect Data Type on the whole table
2. Check for columns that became "Any" type (mixed types in data)
3. For each problem column, use Add Column → Column From Examples to extract the correct format, or Add Column → Custom Column with explicit type conversion

// Safe type conversion with error handling
try Number.From([Amount]) otherwise null

This converts the value if possible, returns null if not — no query failures.

Building Validation Queries

I create separate validation queries that run alongside main data queries:

= Table.SelectRows(CleanedAssets, each [AssetID] = null or [AssetID] = "")

= Table.SelectRows(
    Table.Group(Transactions, {"TransactionID"}, {{"Count", each Table.RowCount(_)}}),
    each [Count] > 1
)

= Table.NestedJoin(ServiceRecords, "AssetID", Assets, "AssetID", "AssetMatch", JoinKind.LeftAnti)

Once you've mastered basics, these transformations unlock complex scenarios.

Pivot and Unpivot

Now you can filter by month, create time series, and build proper visualizations.

Merging and Appending

Merge join types:

• Left Outer: All rows from first table, matching rows from second
• Inner: Only rows that match in both tables
• Full Outer: All rows from both tables
• Left Anti: Rows from first table with NO match in second (great for finding orphans)

Custom Columns and Conditional Logic

Simple conditional:

if [Status] = "active" then "In Service" else "Out of Service"

Multiple conditions:

if [DaysSinceService] > 365 then "Overdue"
else if [DaysSinceService] > 180 then "Due Soon"
else "Current"

Date calculations:

Duration.Days(DateTime.LocalNow() - [LastServiceDate])

Text manipulation:

Text.Upper(Text.Trim([CustomerName]))

Parameters for Dynamic Queries

Parameters let you create flexible, reusable queries.

1. Home → Manage Parameters → New Parameter
2. Set name, type, and default value
3. Use in queries: = FilterDate (parameter name)

// Using a parameter in a query
let
    ServerName = ServerParameter, // References the parameter
    Source = Sql.Database(ServerName, "SalesDB"),
    ...

Custom Functions

For transformations you repeat across multiple queries, create custom functions.

// CleanPhoneNumber function
(phoneText as text) as text =>
let
    DigitsOnly = Text.Select(phoneText, {"0".."9"}),
    Formatted = if Text.Length(DigitsOnly) = 10 
                then Text.Format("(#{0}) #{1}-#{2}", 
                    {Text.Range(DigitsOnly,0,3), 
                     Text.Range(DigitsOnly,3,3), 
                     Text.Range(DigitsOnly,6,4)})
                else DigitsOnly
in
    Formatted

Invoke in any query:

= Table.AddColumn(Source, "CleanPhone", each CleanPhoneNumber([RawPhone]))

Let me walk through the actual data integration project I completed during the Toyota Lift / acquired company merger.

The Challenge

When Toyota Lift Northeast acquired a regional equipment dealer, we inherited:

• Legacy asset database: Different field names, different ID formats, incomplete data
• Target system (Toyota Lift): Standardized fields, comprehensive service history
• Business requirement: Single unified view of all assets for reporting

The two systems had grown independently for years. Field mappings weren't documented. Some assets existed in both systems with different IDs.

The Approach

I built a Power Query solution with three main queries:

Source: PostgreSQL connection to legacy database

Transformations:
- Renamed columns to match target schema
- Converted legacy date format (MM/DD/YYYY) to standard
- Mapped legacy status codes to target status values
- Created composite key from legacy fields for matching
- Flagged records with critical nulls

Source: SQL Server connection to Toyota Lift system

Transformations:
- Filtered to active assets only
- Standardized location codes
- Created matching key for merge

Merge: Full outer join on matching key
  - Matched records: Use target system as primary, legacy as supplement
  - Legacy-only records: Flag for review and migration
  - Target-only records: Carry through as-is

Additional transformations:
- Coalesce: Use target value if available, else legacy
- Calculate data completeness score per record
- Flag duplicates for manual review

Validation Queries

Alongside the main queries, I created validation outputs:

These fed a Data Quality dashboard that stakeholders reviewed weekly during the migration.

Results

Lessons Learned

I use all three regularly. Here's my decision framework.

When I Choose Power Query

• Multiple source types: Combining database + Excel + CSV in one workflow
• Power BI destination: Direct integration, scheduled refresh
• Non-technical stakeholders: Need to understand and validate logic
• Quick iteration: Prototyping transformations visually
• No database write access: Read-only data preparation

When I Choose SQL

• Large datasets: Millions of rows where performance matters
• Single database source: All data in one place
• Shared transformation logic: Multiple reports need same cleaned data
• Complex joins and aggregations: SQL syntax is more expressive
• Database write operations: Creating tables, updating records

When I Choose Python

• Statistical analysis: Beyond simple aggregations
• Machine learning prep: Feature engineering, model input
• Custom algorithms: Logic that doesn't fit visual tools
• Version control needed: Git-based collaboration
• Automation: Scheduled scripts, CI/CD pipelines

Combining Approaches

Often, I use multiple tools together:

At UMBC, I used:

• PostgreSQL (SQL): Store incident data, run complex queries
• Power Query: Prepare data for Power BI dashboards
• Python (Pandas): Exploratory analysis and pattern detection

The tools complement each other. Choose based on the task, not tribal loyalty.

After years of Power Query work, these are the mistakes I see most often.

Mistake: Over-Fetching Data

// Instead of filtering after loading
Source = Sql.Database("server", "database"),
Table = Source{[Schema="dbo",Item="HugeTable"]}[Data],
Filtered = Table.SelectRows(Table, each [Year] = 2025)

// Do this — filter in the SQL
Source = Sql.Database("server", "database", 
    [Query="SELECT * FROM HugeTable WHERE Year = 2025"])

Mistake: Breaking Query Folding

• Custom functions with complex logic
• Table.Buffer (forces full load)
• Some text transformations

1. Database filters and simple transformations first (these fold)
2. Complex M logic after (runs locally on smaller dataset)
3. Check "View Native Query" to verify folding

Mistake: Transformation Spaghetti

• Raw_SourceA: Connect and do minimal cleaning
• Clean_SourceA: Apply business transformations
• Merged_Data: Combine clean sources
• Final_Output: Add calculated columns, final formatting

Reference earlier queries in later ones. If something breaks, you know exactly where.

Mistake: Ignoring Nulls

• Math on null returns null (quietly)
• String functions on null often error
• Joins on null don't match (null ≠ null in joins)

• Replace with defaults before operations
• Use null-safe functions: if [Field] = null then 0 else [Field]
• Filter out nulls if they're invalid for your use case

Mistake: Hardcoded File Paths

1. Create a parameter: DataFolderPath
2. Reference in query: = Excel.Workbook(File.Contents(DataFolderPath & "data.xlsx"))
3. Update parameter when deploying to another machine or server