The Easy Guide To AI In Power Query

There was a particular month-end a few years back where I genuinely questioned whether the universe was sending me a sign to leave corporate finance and become a barista.

I opened the expense export from our ERP and for reasons known only to whatever gremlin lived inside that system, the file showed up with new column names, merged cells, random blanks, and dates formatted like it had just returned from studying abroad.

Naturally, this was at 6:42 PM. On a Friday. Before a long weekend.

I sat there thinking, “Cool, so I’m about to spend the next three hours manually un-chaosing this data because a system update decided to express its creativity.” And that was the moment I snapped. In the “I refuse to burn another evening doing caveman data prep” kind of way.

That’s when I leaned on the combo that has basically become my secret weapon ever since:

Power Query for the grunt work. AI in Power Query for the brain work.

Power Query handles the cleaning, fixing, shaping, and refreshing, and is available in both Microsoft Excel and Power BI, two core platforms for business intelligence. These tools are central to how modern finance teams transform and analyze data.

AI handles the “wait, how do I fix this?” moments without me spiraling into documentation hell.

And when you put the two together, month-end suddenly stops feeling like a crime against your sanity. The reality is that artificial intelligence is now an integral part of business processes, making data transformation and insights generation more practical and accessible than ever.

In this guide, I’m going to walk you through exactly how to pair Power Query with tools like ChatGPT or Copilot. No coding, no APIs, no developer vibes, just the real-world workflow I use to cut hours out of reporting, clean up messy data dumps, and survive the chaos that finance throws at us every month.

By the end, you’ll know how to:

• Turn recurring manual cleanup into one-click refreshes with little effort
• Use AI as your Power Query mentor—on demand
• Automate sticky parts of your workflow without breaking anything
• Build reporting pipelines that actually behave next month

Power Query: The Unsung Hero of Every Finance Team

If Excel is the trusty old sedan we’ve all been driving since college, Power Query is the quiet hybrid engine hiding under the hood that suddenly makes the whole thing feel… way less crappy.

Power Query is built-in software within Microsoft Excel and Power BI, so you don’t need to install any additional software to access its features.

Most finance pros have seen Power Query, maybe even clicked into it once, stared at all the buttons, and immediately hit “Cancel” like they’d just walked into the wrong bathroom. I get it. It looks intense.

But here’s the truth nobody tells you: Power Query isn’t a coding tool. It’s a “stop doing repetitive data prep” tool.And once you get the hang of it, it becomes the backbone of every reporting workflow you actually want to maintain long-term.

Let me break it down in normal human language.

What Power Query Actually Does (Without the Nerd Gloss)

Power Query is the thing in Excel and Power BI that:

• Takes in your messy CSVs
• Fixes all the formatting nonsense
• Standardizes the columns
• Removes duplicates
• Deals with typos, random blanks, weird date formats
• Combines multiple files into one
• And then—with almost smug confidence—does the exact same thing next month with one click

Power Query is available in both Microsoft Excel and Power BI. While the core functionality is similar, some advanced features and data connectors may differ between Microsoft Excel and Power BI, and licensing requirements can vary depending on the platform.

It’s like hiring an intern who actually listens, never forgets instructions, and doesn’t quit during busy season.

Why Power Query Is Perfect for Finance Chaos

Finance data is… messy. Different systems. Different exports. Different month-end quirks. Different people downloading the same report four different ways.

Power Query is especially useful for handling large datasets that are common in finance, making data preparation more manageable and efficient.

Power Query handles the ugly stuff we shouldn’t be doing manually anyway:

• Recurring Cleanup

That headcount file where the termination dates sometimes come through as “01/01/1900”?

Power Query can fix that forever. Recurring cleanup like this is a data transformation task that Power Query can automate efficiently, saving you time and reducing manual effort.

• Standardizing Across Entities

