Using AI to Generate SQL, DAX, and M Code

You're staring at a blank query window in Power BI, trying to figure out how to calculate year-over-year growth by product category. You know what you want to accomplish, but translating that business logic into DAX feels like learning a foreign language. Meanwhile, your colleague just asked for a complex SQL query to analyze customer churn, and you're not even sure where to start with the joins.

Sound familiar? You're not alone. Data professionals spend countless hours wrestling with syntax, debugging code, and trying to remember the exact function names across different platforms. But here's the game-changer: AI can now write much of this code for you, often better and faster than you could manually.

By the end of this lesson, you'll understand how to harness AI as your coding assistant for the three most important query languages in modern data work: SQL for database queries, DAX for Power BI calculations, and M (Power Query) for data transformation. You'll learn not just how to generate code, but how to refine it, understand it, and adapt it to your specific needs.

What you'll learn:

• How to craft effective prompts that generate accurate SQL, DAX, and M code
• The strengths and limitations of AI code generation for each language
• Techniques for iterating and refining AI-generated code
• How to verify and test AI-generated queries before deploying them
• Best practices for incorporating AI assistance into your data workflow

Prerequisites

To get the most from this lesson, you should have:

• Basic understanding of what databases, data models, and data transformation are (you don't need to be an expert)
• Access to an AI tool like ChatGPT, Claude, or Copilot (free versions work fine)
• Familiarity with at least one data tool like Excel, Power BI, or SQL Server (helpful but not required)

Understanding AI's Role in Code Generation

Before diving into specific languages, let's establish what AI can and cannot do for you as a code generator.

AI excels at pattern recognition and has been trained on millions of lines of SQL, DAX, and M code from documentation, forums, and repositories. This means it can:

• Generate syntactically correct code from natural language descriptions
• Suggest multiple approaches to solve the same problem
• Explain existing code line by line
• Debug common errors and suggest fixes
• Adapt code patterns to different scenarios

However, AI has important limitations:

• It doesn't know your specific data structure unless you tell it
• It can't validate that the logic matches your exact business requirements
• It may suggest overly complex solutions when simpler ones exist
• It can generate code that runs but produces incorrect results

Think of AI as an extremely knowledgeable junior developer who knows syntax perfectly but needs clear direction and supervision.

Crafting Effective Prompts for Code Generation

The quality of AI-generated code depends heavily on how you describe what you need. Here's a framework that works across all three languages:

Context + Goal + Constraints + Format

Let's break this down:

Context

Describe your data structure and scenario. Instead of saying "I have sales data," say: "I have a sales table with columns: OrderID, CustomerID, ProductID, OrderDate, Quantity, UnitPrice. I also have a Products table with ProductID, ProductName, Category, and a Customers table with CustomerID, CustomerName, Region."

Goal

Be specific about what you want to achieve:

• Good: "Calculate total sales by region for the current year"
• Better: "Calculate total sales revenue (Quantity × UnitPrice) grouped by customer region, filtered to show only orders from 2024"

Constraints

Mention any limitations or requirements:

• "Use only standard SQL functions (no window functions)"
• "The result should work in Power BI Desktop"
• "Handle null values by treating them as zero"

Format

Specify how you want the output:

• "Return the code with comments explaining each step"
• "Include sample data to test the query"
• "Provide both the measure formula and where to place it in Power BI"

Generating SQL Code with AI

SQL is where AI shines brightest because SQL syntax is highly standardized and well-documented. Let's walk through practical examples.

Basic Query Generation

Here's how to prompt for a straightforward SQL query:

Prompt: "I have an Orders table with columns OrderID, CustomerID, OrderDate, TotalAmount, and Status. Generate SQL to find the top 5 customers by total purchase amount in 2024, including their customer ID and total spent."

AI Response:

SELECT 
    CustomerID,
    SUM(TotalAmount) AS TotalSpent
FROM Orders
WHERE YEAR(OrderDate) = 2024
    AND Status = 'Completed'
GROUP BY CustomerID
ORDER BY TotalSpent DESC
LIMIT 5;

Notice how the AI made reasonable assumptions (filtering by completed orders, using YEAR function) but you might need to adjust these based on your specific needs.

Complex Multi-Table Queries

For more complex scenarios, provide detailed table structures:

Prompt: "I have three tables:

• Orders: OrderID, CustomerID, OrderDate, TotalAmount
• OrderItems: OrderID, ProductID, Quantity, UnitPrice
• Products: ProductID, ProductName, Category, Supplier

Generate SQL to find which product categories had the highest average order value in Q1 2024, showing category name and average order value."

AI Response:

SELECT 
    p.Category,
    AVG(oi.Quantity * oi.UnitPrice) AS AvgOrderValue,
    COUNT(DISTINCT o.OrderID) AS OrderCount
FROM Orders o
INNER JOIN OrderItems oi ON o.OrderID = oi.OrderID
INNER JOIN Products p ON oi.ProductID = p.ProductID
WHERE o.OrderDate >= '2024-01-01' 
    AND o.OrderDate <= '2024-03-31'
GROUP BY p.Category
ORDER BY AvgOrderValue DESC;

Refining SQL Results

If the first attempt isn't quite right, iterate with specific feedback:

Follow-up: "Modify the query to show only categories with at least 10 orders, and format the average as currency with 2 decimal places."

SELECT 
    p.Category,
    CONCAT('$', FORMAT(AVG(oi.Quantity * oi.UnitPrice), 2)) AS AvgOrderValue,
    COUNT(DISTINCT o.OrderID) AS OrderCount
FROM Orders o
INNER JOIN OrderItems oi ON o.OrderID = oi.OrderID
INNER JOIN Products p ON oi.ProductID = p.ProductID
WHERE o.OrderDate >= '2024-01-01' 
    AND o.OrderDate <= '2024-03-31'
GROUP BY p.Category
HAVING COUNT(DISTINCT o.OrderID) >= 10
ORDER BY AVG(oi.Quantity * oi.UnitPrice) DESC;

Tip: When working with SQL, always specify your database system (SQL Server, MySQL, PostgreSQL) as syntax can vary for functions like date handling and formatting.

Generating DAX Code for Power BI

DAX (Data Analysis Expressions) is Microsoft's formula language for Power BI, and it's where AI can save you the most time. DAX has unique concepts like filter context and row context that can be tricky to master.

Creating Basic Measures

Prompt: "I have a Sales table with columns Date, CustomerID, ProductID, Quantity, UnitPrice. Create a DAX measure to calculate total revenue for the current year compared to the previous year."

AI Response:

Total Revenue Current Year = 
CALCULATE(
    SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]),
    YEAR(Sales[Date]) = YEAR(TODAY())
)

