Power Query and M Language the easy way

Discover why Microsoft built Power Query and the M language to transform and clean data, connect to hundreds of sources, and automate repeatable, refreshable workflows.

Explore the Power Query interface in Power BI, learning how to load data and transform it. See the Navigator pane, Formula bar, and applied steps for cleaning and shaping data.

Learn M language basics through theory and hands-on power query demos, using Power BI, Advanced Editor, formula bar, and file workflows with source and working groups.

Explore M language syntax in Power Query, including subroutines, in and out sections, comma rules, and comments, then build a sample with a custom column and balance with interest.

Explore M language primitive value types in Power Query, including null, logical, date, date time, time zone UTC, and text, with practical examples and syntax rules.

Explore M language operators, including record and list access with brackets, and practice relational, arithmetic, and logical operators. Build if-then-else conditions and function invocations to transform data.

Learn how to create lists from any column using Power Query M language, convert between lists and tables, and apply list operations like remove duplicates and sort.

Open Power BI Desktop, load the financials sample and a CSV, then create a main source files group and prepare duplicates on the home tab for transforming data.

Learn how Power Query connects to hundreds of sources—from files and folders to databases and Azure data services—and navigate the new sources dialog, including beta previews.

Connect to new sources, recent sources, and enter data using Power Query, advanced editor, and blank queries to build tables and reports.

Learn how to change data sources in Power Query through four methods, manage global versus file-specific settings, and set privacy levels to protect data when merging or appending.

Create and manage Power Query parameters using the manage dialog; define a state parameter with a type and default value, then apply it in the filter step and advanced editor.

Learn to use parameters as a file path in Power Query to centralize data source changes and automatically update hundreds of queries across files and databases.

Master Power Query options by refreshing previews, configuring properties, and using the advanced editor to manage M code, let and in blocks, and reference queries.

Learn to manage columns in Power Query using M language by selecting columns with table select columns, using the advanced editor, and keeping needed fields like country, segments, and sales.

Master reduce rows in Power Query by keeping or removing rows, using top, bottom, range, duplicates, and errors, and add an index to track changes.

Learn to reduce rows in Power Query with M language by using Table.FirstN, Table.LastN, Table.Range, and Table.Skip to select top rows, bottom rows, or ranges, plus distinct.

Learn to sort data in Power Query with multi-level orders, using ascending and descending on full name, state, education, and balance, and manage filters with a custom state sort table.

Sort data in Power Query with M language using the table.sort function on a table, sorting by segment, then country and product in ascending order.

Learn to split a column in Power Query using delimiter (leftmost, rightmost, or each), by character count or by position, and by case or digits, including dates.

Learn how to split a name into first and last names with m language in power query, using space delimiter in advanced editor to output first name and last name.

Learn to group data with M language using table.Group, grouping by segment (and optionally country), and computing aggregates like count and list to derive total sales and invoice counts.

Learn to promote the first row to headers and demote headers to a first row in Power Query using M, with hands-on Excel examples and transform steps.

Learn to merge and append data in Power Query by left-joining a CSV and an Excel file on state, adding state abbreviation and office address as new columns.

Learn how to append tables in Power Query by combining files with identical column names into a longer table, adding a country column, and loading a unified dataset for reporting.

Append tables from monthly excel files using power query, combining and transforming data from a folder, ensuring identical columns, and refreshing to auto append new country data.

Group data in Power Query with group by and new group to create age bins and age groups, plus categorize by education and balance using a conditional column.

Learn to group data by state or age using group by in Power Query. Compute counts, sums, averages, and deviations, and create customer rankings with visuals like a pie chart.

Power Query and M language basics teach transpose data, reverse rows, and count rows using group by, pivot, and demote headers, with practical M code in the advanced editor.

Power Query automatically detects data types when loading files; verify and adjust types, and rename columns descriptively, then apply changes using the advanced editor and M code.

Master pivot and unpivot techniques in Power Query to flatten data into a matrix, reorient attributes and values, and prepare charts, reports, and filters.