Subsidiary A uses “Dept,” subsidiary B uses “Department,” subsidiary C uses “DEPT_NM”—Power Query can unify all of it. By standardizing values such as department names across different entities, Power Query ensures consistency in your data, making it easier to analyze and generate accurate reports.

• Combining Multiple Files

If you’ve ever stacked 12 months of CSVs manually, I’m genuinely sorry. Power Query turns that into a drag-and-drop refresh. When working with large numbers of files, query folding can further optimize performance and efficiency by pushing data transformation steps back to the source, making the data loading process much faster.

• Data Types That Stay Correct

No more “why is this number suddenly text?”

Power Query locks types in place. In detail, this feature ensures that once you set a data type for a column, Power Query consistently enforces it throughout your workflow, preventing accidental changes that could lead to errors and helping maintain data integrity.

• One-Click Refresh (The Best Part)

Every month after you build a query, your job is literally:

1. Drop in the new files
2. Click Refresh
3. Go get coffee because the robot handled everything

You can also enable scheduled refreshes or automation features to make the process even more hands-off.

The 80/20 Power Query Skills Every Finance Pro Needs

You don’t need to master M code or become a data engineer.
You just need these basics:

• Get Data (bring files into PQ)
• Remove Columns / Keep Columns
• Split Columns
• Change Data Types
• Append Queries (stack files)
• Merge Queries (VLOOKUP but stronger and less emotionally damaging)
• Group By (summaries)
• Fill Down / Fill Up
• Filter rows
• Close & Load

That’s it.
If you can use these, you can automate 70–80% of the manual cleaning you’re doing now.

The Mindset Shift That Makes Power Query Click

Most people treat Excel like a scratch pad. Power Query forces you to think like a chef:

• You prep once
• You create a recipe
• You follow the same recipe every month
• You never start from scratch again

Yes, it takes a little setup. But if you spend 30 minutes building a Power Query flow that saves you 3 hours every month… that’s a 600% return on time.

To truly maximize your efficiency, implement these Power Query workflows in your finance processes and experience the benefits firsthand.

AI: Your Personal Power Query Coach

Here’s the part nobody warned me about when I first started using Power Query: You will get stuck.Not every day. Not every step. But you will hit something that makes you stare at your screen like, “Why… why is it doing that?”

Maybe you try to merge two tables and Power Query decides the matching keys are “similar but not similar enough.” Maybe it converts all your dates to text because it’s feeling chaotic. Maybe it throws an error that sounds like it was written by a frustrated poet.

And this is where AI becomes your secret weapon—not as some fancy API, not as a developer tool, but as the world’s most patient Power Query tutor. AI Insights, a feature in Power Query and Power BI, can provide helpful explanations and guidance, making it easier to understand and resolve issues as you work.

AI Is Here To Do The Coding

The magic of pairing AI with Power Query is this:

Power Query does the work. AI explains the work.

AI is the friendly coworker who says:

“Yeah, that error is stupid. Here’s what it means and exactly what to click to fix it.”

AI helps clarify the meaning behind confusing Power Query messages, making it easier to interpret errors and understand what’s really going on.

We’re not asking AI to write M code from scratch. We’re using it to get clarity, shortcuts, explanations, and step-by-step fixes in plain English, so you don’t spend half your afternoon trying to decode the Power Query Editor like it’s ancient hieroglyphics.

What AI Can Help You With (No Tech Required)

1. Understanding What Power Query Is Actually Doing

You can literally paste the list of Applied Steps into ChatGPT and say:
“Explain what this query is doing like I’m five spreadsheets deep into month-end.”
And it will.

2. Figuring Out the Right Transformation

Ever stare at a dataset and think, “I know what I want to do… but I have no idea what this is called in Power Query”? AI solves this instantly. It can also recommend the most effective data transformation techniques for your specific scenario, helping you clean, restructure, and analyze your data efficiently.

Prompts that work:

• “I need to group expenses by vendor but ignore capitalization—how do I do that in Power Query?”
• “I want to stack 12 CSV files but one has an extra column. How should I handle that?”