Total Revenue Previous Year = 
CALCULATE(
    SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]),
    YEAR(Sales[Date]) = YEAR(TODAY()) - 1
)

Revenue Growth % = 
DIVIDE(
    [Total Revenue Current Year] - [Total Revenue Previous Year],
    [Total Revenue Previous Year],
    0
) * 100

Advanced DAX Patterns

For more sophisticated calculations, provide business context:

Prompt: "I need a DAX measure that calculates running total sales by month, but resets at the beginning of each year. My date table is called 'Calendar' with a Date column, and my Sales table has Date, Amount columns."

AI Response:

Running Total Sales YTD = 
VAR CurrentDate = MAX(Calendar[Date])
VAR CurrentYear = YEAR(CurrentDate)
VAR YearStart = DATE(CurrentYear, 1, 1)

RETURN
CALCULATE(
    SUM(Sales[Amount]),
    Calendar[Date] >= YearStart && Calendar[Date] <= CurrentDate,
    ALL(Calendar[Date])
)

Time Intelligence with DAX

DAX time intelligence is particularly complex, making it perfect for AI assistance:

Prompt: "Create a DAX measure that shows sales growth percentage compared to the same period last year, handling both month-over-month and year-over-year comparisons automatically based on the filter context."

AI Response:

Sales Growth % = 
VAR CurrentPeriodSales = SUM(Sales[Amount])
VAR PreviousYearSales = 
    CALCULATE(
        SUM(Sales[Amount]),
        SAMEPERIODLASTYEAR(Calendar[Date])
    )

RETURN
IF(
    NOT ISBLANK(PreviousYearSales),
    DIVIDE(
        CurrentPeriodSales - PreviousYearSales,
        PreviousYearSales,
        0
    ) * 100,
    BLANK()
)

Warning: DAX measures generated by AI often work but may not be optimized for performance. For large datasets, ask the AI to explain the performance implications and suggest optimizations.

Generating M Code for Power Query

M is the functional language behind Power Query in Power BI and Excel. It's used for data transformation and is often the most challenging of the three languages because of its functional programming nature.

Basic Data Transformation

Prompt: "I have a CSV file with columns: Date (in MM/DD/YYYY format), Product, Sales, Region. Generate M code to load this data, convert the Date column to proper date format, filter out rows where Sales is null or zero, and add a new column called 'Quarter' that shows Q1, Q2, etc. based on the date."

