DEV Community

Thesius Code
Thesius Code

Posted on • Originally published at datanest-stores.pages.dev

Excel Power Query Templates

#datascience #sql #python #analytics

Excel Power Query Templates

50+ Power Query M code templates for data transformation, API connections, file consolidation, and automated reporting. Each template is copy-paste ready with inline comments explaining every step. Works with Excel 2016+ and Power BI Desktop.

Key Features

  • 50+ M Code Templates — organized by category: import, transform, combine, date, text, API
  • File Consolidation Patterns — merge CSVs, Excel files, and folders into a single table
  • API Connection Templates — REST API connectors with pagination and error handling
  • Date Intelligence — fiscal calendars, rolling windows, holiday tables
  • Text Transformation — parsing, splitting, fuzzy matching, standardization
  • Dynamic Parameters — templates that accept runtime parameters for reusable queries
  • Error Handling Patterns — try/otherwise blocks, null handling, type coercion
  • Performance Tips — query folding indicators and optimization techniques

Quick Start

  1. Open Excel → Data → Get Data → Blank Query → Advanced Editor
  2. Paste any template from src/templates/
  3. Update the source path and column references for your data
  4. Click "Close & Load" to materialize the query

Basic: Consolidate All CSVs in a Folder 

let
    // Point to folder containing CSV files
    Source = Folder.Files("C:\Data\monthly_reports"),

    // Filter to CSV files only
    FilteredFiles = Table.SelectRows(Source, each [Extension] = ".csv"),

    // Parse each CSV with consistent settings
    ParsedFiles = Table.AddColumn(FilteredFiles, "ParsedData", each
        Csv.Document([Content], [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv])
    ),

    // Expand all parsed tables into one
    ExpandedData = Table.ExpandTableColumn(
        ParsedFiles, "ParsedData",
        {"Column1", "Column2", "Column3", "Column4"}
    ),

    // Promote first row as headers and set types
    PromotedHeaders = Table.PromoteHeaders(ExpandedData, [PromoteAllScalars=true]),
    TypedColumns = Table.TransformColumnTypes(PromotedHeaders, {
        {"Date", type date}, {"Amount", type number}, {"Category", type text}
    })
in
    TypedColumns
Enter fullscreen mode Exit fullscreen mode

Usage Examples

Dynamic Date Table (Fiscal Year Support) 

let
    // Parameters — adjust for your fiscal calendar
    FiscalYearStartMonth = 7,    // July = fiscal year start
    StartDate = #date(2020, 1, 1),
    EndDate = Date.EndOfYear(DateTime.Date(DateTime.LocalNow())),

    // Generate date list
    DateCount = Duration.Days(EndDate - StartDate) + 1,
    DateList = List.Dates(StartDate, DateCount, #duration(1, 0, 0, 0)),
    DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    TypedDate = Table.TransformColumnTypes(DateTable, {{"Date", type date}}),

    // Add calendar columns
    AddYear = Table.AddColumn(TypedDate, "Year", each Date.Year([Date]), Int64.Type),
    AddMonth = Table.AddColumn(AddYear, "Month", each Date.Month([Date]), Int64.Type),
    AddMonthName = Table.AddColumn(AddMonth, "Month Name", each Date.MonthName([Date]), type text),
    AddQuarter = Table.AddColumn(AddMonthName, "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date])), type text),
    AddWeekday = Table.AddColumn(AddQuarter, "Day of Week", each Date.DayOfWeekName([Date]), type text),

    // Add fiscal year columns
    AddFiscalYear = Table.AddColumn(AddWeekday, "Fiscal Year", each
        if Date.Month([Date]) >= FiscalYearStartMonth
        then Date.Year([Date]) + 1
        else Date.Year([Date]),
        Int64.Type
    ),
    AddFiscalQuarter = Table.AddColumn(AddFiscalYear, "Fiscal Quarter", each
        "FQ" & Text.From(Number.RoundUp((Date.Month([Date]) - FiscalYearStartMonth + 12) / 3)),
        type text
    )
in
    AddFiscalQuarter
Enter fullscreen mode Exit fullscreen mode

REST API with Pagination 

let
    // Paginated API fetch — handles offset-based pagination
    BaseUrl = "https://api.example.com/v1/records",
    PageSize = 100,

    GetPage = (offset as number) as table =>
        let
            Response = Json.Document(Web.Contents(
                BaseUrl,
                [Query = [offset = Text.From(offset), limit = Text.From(PageSize)]]
            )),
            AsTable = Table.FromRecords(Response[data])
        in
            AsTable,

    // Fetch pages until empty
    AllPages = List.Generate(
        () => [Page = GetPage(0), Offset = 0],
        each Table.RowCount([Page]) > 0,
        each [Page = GetPage([Offset] + PageSize), Offset = [Offset] + PageSize],
        each [Page]
    ),
    Combined = Table.Combine(AllPages)
in
    Combined
Enter fullscreen mode Exit fullscreen mode

Text Standardization 

let
    CleanText = (input as text) as text =>
        let
            Trimmed = Text.Trim(input),
            Lowered = Text.Lower(Trimmed),
            NoDoubleSpaces = Text.Replace(Lowered, "  ", " "),
            NoPunctuation = Text.Remove(NoDoubleSpaces, {".", ",", ";", ":", "!", "?"})
        in
            NoPunctuation
in
    CleanText
Enter fullscreen mode Exit fullscreen mode

Configuration 

# config.example.yaml — Template defaults
source:
  default_encoding: 65001       # UTF-8
  csv_delimiter: ","
  skip_header_rows: 0

date_table:
  start_year: 2020
  fiscal_year_start_month: 1    # January (change for fiscal)

api:
  page_size: 100
  max_retries: 3
  timeout_seconds: 30
Enter fullscreen mode Exit fullscreen mode

Best Practices

  1. Check query folding — right-click a step and look for "View Native Query" to confirm pushdown
  2. Use parameters for file paths — never hardcode C:\Users\yourname\ in shared templates
  3. Buffer large tables — use Table.Buffer() when referencing the same table multiple times
  4. Name your steps clearly — default step names like "Changed Type1" are unmaintainable
  5. Handle nulls explicitly — use try ... otherwise to prevent query failures on bad data
  6. Document with comments — M supports // line comments and /* */ block comments

Troubleshooting

Issue Cause Fix
"Expression.Error: column not found" Source schema changed Add MissingField.UseNull to expand steps
Query runs slowly Query folding broken Check which step breaks folding; restructure to keep native query
API returns 429 Too Many Requests Rate limiting Add Function.InvokeAfter with delay between page fetches
Dates parsed as text Locale mismatch Specify culture in Date.FromText(value, "en-US")

This is 1 of 11 resources in the Data Analyst Toolkit toolkit. Get the complete [Excel Power Query Templates] with all files, templates, and documentation for $29.

Get the Full Kit →

Or grab the entire Data Analyst Toolkit bundle (11 products) for $129 — save 30%.

Get the Complete Bundle →

Related Articles

Top comments (0)