3. Troubleshooting Errors Without Losing Your Mind

Power Query errors are notorious for sounding like ransom notes.

You can copy/paste an error message into ChatGPT and it’ll translate it into human-speak + give the fix. The AI provides a clear response to each error message, offering an actionable solution based on the specific issue.

4. Turning Your Manual Process Into a Repeatable Workflow

If you describe the way you usually clean a file manually, AI can turn that description into a Power Query recipe:

“Step 1: Remove rows where Column X is blank. Step 2: Split Column Y by space. Step 3: Convert Column Z to date.”

It’s easy to implement the AI-generated workflow directly in Power Query, allowing you to quickly apply these steps and enhance your data process.

Boom—instant SOP.

5. Helping You Learn Without Reading a 40-Page Guide

Look… Microsoft documentation is great. It’s also written for people who drink black coffee and genuinely enjoy JSON.

AI gives you the “real human explanation” immediately, making it easier for users of all skill levels to learn Power Query without having to read lengthy guides.

AI Takes the Fear Out of Power Query

Power Query is one of the best tools in the finance world… once you understand how to speak its language.

AI is your translator.

It reduces the learning curve from “this looks like a crime scene” to “oh, that’s it?” It speeds up troubleshooting. It removes uncertainty. It makes you way more confident building workflows that don’t explode next month. AI also helps you focus on the most relevant data and transformations, ensuring your workflow is both efficient and accurate.

But more importantly?

AI makes you dangerous.Because once you know the why behind a transformation, you can adapt it to any dataset your company throws at you.

How Power Query + AI Work Together

Let me paint the actual workflow, because it’s not some futuristic “AI integrates seamlessly into your data pipeline” fantasy.
It’s much more human.
Much more chaotic.
And way more relatable.

However, AI systems are now an integral part of modern data workflows, making processes more efficient and reliable.

Here’s how it actually goes down on a random Tuesday at 3:17 PM when month-end is getting spicy:

Step 1 — You Import the Data Into Power Query

You’ve got five CSVs, two Excel files, and one export that looks like it was assembled by a sleep-deprived raccoon.

You pull everything into Power Query. Here, you can connect to a variety of data sources, including Excel, CSV, and databases, making it easy to bring all your data together.

You feel hopeful. You remove a few columns, fix a few dates, merge a few queries.

Things are going great.

Step 2 — Something Breaks (It Always Does)

Maybe one file has an extra column. Maybe someone renamed “Vendor Name” to “Name of Vendor.” Maybe Power Query decides a perfectly good date is actually text.

Note: Some errors may require specific troubleshooting steps or knowledge of Power Query’s quirks to resolve.

Whatever it is, your calm ends instantly.

This is where most people either:

• Start randomly clicking buttons
• Re-import everything
• Or scream into the void

But not you. Because you’ve got AI on standby.

Step 3 — You Pull Up ChatGPT/Copilot and Explain the Problem Like You’re Talking to a Friend

You don’t need to be precise. You don’t need jargon. You don’t need “M language.”

You literally type:

“Hey, Power Query is suddenly saying this date column is text. It wasn’t doing that earlier. What’s happening?”

Or:

“I’m trying to append multiple CSVs but one of them has an extra column. What’s the cleanest way to handle that?”

Or:

“Power Query error: Expression.Error: We cannot convert the value ‘1/2/25’ to type Number. Translate this into English and tell me how to fix it.”

AI will analyze your description and suggest the most effective solution or next step, just like an expert colleague would.

And AI answers like the colleague you wish you had sitting next to you.

Step 4 — AI Translates the Nonsense Into Clear Directions

AI doesn’t just tell you what’s wrong.It tells you what to do next.

Things like:

• “Change the column type before merging.”
• “Use ‘Choose Columns’ to standardize structure before appending.”
• “Your system exported dates inconsistently—apply ‘Using Locale’ to force date format.”
• “Add a Fill Down to handle those multi-level headers.”

