Power Query M for Power BI

Power Query tools

• Power Query M FormatterFormat M code from Power BI or Excel with Microsoft's own Power Query formatter.
• Power BI Date Table GeneratorGenerate a complete date table as Power Query M, with fiscal year and ISO week options.

Power Query M Code Snippets

145 snippets in 17 categories, each with the full code, notes on what it needs and related snippets. Search the whole Power Query M Code Snippets, or jump straight into a category:

• Data Cleaning 10
• Text Transformations 12
• Error Handling 6
• Data Types 13
• Row Operations 5
• Date & Time 19
• Conditional Logic 12
• Column Operations 8
• Table Transformations 15
• Merging & Appending 2
• List & Record Operations 2
• Advanced M Functions 13
• File Handling 10
• API & Web Data 6
• Performance Optimization 2
• Database & Refresh 5
• Comparing & Lookups 5

Featured snippets

The snippets other entries in the library build on or point to most often, a good place to start.

• Fiscal Calendar with Custom Start MonthBuilds a calendar table where the fiscal year and fiscal month are calculated from a custom fiscal year start month instead of January.
• Generate a Date TableBuilds a continuous calendar table between two dates with year, month and quarter columns.
• Convert Currency Text to Number ($1,200.50)Strips currency symbols and thousands separators from a text value before converting it to a proper number.
• Unpivot Month ColumnsUnpivots only the columns that look like month names, leaving key columns like ID or Region untouched.
• Convert a Text Column to NumberConverts a text column containing numeric values into a proper Number type.
• Transpose Table and Keep HeadersFlips rows and columns while preserving the original column names as a new first row.
• Extract Digits Only from TextRemoves every non-numeric character from a text column, leaving only the digits.
• Add a Boolean Flag Column from a ConditionAdds a simple true/false column instead of text, which is cheaper to filter and aggregate on later.
• Trim and Clean All Text ColumnsApplies trim and clean to every text column in a table at once, without naming columns individually.
• Remove Extra Spaces Between WordsCollapses repeated spaces inside a text value down to a single space, in addition to trimming the ends.
• Conditional Column Based on a Value RangeFlags rows whose value falls between two thresholds, using Power Query's between-style comparison.
• Load Google Sheet via Published CSV LinkLoads data from a Google Sheet that's been published to the web as a CSV, without needing an API connection.

Related topics

• DAX for Power BIDAX measures and the DAX formatter for the calculations that sit on top of your data.