AI Response:

let
    Source = Csv.Document(File.Contents("C:\path\to\your\file.csv"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    ChangedTypes = Table.TransformColumnTypes(PromotedHeaders,{
        {"Date", type date}, 
        {"Product", type text}, 
        {"Sales", type number}, 
        {"Region", type text}
    }),
    FilteredRows = Table.SelectRows(ChangedTypes, each [Sales] <> null and [Sales] > 0),
    AddedQuarter = Table.AddColumn(FilteredRows, "Quarter", each 
        if Date.Month([Date]) <= 3 then "Q1"
        else if Date.Month([Date]) <= 6 then "Q2" 
        else if Date.Month([Date]) <= 9 then "Q3"
        else "Q4", type text)
in
    AddedQuarter

Complex Data Reshaping

M really shines in data transformation scenarios that would be difficult in SQL:

Prompt: "I have sales data where each row represents a month, and I have separate columns for Jan_Sales, Feb_Sales, Mar_Sales, etc. Generate M code to unpivot this data so I have one row per month per record, with columns for Date, Sales, and all the other original columns preserved."

AI Response:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Product", type text}, {"Region", type text}}),
    UnpivotedColumns = Table.UnpivotOtherColumns(ChangedType, {"ID", "Product", "Region"}, "Month", "Sales"),
    ParsedMonth = Table.TransformColumns(UnpivotedColumns, {
        {"Month", each 
            let 
                MonthText = Text.BeforeDelimiter(_, "_"),
                MonthNumber = 
                    if MonthText = "Jan" then 1
                    else if MonthText = "Feb" then 2
                    else if MonthText = "Mar" then 3
                    else if MonthText = "Apr" then 4
                    else if MonthText = "May" then 5
                    else if MonthText = "Jun" then 6
                    else if MonthText = "Jul" then 7
                    else if MonthText = "Aug" then 8
                    else if MonthText = "Sep" then 9
                    else if MonthText = "Oct" then 10
                    else if MonthText = "Nov" then 11
                    else 12
            in
                Date.FromText("2024-" & Text.PadStart(Text.From(MonthNumber), 2, "0") & "-01")
        }
    }),
    RenamedColumns = Table.RenameColumns(ParsedMonth,{{"Month", "Date"}}),
    ChangedTypes = Table.TransformColumnTypes(RenamedColumns,{{"Date", type date}, {"Sales", type number}})
in
    ChangedTypes

API Data Connection

M is also used for connecting to APIs and web services:

Prompt: "Generate M code to connect to a REST API that returns JSON data. The API endpoint is 'https://api.example.com/sales' and requires an API key in the header as 'X-API-Key'. Parse the JSON response and create a table from the 'data' array."

AI Response:

let
    ApiKey = "your-api-key-here",
    Headers = [#"X-API-Key" = ApiKey, #"Content-Type" = "application/json"],
    Source = Web.Contents("https://api.example.com/sales", [Headers=Headers]),
    JsonDocument = Json.Document(Source),
    DataList = JsonDocument[data],
    ConvertedTable = Table.FromList(DataList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedRecords = Table.ExpandRecordColumn(ConvertedTable, "Column1", 
        {"id", "date", "amount", "customer"}, 
        {"ID", "Date", "Amount", "Customer"}),
    ChangedTypes = Table.TransformColumnTypes(ExpandedRecords,{
        {"ID", type text}, 
        {"Date", type date}, 
        {"Amount", type number}, 
        {"Customer", type text}
    })
in
    ChangedTypes

Hands-On Exercise

Let's put these skills to practice with a realistic scenario. You work for a retail company and need to analyze customer purchasing patterns.

Scenario: You have three data sources:

1. A SQL database with customer orders
2. An Excel file with product categories that needs transformation
3. A Power BI model that needs calculated measures

Your Task: Use AI to generate code for each step:

Step 1: SQL Query

Prompt AI to generate SQL that finds customers who made purchases in both 2023 and 2024, showing their total spend in each year.

Try this prompt: "I have an Orders table with CustomerID, OrderDate (datetime), and TotalAmount (decimal). Generate SQL to find customers who purchased in both 2023 and 2024, showing CustomerID, total spent in 2023, total spent in 2024, and the percentage change year over year."

Step 2: M Code for Data Transformation

The product data comes in a messy Excel format where categories are in separate columns that need to be unpivoted.

Try this prompt: "Generate M code for Power Query to transform Excel data where I have columns: ProductID, ProductName, Category_Electronics, Category_Clothing, Category_Home with TRUE/FALSE values. I need to unpivot the categories so each product appears once per category it belongs to."

Step 3: DAX Measures

Create measures for customer segmentation based on purchase behavior.

Try this prompt: "Create DAX measures to segment customers as 'High Value' (>$1000 annual spend), 'Medium Value' ($200-$1000), or 'Low Value' (<$200). Include measures for count of customers in each segment and average order value per segment."

Take a few minutes to work through each prompt and examine the generated code. Notice how the AI adapts its approach based on the specific language and requirements.

Common Mistakes & Troubleshooting

Even with AI assistance, you'll encounter issues. Here are the most common problems and how to address them:

SQL Issues

Problem: AI generates code that works in one database but not another. Solution: Always specify your database system in your prompt. "Generate PostgreSQL-compatible SQL..." or "Create a SQL Server query..."

Problem: The query runs but returns unexpected results. Solution: Ask AI to add comments explaining the logic, then verify each step matches your business requirements.

DAX Issues

Problem: DAX measures show incorrect values when filters are applied. Solution: This usually indicates incorrect filter context. Ask AI to explain the filter context behavior and suggest alternatives using CALCULATE or ALL functions.

Problem: Performance issues with complex DAX measures. Solution: Prompt: "Optimize this DAX measure for better performance" and ask for explanations of why the optimized version is faster.

M Code Issues

Problem: M code fails with "Expression.Error" messages. Solution: M is sensitive to data types and null values. Ask AI to add error handling: "Modify this M code to handle null values and type conversion errors gracefully."

Problem: Transformation steps are in the wrong order. Solution: M executes steps sequentially. Ask AI to explain the step order and why each step depends on the previous ones.

General Debugging Strategy

1. Test with sample data first: Always test AI-generated code with a small dataset before applying it to production data.

2. Ask for explanations: Follow up with "Explain what each part of this code does" to understand the logic.

3. Iterate incrementally: If complex code doesn't work, break it down: "Simplify this to just the basic calculation, then we'll add complexity step by step."

4. Verify assumptions: AI makes assumptions about your data structure. Always double-check these match your actual data.

Best Practice: Keep a testing environment where you can safely run AI-generated code without affecting production systems or reports.

Optimizing Your AI Workflow

As you become more comfortable with AI code generation, these strategies will make you more effective:

Building a Prompt Library

Start collecting your most successful prompts for common scenarios:

• "Customer segmentation analysis in SQL"
• "Year-over-year growth calculations in DAX"
• "Excel data cleanup and transformation in M"

Combining Languages

Real projects often require all three languages. Use AI to help you understand the handoffs:

Prompt: "I have SQL data that I'll import to Power BI. The SQL query calculates monthly sales. Show me how to create corresponding DAX measures that work with this data structure, and M code to refresh the data daily."

Learning from AI Output

Don't just use the code—study it. Ask follow-up questions like:

• "Why did you use SUMX instead of SUM in this DAX measure?"
• "What would happen if I removed the ALL function from this calculation?"
• "Can you show me an alternative approach to this M transformation?"

This turns AI from just a code generator into a personalized tutor.

Summary & Next Steps

You now have a practical framework for using AI to generate SQL, DAX, and M code effectively. The key insights to remember:

1. Prompt quality determines output quality: Provide context, be specific about goals, mention constraints, and specify the desired format.

2. AI excels at syntax, not business logic: You still need to verify that the generated code actually solves your business problem correctly.

3. Iteration is essential: Rarely will the first generated code be perfect. Use follow-up prompts to refine and optimize.

4. Test everything: AI-generated code can run successfully while producing incorrect results. Always validate with known data first.

5. Learn from the output: Study the generated code to improve your understanding of each language.

Recommended Next Steps

1. Practice with your own data: Take a current project and try generating code with AI for each component.

2. Build prompt templates: Create reusable prompt formats for your most common tasks.

3. Explore advanced AI features: Many AI tools now offer code explanation, debugging, and optimization features beyond basic generation.

4. Join communities: Participate in forums where others share their AI coding experiences and prompt strategies.

5. Stay updated: AI capabilities in code generation are improving rapidly. Follow updates from your preferred AI tools.

The combination of AI assistance and your business knowledge creates a powerful partnership. You provide the strategic thinking and domain expertise, while AI handles the syntax and suggests implementation approaches. This collaboration will make you more productive and help you tackle more complex data challenges than ever before.

Start small, experiment freely, and remember that every expert was once a beginner. With AI as your coding assistant, you're equipped to write sophisticated queries and transformations across all three critical languages in the modern data stack.