You get the exact steps, in the correct order, without guessing.
AI can also help you optimize your Power Query workflow for better performance, suggesting ways to streamline steps and handle data more efficiently.

This is the part where you start feeling oddly powerful.

Step 5 — You Go Back Into Power Query and Fix It in 60 Seconds

You follow the instructions. Your error disappears. Your table refreshes. You feel like the chosen one.

With little effort, you can implement these fixes and see immediate results.

Power Query did the grunt work. AI did the thinking.

That’s the combo.

Step 6 — The Workflow Clicks Into Place

Now that you’ve survived one crisis, the loop becomes natural:

1. PQ imports data
2. You make some transformations
3. Something confusing pops up
4. AI explains it instantly
5. You fix it confidently
6. Everything refreshes smoothly next month

At any point in your Power Query workflow, you can easily access AI-powered assistance to help clarify issues or guide your next steps.

It becomes muscle memory.

This, Right Here, Is the Real Automation Superpower

It’s not about AI writing scripts. It’s not about APIs or custom connectors. It’s not about becoming a developer.

It’s this:

Power Query makes your data clean and repeatable.AI makes you smarter and faster.

AI models are the engine behind these smart automation features in Power Query, enabling advanced analysis and decision-making without requiring deep data science expertise.

Step-By-Step Walkthrough: Cleaning a Finance Dataset Using Power Query + AI

Alright, time to roll up our sleeves and actually do this.

Let’s walk through a real scenario you and I have lived through more times than we’d like to admit. This walkthrough will provide detail on each step of the process so you can follow along confidently.

Picture this: It’s month-end. You’ve got expense dumps from five different systems, each one believing it is the center of the universe and therefore free to format data however it pleases.

Your mission? Turn this circus into one clean, trustworthy table that refreshes in seconds next month.

Here’s exactly how I do it using Power Query for the muscle and AI for the brain—no coding, no APIs, no tech heroics.

Scenario: The “Five-Exports-From-Hell” Expense Report

You’re given a folder full of files like:

• “Expenses_EntityA_Final_v4.xlsx”
• “ExpenseDump(2).csv”
• “Copy of ExpenseRaw_Jan_System4.xlsx”
• And of course, the mysterious “FINAL_FINAL_THIS_ONE.xlsx”

Each one has:

• Different column names
• Different date formats
• Random blank rows
• Duplicate transactions
• Free-text descriptions ranging from “Uber” to “UBR-LA-01-Acc” to “I took an Uber but forgot to categorize it”

Let’s tame this thing. The end goal is to transform all this messy data into clean, reliable reports that provide accurate insights and support decision-making.

Step 1 — Pull Everything Into Power Query

1. Open Excel → Data Tab → Get Data → From File

Choose “From Folder” if you want to future-proof the process.
Put all monthly files in the same folder and PQ will automatically read all of them next month.
Alternatively, you can also load files into the Power BI Service for cloud-based analysis and collaboration, enabling advanced analytics and sharing within the Power BI cloud environment.

2. Preview Shows All Files → Click “Combine & Load”

Power Query will show you a sample. This is your chance to trash any garbage columns or unnecessary headers.

After you combine and load your files, you can apply pre-built models—such as those integrated with Azure Cognitive Services—for advanced analysis like text analysis, language detection, or image tagging on your combined data.

3. Convert to a Table Inside Power Query

You’ll land in the Power Query Editor, your new command center. From here, you can also access AI Insights, which provides advanced data analysis and guidance directly within the Power Query Editor.

Here’s where the real work begins.

Step 2 — Fix the Inconsistencies (AKA Make the Data Behave)

Let’s clean this thing properly. These steps are part of the data transformation process in Power Query, ensuring your data is structured and ready for analysis.

1. Standardize Column Names

Even if one file says “Vendor,” another says “Vend,” another says “Supplier,” you can unify everything inside PQ. First, identify which columns need to be standardized for consistency across your datasets.