Explore how to format text in Power Query using M language, applying lower, upper, trim, clean, and proper capitalization through transform column and add column steps.

Master merging columns and transforming data with Power Query and M language. Capitalize model suffixes and combine item with total sales using a dash delimiter.

Learn to extract text with Power Query using transform column options such as length, first/last characters, digits, and range; derive month, day, and year from dates using delimiters.

Parse and extract XML data with Power Query and M Language, transforming XML tables from text, CSV, or web sources, and add columns as needed.

Parse JSON data with Power Query and M Language by using the transform tab to parse JSON and expand records and lists to reveal author, assets, and related fields.

Learn to compute statistics on a number column in Power Query using M language, including sum, average, minimum, maximum, median, and standard deviation, and create total balance and percentage columns.

Explore standard and scientific transformations in Power Query and M Language, including add column and transform column operations, absolute value, powers, and null handling. Read Excel data.

Master trigonometric functions and rounding in Power Query using M Language, transforming or adding columns, and set decimals to two decimals for sine, cosine, and tangent results.

Explore how Power Query and M language perform rounding with add column, rounding up and down, and control decimal places, plus use information functions to check even or odd numbers.

Create a date dimension in Power Query, adding year, month name and number, days in month, quarter, and week, then connect it to multiple tables for efficient filtering.

Learn to transform time and duration in Power Query using M language, including extracting hours and minutes, calculating delivery duration, and formatting results into a human-readable form.

Create a custom column in Power Query using M language, exploring static and dynamic values, date time calculations with date time local now, and computing durations in years and months.

Create a custom column in Power Query to compute age from date of birth, convert duration to years, and classify by generation with if-then-else logic (baby boomers to Gen Z).

Learn to add a column across multiple tables by creating an external function in Power Query and M Language and invoking it to compute interest on balances.

Learn how to use Power Query and M language to dynamically assign interest by state with invoke custom column, including California 3.5 versus others, using let, if, and invoke function.

Create and customize an index column in Power Query and M Language to display row numbers, start values, and increments, enabling sorting, filtering by row position, and restoring original order.

Learn to duplicate columns, split full names into first and last names, extract year and month from dates, and merge columns with custom separators using Power Query and M Language.

Learn text transformations in Power Query, including lower case, upper case, capitalize each word, trim and clean, and add prefix or suffix with three application methods.

Master format functions in M language to convert text to lowercase, uppercase, or proper case, and apply trim and clean operations while adding new columns with the last applied step.

Learn to parse XML data in Power Query using M Language by adding a column, passing and expanding the XML, and reading from files or the web.

Learn to parse JSON data with Power Query and M Language by creating a table, adding a parse column, and expanding fields such as visual name, version, and class.

Explore statistics in Power Query and M Language for number columns. Compute minimum, maximum, average, sum, and standard deviation, and create new columns with add column and custom column.

Apply standard and scientific transformations in Power Query using add column and transform column to perform arithmetic, absolute value, square root, power, and logarithms, with careful null handling.

Learn to perform date transformations and build a custom date calendar in Power Query and M language for Power BI, deriving year, quarter, month, and week with abbreviations.

Create a date table in Power Query and M Language, adding year, month name, month number, quarter, week, and day name for robust time intelligence.

Explore the view tab in Power Query editor to manage the query settings pane, applied steps, and step editing with the gear icon, including promoting headers and adding an index.

Diagnose Power Query steps with the diagnostic tool to generate detail reports, identify time-taken bottlenecks and trace gaps, and use properties and M language edits for documentation and optimization.

Explore data preview and data profiling in the view tab to assess data quality, detect anomalies, and profile columns with distinct values, errors, and empty cells.

Create a data profile for data owners in Power Query by using table profile to expose min, max, average, std, nulls and completeness percentages, then pivot to highlight per-record gaps.

Create a date table using DAX and M language, paste the code to build a test table, then customize start date and fiscal month for a full calendar.