• Right-click → Rename
• Repeat across all needed columns
• Now they’ll append cleanly

2. Remove Random Blank Rows

Filter out nulls or use “Remove Blank Rows.” Removing blank rows is a common data cleaning task in Power Query, helping to ensure your data is ready for further analysis.

3. Fix Data Types

Dates especially love to gaslight you.

• Click the little icon next to the column header
• Choose Date
• If you get weird results, change type → Using Locale → Pick correct region

For more detail, if you encounter tricky cases like columns with mixed date formats or regional differences (e.g., US vs. UK date order), use the “Using Locale” option to specify the exact format and region, ensuring Power Query interprets your data correctly.

4. Remove Duplicates

Home → Remove Rows → Remove Duplicates
(Your auditors will thank you.)

When you remove duplicates, you ensure that only unique values remain in your dataset. This helps maintain data integrity and accuracy for further analysis.

5. Clean Up Descriptions

Trim whitespace
Clean errors
Remove non-printable characters
Normalize weird punctuation

Power Query crushes all the mechanical cleanup.

Cleaning up descriptions also helps ensure only relevant information is retained in your dataset.

But… something always comes up that needs a little extra thinking.

And that’s…

Step 3 – “Phone a Friend”

You’ve got data that looks mostly clean, but then something weird pops up.

With AI tools, you can use natural language to ask for help with Power Query problems—just describe your issue in plain English and get step-by-step guidance.

Here are real questions I’ve thrown at ChatGPT/Copilot mid-workflow:

Problem Example 1:

“My dates are randomly flipping between text and datetime. Why?”

AI explains: Your files have different regional formats. Use “Change Type Using Locale.”

Note: Regional date formats can cause unexpected issues in Power Query, so always check your locale settings to avoid errors.

Boom—problem solved.

Problem Example 2:

“I want to group expenses by vendor but also catch misspellings like ‘Starbucks,’ ‘Starbcks,’ and ‘Starbux.’ What’s the best approach?”

AI offers 2–3 clean strategies:

• Use “Text.Lower” → then group
• Use “Trim” to normalize
• If needed, create a mapping table
• Or use fuzzy grouping (yep, PQ can do that)
• You can also leverage key phrase extraction to categorize vendor descriptions more accurately, especially when dealing with varied or unstructured text.

You pick what fits your environment. No guessing. No YouTube rabbit hole.

Problem Example 3:

“One CSV has an extra column and it’s breaking my append. What’s the cleanest fix?”

AI answers instantly:
Use “Choose Columns” or “Select Columns” to force all files to have identical structures before appending.

Done and done.

Problem Example 4:

“This column needs to be split based on the last dash only. How do I do that?”

AI tells you the exact menu path in PQ.

For more detail, you can use the “Split Column by Delimiter” feature in Power Query, select “Custom” as the delimiter, enter a dash (-), and choose “Right-most delimiter” to split based on the last dash specifically.

You just follow the clicks.

Step 4 — Add Final Touches

Once the big cleanup is done:

• Add any calculated columns. Calculated columns are a powerful feature that lets you customize your dataset for deeper analysis.
• Remove junk you don’t need
• Sort, group, or filter
• Check for outliers (AI can help tell you what to look for)
• Rename everything clearly

This is the part where the dataset goes from “presentable” to “actually useful.”

Step 5 — Load It Back Into Excel or Power BI

Choose your destination:

• Excel Table → for ad hoc reporting. You can load cleaned data directly into Microsoft Excel for further analysis.
• Data Model → for dashboards
• Power BI → for automated visuals and scheduled refresh

Click Close & Load and watch it populate.

Step 6 — Next Month: Refresh and Flex

Here’s why you did all this:

Next month…

1. Drop new files in the folder
2. Click Refresh
3. Watch PQ do everything
4. Spend the time you saved doing something useful—like building better analysis or going home on time for once in your career

You can also enable scheduled refreshes to automate the process even further, so your data updates without manual intervention.

Case Studies That Prove This Combo Actually Saves Hours

Let’s get out of theory and into the trenches.

These are real-world finance scenarios where pairing Power Query with AI didn’t just make life easier—it flat-out changed the workflow. The following case studies will provide detail on how Power Query and AI are used in practice.

Nothing fancy. Nothing technical. Just practical wins that any finance pro could replicate.

Case Study 1 — Multi-Entity P&L Consolidation (The 6-Hour Monster Shrunk to 15 Minutes)

The Situation

I was working with a team that had 10 subsidiaries, each with their own version of a P&L export:

• Different GL layouts
• Different naming conventions
• Different date formats
• And—my personal favorite—one entity that used tabs as delimiters because… why not?

Every month, one poor analyst spent half his day stitching these files together so the CFO could see consolidated results.

The Power Query Fix

We built a simple “From Folder” Power Query flow:

• Standardize column names
• Transform all dates to a single format
• Remove junk rows
• Append all entities into one clean table
• Add a column for Entity Name
• Close & Load

Total build time: ~45 minutes.

Where AI Stepped In

Every time we ran into something weird, like:

• “Entity 7 has a random merged header, what do I do?”
• “This file has two rows of headers—how do I delete both?”
• “Why is this measure blowing up after the append?”

ChatGPT explained the fix in seconds.
Menu paths, step-by-step. No Googling. No documentation hunts.

The Result

📉 Manual work: 6 hours → 15 minutes
📈 Team confidence: way higher
🚀 CFO reaction: “Wait… you already finished this?”

PQ did the grunt work.
AI filled in the gaps.

Case Study 2 — Cleaning & Categorizing 30,000+ Expense Lines

The Situation

A company was trying to automate their monthly expense analysis.
The raw data came from four systems and contained:

• Ugly vendor names (“Uber,” “UBR,” “UBER US,” etc.)
• Messy descriptions
• Duplicates
• Random errors
• Dozens of one-off misspellings

They wanted clean categories and consistent vendor groupings.

The Power Query Fix

We used PQ to:

• Clean descriptions. Text analytics can also be used to categorize vendor names and descriptions for more accurate analysis.
• Standardize vendor names
• Convert everything to lowercase
• Remove duplicates
• Split long text strings into usable chunks

Where AI Stepped In

Instead of guessing how to categorize 30k entries, we asked AI:

“What are the best practices for mapping messy vendor names to consistent categories?”

It gave us:

• A rule-based approach
• A fallback table
• A fuzzy match option (yep, PQ has it)
• Clear steps for each method

We implemented a simple mapping table + fuzzy matching. Additionally, sentiment analysis can be used to evaluate the tone of expense descriptions, providing further insights into spending patterns and potential issues.

The Result

📉 Categorization labor: Down ~80%
📈 Accuracy: More consistent than manual
⏱ Future refresh time: Seconds, not hours

And the team didn’t have to learn a single line of M code.

Case Study 3 — Forecast Review with Automated Data Validation

The Situation

FP&A had a messy forecasting process:

• Business partners dumped numbers in random formats
• Columns were inconsistent
• One manager’s idea of “forecasting” was dragging formulas diagonally and hoping for the best
• Data quality checks were happening after slides were already built (ouch)

The Power Query Fix

We created a clean forecast ingestion pipeline:

• Standardize formats
• Fix column types
• Flag missing inputs
• Unpivot data into a usable shape
• Load into Excel and Power BI
• Implement automated validation steps to catch errors early

Now they had one clean version of inputs.

Where AI Stepped In

The team asked AI:

• “What are the quickest ways to detect outliers in forecast data?”
• “How should I structure my Group By to see YoY deltas?”
• “How do I build a check that flags negative revenue?”

AI gave:

• Clear logic patterns
• Step-by-step transformations
• Visual examples of checks to run
• Ideas for automatic validation columns

Additionally, AI models can be used to detect outliers and flag issues in forecast data, providing automated and scalable validation without requiring extensive data science expertise.

The Result

📉 Review time: Cut in half📈 Errors caught earlier 💡 Insights improved (because now they could actually see the data)

With improved data quality, the team produced more accurate and insightful reports, making it easier to identify trends and drive better decisions.

The analysts went from data janitors to actual business partners.

Case Study 4 — The “Oh No, The ERP Changed Again” Incident

The Situation

One company’s ERP pushed a “minor update” (lol) that changed:

• Column names
• Header rows
• Date formatting
• And added three blank lines to every export

Predictably, month-end exploded.

The Power Query Fix

The query broke… but only for a minute.

With AI:

“My column used to be named ‘Invoice_Date’ and now it’s ‘Inv Date.’
What’s the fastest way to update my Power Query steps without rebuilding everything?”

AI responded:

• “Open Applied Steps
• Rename columns before your type-changing steps
• Adjust the ‘Changed Type’ step accordingly
• Keep everything else the same”

Two minutes later, everything refreshed again.

The Result

📉 Time lost: 5 minutes
📈 Stress saved: immeasurable
🧠 Lesson: PQ + AI = resilience to system chaos

Best Practices For AI In Power Query

Here’s the part of the article where I put my seasoned-finance-leader hat on and say: “Yes, this combo is powerful… but please don’t go full ‘move fast and break things’ in Power Query. Month-end is not the place for chaos.”

The following best practices provide detail on building robust Power Query workflows, helping you avoid common pitfalls and ensure reliability.

Let me give you the playbook I wish someone had handed me before I built my first PQ workflow and immediately broke it trying to be clever.

1. Name Your Steps Like a Sane Person

If your Applied Steps pane looks like:

• Renamed Columns1
• Renamed Columns2
• Renamed Columns3

…you’re living dangerously.

Clear step naming is a valuable feature for maintaining and understanding your workflows over time.

Give yourself names you’ll understand next month. Future You deserves that mercy.

2. Keep Transformations Modular (Small Steps = Easy Fixes)

Big, complex steps = pain.
Tiny, clear steps = fast troubleshooting.

Breaking transformations into small, modular steps helps optimize troubleshooting and maintenance, making it easier to identify and fix issues quickly.

Think of your steps like ingredients in a recipe. If something tastes wrong, you want to know which ingredient ruined the stew… not guess between all 47 at once.

3. Always Keep a Copy of the Raw Data

Save yourself from the day someone says, “Are you sure that number is right?”

Always. Keep. A. Raw. Backup.

If your company allows it, store last month’s raw files in a folder the robots can’t touch. This ensures you always have access to the original data for verification whenever needed.

4. Use AI to Document Your Workflow

This is the biggest hack of all.

Copy/paste your Applied Steps into ChatGPT and ask:

“Explain what this query does in clear English so I can add it to my SOP.”

Boom—instant documentation.

Great for audits.
Great for handoffs.
Great when a teammate asks “how does this work?” and you don’t feel like explaining.

5. Use Parameters for Anything That Changes Monthly

If your file path is buried inside a step like /January/Expenses… you’re just begging for failure.

Parameters let you update a single value each month without breaking 20 steps downstream.

AI can explain how to set them up in 30 seconds.

6. When Something Looks Weird… Stop and Ask AI First

The fastest way to break a working query is by randomly clicking buttons until the error disappears.

A better approach:

“This step is doing something strange—explain what’s happening before I touch it.”

Let AI translate the chaos before you go poking around. AI Insights in Power Query can also provide explanations for confusing steps, helping you understand data transformations before making changes.

7. Build With Next Month In Mind

This is the golden rule.

Before you add a step, ask:

“Will this still work with next month’s file? Or am I fixing a one-off issue I’ll regret later?”

If it’s a one-off, fix the data before ingestion—not inside Power Query.

To ensure your workflows remain robust over time, implement these best practices as you build for the future.