# LearnSQL > A free, read-only learning site by CPE Today: sample databases, a REST API, an MCP server and long guides on SQL, DAX and Power Query M. Guides are plain Markdown. Start with the agents file. ## Start here - [Agents file](https://learnsql.cpetoday.com/agents.md): how to connect, query rules, which guide to read - [All guides in one file](https://learnsql.cpetoday.com/llms-full.txt): every guide, concatenated - [API documentation](https://learnsql.cpetoday.com/docs): REST API reference - [MCP server setup](https://learnsql.cpetoday.com/ai/connect): connect an AI assistant ## Start here - [How databases work, for finance professionals](https://learnsql.cpetoday.com/analyze/how-databases-work.md): Tables, keys and relationships explained with invoices and ledgers, why an ERP is not one big spreadsheet, and how to read a schema. - [From Excel to SQL: what you already know](https://learnsql.cpetoday.com/analyze/excel-to-sql.md): VLOOKUP is a join, SUMIFS is GROUP BY, a filter is WHERE: translate the Excel skills you use every day into SQL, side by side. ## SQL - [SQL essentials](https://learnsql.cpetoday.com/analyze/sql-essentials.md): SELECT, WHERE, joins, GROUP BY, CASE, dates, subqueries and CTEs, with the SQL Server and MariaDB differences. - [SQL deep end](https://learnsql.cpetoday.com/analyze/sql-deep-end.md): Window functions, recursive queries, ROLLUP, deduplication and gaps-and-islands, indexes and execution plans. ## Power Query - [Power Query editor: clean data without code](https://learnsql.cpetoday.com/analyze/power-query-editor.md): The editor, applied steps, the common clicks for finance data, and what each click writes in M. - [M basics: queries, values, lists and records](https://learnsql.cpetoday.com/analyze/power-query-m-basics.md): What a query is, the let and in structure, types and values, lists, records and each. - [M tables: rows, columns, joins and groups](https://learnsql.cpetoday.com/analyze/power-query-m-tables.md): Select, add and change columns, merge, group, pivot and unpivot, plus text, date and number functions. - [M functions and patterns](https://learnsql.cpetoday.com/analyze/power-query-m-patterns.md): Your own functions, if and try, running totals, a date table, latest row per group and a complete worked example. - [M performance, errors and when to use M](https://learnsql.cpetoday.com/analyze/power-query-m-performance.md): Query folding, the errors you will meet and how to fix them, and when to use M, SQL or DAX. ## Data modeling - [Data modeling for finance](https://learnsql.cpetoday.com/analyze/data-modeling-for-finance.md): Facts and dimensions for a general ledger, budget against actual at different grains, chart of accounts hierarchies and the mistakes that give wrong totals. - [Relationships and the data model](https://learnsql.cpetoday.com/analyze/relationships.md): Star schemas, RELATED, role-playing dates, many-to-many and security. - [Model the LearnSQL databases](https://learnsql.cpetoday.com/analyze/learnsql-models.md): Connection details and a star-schema recipe for each sample database. - [Data structures and Power BI limits](https://learnsql.cpetoday.com/analyze/data-structures.md): Which relational structures exist, which sample database teaches each, and what Power BI and Excel cannot model or load. ## Power BI and DAX - [Power BI Desktop how-to](https://learnsql.cpetoday.com/analyze/power-bi-desktop.md): Model view, DAX query view, parameters, security, publishing and PBIP projects. - [Power Pivot in Excel](https://learnsql.cpetoday.com/analyze/power-pivot.md): The Excel Data Model: load data, relate tables, write measures, PivotTables and cube formulas. - [DAX fundamentals](https://learnsql.cpetoday.com/analyze/fundamentals.md): Measures versus columns, row and filter context, VAR and RETURN, and your first measures. - [CALCULATE and filter context](https://learnsql.cpetoday.com/analyze/calculate.md): The most important function in DAX: add, remove and keep filters. - [Time intelligence](https://learnsql.cpetoday.com/analyze/time-intelligence.md): Date tables, year to date, prior year, rolling 12 months and fiscal calendars. - [Iterators and table functions](https://learnsql.cpetoday.com/analyze/iterators.md): SUMX, RANKX, TOPN, ADDCOLUMNS and when to use each. - [Pattern cookbook](https://learnsql.cpetoday.com/analyze/patterns.md): Percent of total, ranking, Pareto, new versus returning customers, budget variance, calculation groups. - [Report recipes](https://learnsql.cpetoday.com/analyze/report-recipes.md): The standard sales, finance and operations report layouts and the measures behind them. - [Performance and troubleshooting](https://learnsql.cpetoday.com/analyze/performance.md): Make DAX fast, read common error messages, and debug a wrong number. - [Function reference](https://learnsql.cpetoday.com/analyze/functions.md): A quick reference of the functions you will use, grouped by purpose. - [DAX deep end](https://learnsql.cpetoday.com/analyze/dax-deep-end.md): How the formula and storage engines work, context transition, how CALCULATE evaluates, query plans and why a measure is slow. ## Finance playbooks - [SQL for accountants](https://learnsql.cpetoday.com/analyze/sql-for-accountants.md): Accounting questions answered in SQL and checked on real sample data: aging, trial balance, year over year, duplicates, accruals and fund balances. - [DAX for financial reporting](https://learnsql.cpetoday.com/analyze/dax-financial-reporting.md): Model a general ledger for DAX and build income statement, balance sheet, budget variance and ratio measures with correct signs and fiscal calendars. - [Power Query for accounting data](https://learnsql.cpetoday.com/analyze/power-query-accounting-data-prep.md): Fix the import problems finance teams meet: bank exports, trial balances, monthly folders, account mapping and data quality checks, with M you can paste. - [Power BI for finance teams: ledger to dashboard](https://learnsql.cpetoday.com/analyze/power-bi-for-finance.md): A start-to-finish build: connect to the ledger, shape it, model it, write the measures and lay out a monthly finance pack. - [Audit and controls analytics in SQL](https://learnsql.cpetoday.com/analyze/audit-analytics-sql.md): Journal entry testing, duplicate payments, vendor master checks, Benford's law and cutoff tests, each with SQL run on real sample data. ## Sample database guides - [CPA firm practice management (cpafirm)](https://learnsql.cpetoday.com/analyze/cpa-firm.md): Clients, engagements, tax returns, time, billing, trust and a general ledger for a small-business CPA firm, with realization, WIP and AR aging queries. - [Nonprofit donors, grants and funds (nonprofit)](https://learnsql.cpetoday.com/analyze/nonprofit.md): Donor management, pledges, grants and proposals, fund accounting, payroll allocation and volunteers for a regional nonprofit. - [Power Query playground (pqlab)](https://learnsql.cpetoday.com/analyze/power-query-lab.md): 86 tables built for practicing Power Query: a retail backbone plus deliberately messy tables (now with geospatial exercises), a technique index and M recipes. - [IoT and smart home (smarthome)](https://learnsql.cpetoday.com/analyze/smart-home.md): One connected family home: 83 devices, 13 months of sensor readings, solar, battery and EV energy, automations, security, bills, trips and a floor plan. - [City government operations (bigbear)](https://learnsql.cpetoday.com/analyze/big-bear.md): A small resort city: 311 requests, snow operations, dispatch, permits and code enforcement, a water utility, the lodging tax, parcels and fund accounting, on a map and a July-June fiscal year. - [Retail data warehouse (retailwh)](https://learnsql.cpetoday.com/analyze/retail-warehouse.md): Star and snowflake schemas, slowly changing customers, price history, inventory and order-pipeline snapshots, a factless table, multi-currency and budgets, in one retail warehouse. - [Big data: manufacturer orders (mfgorders)](https://learnsql.cpetoday.com/analyze/big-orders.md): 25 million order lines from a manufacturer to wholesalers and retailers, stored as compressed columnstore on SQL Server and computed on the fly on MariaDB; plus a bill of materials. - [Utility field operations (fieldops)](https://learnsql.cpetoday.com/analyze/field-operations.md): A water and electric utility: assets, work orders with labor and materials, vendors with purchase orders and invoices, customer billing and service interruptions, all keyed. - [Maps and fiscal calendars](https://learnsql.cpetoday.com/analyze/maps-and-fiscal.md): Addresses with latitude and longitude, reference geography, and a fiscal calendar in every lab database: map it, chart it by fiscal period. ## Function reference ### DAX: 174 functions - [Index of all DAX functions, in Markdown](https://learnsql.cpetoday.com/functions/dax/md) #### Aggregation - [AVERAGE](https://learnsql.cpetoday.com/functions/dax/average.md): Returns the mean of the numbers in one column. - [AVERAGEX](https://learnsql.cpetoday.com/functions/dax/averagex.md): Evaluates an expression on each row of a table, then averages the results. - [COUNT](https://learnsql.cpetoday.com/functions/dax/count.md): Counts the cells in a column that hold a number. - [COUNTA](https://learnsql.cpetoday.com/functions/dax/counta.md): Counts the cells in a column that are not blank, whatever type they hold. - [COUNTBLANK](https://learnsql.cpetoday.com/functions/dax/countblank.md): Counts the cells in a column that are blank. - [COUNTROWS](https://learnsql.cpetoday.com/functions/dax/countrows.md): Counts the rows in a table. - [COUNTX](https://learnsql.cpetoday.com/functions/dax/countx.md): Evaluates an expression on each row of a table and counts the rows where the result is not blank. - [DISTINCTCOUNT](https://learnsql.cpetoday.com/functions/dax/distinctcount.md): Counts the unique values in a column. - [DIVIDE](https://learnsql.cpetoday.com/functions/dax/divide.md): Divides two numbers and returns a safe result when the denominator is zero or blank. - [MAX](https://learnsql.cpetoday.com/functions/dax/max.md): Returns the largest value in a column, or the larger of two values. - [MAXX](https://learnsql.cpetoday.com/functions/dax/maxx.md): Evaluates an expression on each row of a table and returns the largest result. - [MEDIAN](https://learnsql.cpetoday.com/functions/dax/median.md): Returns the middle value of the numbers in a column. - [MIN](https://learnsql.cpetoday.com/functions/dax/min.md): Returns the smallest value in a column, or the smaller of two values. - [MINX](https://learnsql.cpetoday.com/functions/dax/minx.md): Evaluates an expression on each row of a table and returns the smallest result. - [PRODUCT](https://learnsql.cpetoday.com/functions/dax/product.md): Multiplies together the numbers in a column. - [SUM](https://learnsql.cpetoday.com/functions/dax/sum.md): Adds up every number in one column. - [SUMX](https://learnsql.cpetoday.com/functions/dax/sumx.md): Evaluates an expression on each row of a table, then adds the results. #### Date and time - [CALENDAR](https://learnsql.cpetoday.com/functions/dax/calendar.md): Returns a table with one column called Date that holds every day from the start date to the end date. - [CALENDARAUTO](https://learnsql.cpetoday.com/functions/dax/calendarauto.md): Returns a date table that spans from the earliest to the latest date found in the date columns of the model. - [DATE](https://learnsql.cpetoday.com/functions/dax/date.md): Returns a date built from a year, a month number and a day number. - [DATEDIFF](https://learnsql.cpetoday.com/functions/dax/datediff.md): Returns the number of intervals between two dates. - [DATEVALUE](https://learnsql.cpetoday.com/functions/dax/datevalue.md): Converts a date written as text into a date value. - [DAY](https://learnsql.cpetoday.com/functions/dax/day.md): Returns the day of the month of a date, from 1 to 31. - [EDATE](https://learnsql.cpetoday.com/functions/dax/edate.md): Returns the date that is a given number of months before or after the start date, on the same day of the month. - [EOMONTH](https://learnsql.cpetoday.com/functions/dax/eomonth.md): Returns the last day of the month that is a given number of months before or after the start date. - [MONTH](https://learnsql.cpetoday.com/functions/dax/month.md): Returns the month number of a date, from 1 for January to 12 for December. - [NOW](https://learnsql.cpetoday.com/functions/dax/now.md): Returns the current date and time. - [QUARTER](https://learnsql.cpetoday.com/functions/dax/quarter.md): Returns the calendar quarter of a date, from 1 for January to March to 4 for October to December. - [TIME](https://learnsql.cpetoday.com/functions/dax/time.md): Returns a time of day built from hour, minute and second numbers. - [TODAY](https://learnsql.cpetoday.com/functions/dax/today.md): Returns the current date, with no time part. - [WEEKDAY](https://learnsql.cpetoday.com/functions/dax/weekday.md): Returns a number for the day of the week of a date. - [WEEKNUM](https://learnsql.cpetoday.com/functions/dax/weeknum.md): Returns the week number of the year for a date. - [YEAR](https://learnsql.cpetoday.com/functions/dax/year.md): Returns the four-digit calendar year of a date. - [YEARFRAC](https://learnsql.cpetoday.com/functions/dax/yearfrac.md): Returns the fraction of a year between two dates. #### Filter and context - [ALL](https://learnsql.cpetoday.com/functions/dax/all.md): Returns every row or every value of a table or column, ignoring the filters on it. - [ALLEXCEPT](https://learnsql.cpetoday.com/functions/dax/allexcept.md): Removes every filter on a table except the filters on the columns you list. - [ALLNOBLANKROW](https://learnsql.cpetoday.com/functions/dax/allnoblankrow.md): Returns all the rows of a table except the blank row that a broken relationship can add. - [ALLSELECTED](https://learnsql.cpetoday.com/functions/dax/allselected.md): Returns the rows or values that remain after slicers and page filters, ignoring the visual's own row and column grouping. - [CALCULATE](https://learnsql.cpetoday.com/functions/dax/calculate.md): Evaluates an expression in a filter context that you change. - [CALCULATETABLE](https://learnsql.cpetoday.com/functions/dax/calculatetable.md): Evaluates a table expression in a filter context that you change. - [DISTINCT](https://learnsql.cpetoday.com/functions/dax/distinct.md): Returns the unique values of a column, or the table with duplicate rows removed. - [EARLIER](https://learnsql.cpetoday.com/functions/dax/earlier.md): Returns the value of a column from an outer row, while a formula loops over an inner row. - [EARLIEST](https://learnsql.cpetoday.com/functions/dax/earliest.md): Returns the value of a column from the outermost row context, the row where the nesting started. - [FILTER](https://learnsql.cpetoday.com/functions/dax/filter.md): Returns the rows of a table where a condition is true, checked one row at a time. - [HASONEFILTER](https://learnsql.cpetoday.com/functions/dax/hasonefilter.md): Returns TRUE when exactly one filter is applied directly to a column, whatever the number of values in that filter. - [ISEMPTY](https://learnsql.cpetoday.com/functions/dax/isempty.md): Returns TRUE when a table has no rows in the current filter context. - [KEEPFILTERS](https://learnsql.cpetoday.com/functions/dax/keepfilters.md): Combines a new filter with the existing filter, so only rows that satisfy both are kept. - [REMOVEFILTERS](https://learnsql.cpetoday.com/functions/dax/removefilters.md): Clears the filters on the tables or columns you name, or on the whole model when used with no arguments. Works only as a CALCULATE filter. - [VALUES](https://learnsql.cpetoday.com/functions/dax/values.md): Returns the distinct values, or the distinct rows, that are visible in the current filter context. - [VAR](https://learnsql.cpetoday.com/functions/dax/var.md): Stores a value or a table in a named variable that the RETURN part can reuse. #### Financial - [DB](https://learnsql.cpetoday.com/functions/dax/db.md): Returns the fixed-rate declining balance depreciation of an asset for a given period. - [DDB](https://learnsql.cpetoday.com/functions/dax/ddb.md): Returns the double declining balance depreciation of an asset for a given period. - [FV](https://learnsql.cpetoday.com/functions/dax/fv.md): Returns the future value of an investment that has equal periodic payments and a fixed rate. - [IPMT](https://learnsql.cpetoday.com/functions/dax/ipmt.md): Returns the interest part of the payment for a given period of a loan with a fixed rate. - [NPER](https://learnsql.cpetoday.com/functions/dax/nper.md): Returns the number of periods needed to pay off a loan, or to reach a target, with equal payments at a fixed rate. - [PMT](https://learnsql.cpetoday.com/functions/dax/pmt.md): Returns the payment for each period of a loan or annuity that has a fixed rate. - [PPMT](https://learnsql.cpetoday.com/functions/dax/ppmt.md): Returns the principal part of the payment for a given period of a loan with a fixed rate. - [PV](https://learnsql.cpetoday.com/functions/dax/pv.md): Returns the present value of a series of equal payments, or of a future amount, at a fixed rate. - [RATE](https://learnsql.cpetoday.com/functions/dax/rate.md): Returns the interest rate per period of a loan or annuity. - [SLN](https://learnsql.cpetoday.com/functions/dax/sln.md): Returns the straight-line depreciation of an asset for one period. - [SYD](https://learnsql.cpetoday.com/functions/dax/syd.md): Returns the sum-of-years'-digits depreciation of an asset for a given period. - [XIRR](https://learnsql.cpetoday.com/functions/dax/xirr.md): Returns the annual internal rate of return for cash flows that fall on specific dates, which do not need to be evenly spaced. - [XNPV](https://learnsql.cpetoday.com/functions/dax/xnpv.md): Returns the net present value of cash flows that fall on specific dates, which do not need to be evenly spaced. #### Information - [HASONEVALUE](https://learnsql.cpetoday.com/functions/dax/hasonevalue.md): Returns TRUE when a column has exactly one distinct value in the current filter context. - [ISCROSSFILTERED](https://learnsql.cpetoday.com/functions/dax/iscrossfiltered.md): Returns TRUE when a column is filtered indirectly, through a relationship with another table. - [ISERROR](https://learnsql.cpetoday.com/functions/dax/iserror.md): Returns TRUE when a calculation returns an error. - [ISFILTERED](https://learnsql.cpetoday.com/functions/dax/isfiltered.md): Returns TRUE when a column has a direct filter applied to it. - [ISINSCOPE](https://learnsql.cpetoday.com/functions/dax/isinscope.md): Returns TRUE when a column is at a level of a matrix or hierarchy that is shown in the current row or column. - [ISNUMBER](https://learnsql.cpetoday.com/functions/dax/isnumber.md): Returns TRUE when a value is a number. - [ISTEXT](https://learnsql.cpetoday.com/functions/dax/istext.md): Returns TRUE when a value is text. - [SELECTEDVALUE](https://learnsql.cpetoday.com/functions/dax/selectedvalue.md): Returns the one selected value of a column, or an alternate result when zero or several values are selected. - [USERNAME](https://learnsql.cpetoday.com/functions/dax/username.md): Returns the name of the user who is connected to the model. - [USERPRINCIPALNAME](https://learnsql.cpetoday.com/functions/dax/userprincipalname.md): Returns the user principal name of the person connected to the model, usually the sign in email address. #### Logical - [AND](https://learnsql.cpetoday.com/functions/dax/and.md): Returns TRUE only when both conditions are true. - [BLANK](https://learnsql.cpetoday.com/functions/dax/blank.md): Returns a blank value, which means no value at all. - [COALESCE](https://learnsql.cpetoday.com/functions/dax/coalesce.md): Returns the first value in the list that is not blank. - [FALSE](https://learnsql.cpetoday.com/functions/dax/false.md): Returns the logical value FALSE. - [IF](https://learnsql.cpetoday.com/functions/dax/if.md): Returns one value when a test is true and another value when it is false. - [IFERROR](https://learnsql.cpetoday.com/functions/dax/iferror.md): Returns a value, or a replacement value when the calculation fails with an error. - [ISBLANK](https://learnsql.cpetoday.com/functions/dax/isblank.md): Returns TRUE when a value is blank. - [NOT](https://learnsql.cpetoday.com/functions/dax/not.md): Reverses a true or false value. - [OR](https://learnsql.cpetoday.com/functions/dax/or.md): Returns TRUE when at least one of two conditions is true. - [SWITCH](https://learnsql.cpetoday.com/functions/dax/switch.md): Compares an expression with a list of values and returns the result for the first match. - [TRUE](https://learnsql.cpetoday.com/functions/dax/true.md): Returns the logical value TRUE. #### Math - [ABS](https://learnsql.cpetoday.com/functions/dax/abs.md): Returns a number without its sign, so a negative becomes positive. - [CEILING](https://learnsql.cpetoday.com/functions/dax/ceiling.md): Rounds a number up to the nearest multiple of a significance value. - [FLOOR](https://learnsql.cpetoday.com/functions/dax/floor.md): Rounds a number down to the nearest multiple of a significance value. - [INT](https://learnsql.cpetoday.com/functions/dax/int.md): Rounds a number down to the nearest whole number, toward negative infinity. - [MOD](https://learnsql.cpetoday.com/functions/dax/mod.md): Returns the remainder after a number is divided by a divisor. - [POWER](https://learnsql.cpetoday.com/functions/dax/power.md): Raises a number to a power. - [ROUND](https://learnsql.cpetoday.com/functions/dax/round.md): Rounds a number to a set number of decimal places, or to the left of the decimal point. - [ROUNDDOWN](https://learnsql.cpetoday.com/functions/dax/rounddown.md): Rounds a number down, toward zero, to a set number of digits. - [ROUNDUP](https://learnsql.cpetoday.com/functions/dax/roundup.md): Rounds a number up, away from zero, to a set number of digits. - [SQRT](https://learnsql.cpetoday.com/functions/dax/sqrt.md): Returns the square root of a number. - [TRUNC](https://learnsql.cpetoday.com/functions/dax/trunc.md): Cuts a number down to a set number of digits, toward zero, without rounding. #### Ranking and windows - [INDEX](https://learnsql.cpetoday.com/functions/dax/index.md): Returns the row at a given position in an ordered list. Position 1 is the first row, and a negative number counts from the end. - [OFFSET](https://learnsql.cpetoday.com/functions/dax/offset.md): Returns the row a set number of positions before or after the current row, in an ordered list. - [RANK](https://learnsql.cpetoday.com/functions/dax/rank.md): A newer DAX function that ranks items in an ordered set of rows. Its exact arguments depend on the version you use. - [RANKX](https://learnsql.cpetoday.com/functions/dax/rankx.md): Returns the rank of the current item among the rows of a table, based on an expression. - [ROWNUMBER](https://learnsql.cpetoday.com/functions/dax/rownumber.md): Gives each row of an ordered list a sequence number, starting from 1. - [TOPN](https://learnsql.cpetoday.com/functions/dax/topn.md): Returns the top n rows of a table, ordered by an expression, highest first unless you say ASC. - [WINDOW](https://learnsql.cpetoday.com/functions/dax/window.md): Returns a block of rows from an ordered list, with positions counted from the start of the list or from the current row. #### Relationships and lookup - [CROSSFILTER](https://learnsql.cpetoday.com/functions/dax/crossfilter.md): Changes how filters flow through an existing relationship, for one calculation. - [LOOKUPVALUE](https://learnsql.cpetoday.com/functions/dax/lookupvalue.md): Returns one value from a column, found by matching one or more columns to given values, with no relationship needed. - [RELATED](https://learnsql.cpetoday.com/functions/dax/related.md): Brings a value from the one side of a relationship into the current row of the many side. - [RELATEDTABLE](https://learnsql.cpetoday.com/functions/dax/relatedtable.md): Returns the rows of a table on the many side that match the current row on the one side. - [TREATAS](https://learnsql.cpetoday.com/functions/dax/treatas.md): Uses the values of a table as filters on columns in another table, with no relationship in the model. - [USERELATIONSHIP](https://learnsql.cpetoday.com/functions/dax/userelationship.md): Activates an inactive relationship for one calculation, inside CALCULATE. #### Table construction - [ADDCOLUMNS](https://learnsql.cpetoday.com/functions/dax/addcolumns.md): Returns the table you give, with extra computed columns added to each row. - [CONTAINS](https://learnsql.cpetoday.com/functions/dax/contains.md): Returns TRUE if the table has at least one row where the named column equals the value. - [CONTAINSROW](https://learnsql.cpetoday.com/functions/dax/containsrow.md): Returns TRUE if the table has a row that matches the values you give, in column order. - [CROSSJOIN](https://learnsql.cpetoday.com/functions/dax/crossjoin.md): Returns every combination of rows from the tables you give (a cartesian product). - [DATATABLE](https://learnsql.cpetoday.com/functions/dax/datatable.md): Creates a small table with the values typed into the formula. - [EXCEPT](https://learnsql.cpetoday.com/functions/dax/except.md): Returns the rows in the first table that are not in the second table. - [GENERATE](https://learnsql.cpetoday.com/functions/dax/generate.md): For each row of the first table, evaluates a second table expression and returns the combined rows, dropping first-table rows that produce nothing. - [GENERATESERIES](https://learnsql.cpetoday.com/functions/dax/generateseries.md): Returns a one-column table of numbers from a start value to an end value. - [GROUPBY](https://learnsql.cpetoday.com/functions/dax/groupby.md): Groups a table by columns and calculates totals for each group, using CURRENTGROUP() to reach the rows in each group. - [INTERSECT](https://learnsql.cpetoday.com/functions/dax/intersect.md): Returns the rows that appear in every table you give. - [ROW](https://learnsql.cpetoday.com/functions/dax/row.md): Returns a table with exactly one row, made of the named values. - [SELECTCOLUMNS](https://learnsql.cpetoday.com/functions/dax/selectcolumns.md): Returns a new table with only the columns you name, each one built from an expression. - [SUMMARIZE](https://learnsql.cpetoday.com/functions/dax/summarize.md): Returns one row for each combination of the grouping columns that exists in the data. - [SUMMARIZECOLUMNS](https://learnsql.cpetoday.com/functions/dax/summarizecolumns.md): Groups a table by the columns you name, calculates measures for each group, and drops groups where every measure is blank. - [UNION](https://learnsql.cpetoday.com/functions/dax/union.md): Appends the rows of several tables that have the same number of columns. #### Text - [CONCATENATE](https://learnsql.cpetoday.com/functions/dax/concatenate.md): Joins two text values into one text value. - [CONCATENATEX](https://learnsql.cpetoday.com/functions/dax/concatenatex.md): Evaluates an expression on each row of a table and joins the results into one text value. - [FIND](https://learnsql.cpetoday.com/functions/dax/find.md): Returns the position of one text value inside another, matching case exactly. - [FORMAT](https://learnsql.cpetoday.com/functions/dax/format.md): Converts a value to text using a format pattern such as currency, percent or date. - [LEFT](https://learnsql.cpetoday.com/functions/dax/left.md): Returns the first characters of a text value. - [LEN](https://learnsql.cpetoday.com/functions/dax/len.md): Returns the number of characters in a text value. - [LOWER](https://learnsql.cpetoday.com/functions/dax/lower.md): Converts all letters in a text value to lower case. - [MID](https://learnsql.cpetoday.com/functions/dax/mid.md): Returns a set number of characters from the middle of a text value, starting at a position. - [REPLACE](https://learnsql.cpetoday.com/functions/dax/replace.md): Replaces part of a text value, starting at a position and covering a set number of characters. - [REPT](https://learnsql.cpetoday.com/functions/dax/rept.md): Repeats a text value a set number of times. - [RIGHT](https://learnsql.cpetoday.com/functions/dax/right.md): Returns the last characters of a text value. - [SEARCH](https://learnsql.cpetoday.com/functions/dax/search.md): Returns the position of one text value inside another, ignoring case. - [SUBSTITUTE](https://learnsql.cpetoday.com/functions/dax/substitute.md): Replaces text that matches a search string with new text. - [TRIM](https://learnsql.cpetoday.com/functions/dax/trim.md): Removes extra spaces from text, leaving one space between words and none at the ends. - [UPPER](https://learnsql.cpetoday.com/functions/dax/upper.md): Converts all letters in a text value to capitals. - [VALUE](https://learnsql.cpetoday.com/functions/dax/value.md): Converts a text value that looks like a number into a number. #### Time intelligence: balances - [CLOSINGBALANCEMONTH](https://learnsql.cpetoday.com/functions/dax/closingbalancemonth.md): Evaluates an expression on the last date of the month in the filter context, for balances that do not add up over time. - [CLOSINGBALANCEQUARTER](https://learnsql.cpetoday.com/functions/dax/closingbalancequarter.md): Evaluates a balance on the last date of the calendar quarter in the filter context. - [CLOSINGBALANCEYEAR](https://learnsql.cpetoday.com/functions/dax/closingbalanceyear.md): Evaluates a balance on the last date of the year in the filter context, with an optional fiscal year end. - [OPENINGBALANCEMONTH](https://learnsql.cpetoday.com/functions/dax/openingbalancemonth.md): Evaluates a balance at the start of the month in the filter context. - [OPENINGBALANCEQUARTER](https://learnsql.cpetoday.com/functions/dax/openingbalancequarter.md): Evaluates a balance at the start of the calendar quarter in the filter context. - [OPENINGBALANCEYEAR](https://learnsql.cpetoday.com/functions/dax/openingbalanceyear.md): Evaluates a balance at the start of the year in the filter context, with an optional fiscal year end. #### Time intelligence: first and last - [ENDOFMONTH](https://learnsql.cpetoday.com/functions/dax/endofmonth.md): Returns the last date of the month that contains the dates in the filter context, as a one-row table. - [ENDOFQUARTER](https://learnsql.cpetoday.com/functions/dax/endofquarter.md): Returns the last date of the calendar quarter that contains the dates in the filter context, as a one-row table. - [ENDOFYEAR](https://learnsql.cpetoday.com/functions/dax/endofyear.md): Returns the last date of the year that contains the dates in the filter context, with an optional fiscal year end, as a one-row table. - [FIRSTDATE](https://learnsql.cpetoday.com/functions/dax/firstdate.md): Returns the first date in the filter context, as a one-row table. - [FIRSTNONBLANK](https://learnsql.cpetoday.com/functions/dax/firstnonblank.md): Returns the first value in a column, in order, where the expression is not blank. - [LASTDATE](https://learnsql.cpetoday.com/functions/dax/lastdate.md): Returns the last date in the filter context, as a one-row table. - [LASTNONBLANK](https://learnsql.cpetoday.com/functions/dax/lastnonblank.md): Returns the last value in a column, in order, where the expression is not blank. - [STARTOFMONTH](https://learnsql.cpetoday.com/functions/dax/startofmonth.md): Returns the first date of the month that contains the dates in the filter context, as a one-row table. - [STARTOFQUARTER](https://learnsql.cpetoday.com/functions/dax/startofquarter.md): Returns the first date of the calendar quarter that contains the dates in the filter context, as a one-row table. - [STARTOFYEAR](https://learnsql.cpetoday.com/functions/dax/startofyear.md): Returns the first date of the year that contains the dates in the filter context, with an optional fiscal year end, as a one-row table. #### Time intelligence: shift and compare - [DATEADD](https://learnsql.cpetoday.com/functions/dax/dateadd.md): Shifts the dates in the filter context by a number of days, months, quarters or years. - [DATESBETWEEN](https://learnsql.cpetoday.com/functions/dax/datesbetween.md): Returns the dates from the start date to the end date, including both ends. - [DATESINPERIOD](https://learnsql.cpetoday.com/functions/dax/datesinperiod.md): Returns a set of dates that runs for a number of intervals from a start date, going back when the number is negative. - [NEXTDAY](https://learnsql.cpetoday.com/functions/dax/nextday.md): Returns the calendar day after each date in the filter context. - [NEXTMONTH](https://learnsql.cpetoday.com/functions/dax/nextmonth.md): Returns all the dates of the month after the first date in the filter context. - [NEXTQUARTER](https://learnsql.cpetoday.com/functions/dax/nextquarter.md): Returns all the dates of the calendar quarter after the first date in the filter context. - [NEXTYEAR](https://learnsql.cpetoday.com/functions/dax/nextyear.md): Returns all the dates of the year after the year in the filter context. - [PARALLELPERIOD](https://learnsql.cpetoday.com/functions/dax/parallelperiod.md): Returns the whole parallel month, quarter or year, shifted by a number of intervals. - [PREVIOUSDAY](https://learnsql.cpetoday.com/functions/dax/previousday.md): Returns the calendar day before each date in the filter context. - [PREVIOUSMONTH](https://learnsql.cpetoday.com/functions/dax/previousmonth.md): Returns all the dates of the month before the first date in the filter context. - [PREVIOUSQUARTER](https://learnsql.cpetoday.com/functions/dax/previousquarter.md): Returns all the dates of the calendar quarter before the first date in the filter context. - [PREVIOUSYEAR](https://learnsql.cpetoday.com/functions/dax/previousyear.md): Returns all the dates of the year before the year in the filter context. - [SAMEPERIODLASTYEAR](https://learnsql.cpetoday.com/functions/dax/sameperiodlastyear.md): Returns the same dates one year earlier, for use as a filter in CALCULATE. #### Time intelligence: to date - [DATESMTD](https://learnsql.cpetoday.com/functions/dax/datesmtd.md): Returns a table of dates from the first day of the month up to the last date in the filter context. - [DATESQTD](https://learnsql.cpetoday.com/functions/dax/datesqtd.md): Returns a table of dates from the start of the calendar quarter up to the last date in the filter context. - [DATESYTD](https://learnsql.cpetoday.com/functions/dax/datesytd.md): Returns a table of dates from the start of the year up to the last date in the filter context. - [TOTALMTD](https://learnsql.cpetoday.com/functions/dax/totalmtd.md): Evaluates an expression from the first day of the month up to the last date in the filter context. - [TOTALQTD](https://learnsql.cpetoday.com/functions/dax/totalqtd.md): Evaluates an expression from the start of the calendar quarter up to the last date in the filter context. - [TOTALYTD](https://learnsql.cpetoday.com/functions/dax/totalytd.md): Evaluates an expression from the start of the year up to the last date in the filter context. ### Power Query M: 226 functions - [Index of all Power Query M functions, in Markdown](https://learnsql.cpetoday.com/functions/m/md) #### Data sources - [Csv.Document](https://learnsql.cpetoday.com/functions/m/csv-document.md): Reads comma-separated (or other delimited) text from a file or binary content and returns a table. - [Excel.CurrentWorkbook](https://learnsql.cpetoday.com/functions/m/excel-currentworkbook.md): Reads the tables inside the Excel workbook that holds the query, so you can use data already typed in the same file. - [Excel.Workbook](https://learnsql.cpetoday.com/functions/m/excel-workbook.md): Reads an Excel file, given as binary content, and returns a table listing its sheets and tables, from which you pick the data. - [File.Contents](https://learnsql.cpetoday.com/functions/m/file-contents.md): Reads one file from disk and returns its content as binary, ready to be read by a file-type function. - [Folder.Contents](https://learnsql.cpetoday.com/functions/m/folder-contents.md): Lists everything in a folder, both files and subfolders, so you can see the folder's structure before you choose what to load. - [Folder.Files](https://learnsql.cpetoday.com/functions/m/folder-files.md): Lists the files in a folder, one row per file, with the file's name, extension, dates and its content as binary. - [Json.Document](https://learnsql.cpetoday.com/functions/m/json-document.md): Reads JSON text, from a file or a web response, and returns it as lists and records that you can expand into a table. - [Lines.FromBinary](https://learnsql.cpetoday.com/functions/m/lines-frombinary.md): Reads binary content, such as a plain text file, and returns it as a list of lines, one item for each line. - [OData.Feed](https://learnsql.cpetoday.com/functions/m/odata-feed.md): Connects to an OData service, a web data feed used by many business applications, and returns the tables it publishes. - [Sql.Database](https://learnsql.cpetoday.com/functions/m/sql-database.md): Connects to a SQL Server database and returns a navigation table from which you pick the tables or views to load. - [Sql.Databases](https://learnsql.cpetoday.com/functions/m/sql-databases.md): Lists the databases on a SQL Server, so you can see what is there before you connect to one. - [Value.NativeQuery](https://learnsql.cpetoday.com/functions/m/value-nativequery.md): Sends a SQL statement you write to a database connection and returns the result as a table. - [Web.Contents](https://learnsql.cpetoday.com/functions/m/web-contents.md): Fetches the content of a web address (a web page, file or API response) as binary, with optional query parameters, headers and timeout. - [Web.Page](https://learnsql.cpetoday.com/functions/m/web-page.md): Reads an HTML web page and returns the tables it contains, so you can pick the one you need. #### Date - [Date.AddDays](https://learnsql.cpetoday.com/functions/m/date-adddays.md): Adds a number of days to a date, or subtracts them when the number is negative. - [Date.AddMonths](https://learnsql.cpetoday.com/functions/m/date-addmonths.md): Adds a number of months to a date. When the day does not exist in the new month, the result is the last day of that month. - [Date.AddQuarters](https://learnsql.cpetoday.com/functions/m/date-addquarters.md): Adds a number of quarters to a date, where one quarter is three months. - [Date.AddWeeks](https://learnsql.cpetoday.com/functions/m/date-addweeks.md): Adds a number of weeks to a date, or subtracts them when the number is negative. - [Date.AddYears](https://learnsql.cpetoday.com/functions/m/date-addyears.md): Adds a number of years to a date. A February 29 that falls in a non-leap year becomes February 28. - [Date.Day](https://learnsql.cpetoday.com/functions/m/date-day.md): Returns the day of the month of a date or datetime, from 1 to 31. - [Date.DayOfWeek](https://learnsql.cpetoday.com/functions/m/date-dayofweek.md): Returns the day of the week of a date as a number, counted from the day you name as the first day of the week. - [Date.DayOfWeekName](https://learnsql.cpetoday.com/functions/m/date-dayofweekname.md): Returns the name of the day of the week of a date, such as "Thursday". - [Date.DayOfYear](https://learnsql.cpetoday.com/functions/m/date-dayofyear.md): Returns the position of a date in its year, from 1 on January 1 to 365 or 366 on December 31. - [Date.DaysInMonth](https://learnsql.cpetoday.com/functions/m/date-daysinmonth.md): Returns the number of days in the month that contains a date. - [Date.EndOfMonth](https://learnsql.cpetoday.com/functions/m/date-endofmonth.md): Returns the last day of the month that contains a date. - [Date.EndOfQuarter](https://learnsql.cpetoday.com/functions/m/date-endofquarter.md): Returns the last day of the calendar quarter that contains a date. - [Date.EndOfYear](https://learnsql.cpetoday.com/functions/m/date-endofyear.md): Returns December 31 of the year that contains a date. - [Date.From](https://learnsql.cpetoday.com/functions/m/date-from.md): Converts a value such as a date and time, or text, to a date with no time part. - [Date.FromText](https://learnsql.cpetoday.com/functions/m/date-fromtext.md): Reads a date from text, using a format and culture you specify. - [Date.IsLeapYear](https://learnsql.cpetoday.com/functions/m/date-isleapyear.md): Tests whether the year of a date is a leap year, which has 366 days. - [Date.Month](https://learnsql.cpetoday.com/functions/m/date-month.md): Returns the calendar month number of a date or datetime, from 1 for January to 12 for December. - [Date.MonthName](https://learnsql.cpetoday.com/functions/m/date-monthname.md): Returns the name of the month of a date, such as "October". - [Date.QuarterOfYear](https://learnsql.cpetoday.com/functions/m/date-quarterofyear.md): Returns the calendar quarter of a date, from 1 for January to March through 4 for October to December. - [Date.StartOfMonth](https://learnsql.cpetoday.com/functions/m/date-startofmonth.md): Returns the first day of the month that contains a date. - [Date.StartOfQuarter](https://learnsql.cpetoday.com/functions/m/date-startofquarter.md): Returns the first day of the calendar quarter that contains a date. - [Date.StartOfWeek](https://learnsql.cpetoday.com/functions/m/date-startofweek.md): Returns the first day of the week that contains a date, using the day you name as the first day of the week. - [Date.StartOfYear](https://learnsql.cpetoday.com/functions/m/date-startofyear.md): Returns January 1 of the year that contains a date. - [Date.ToText](https://learnsql.cpetoday.com/functions/m/date-totext.md): Formats a date as text using a format string, such as "yyyy-MM-dd" or "MMM yyyy". - [Date.WeekOfYear](https://learnsql.cpetoday.com/functions/m/date-weekofyear.md): Returns the week number of a date within its year. - [Date.Year](https://learnsql.cpetoday.com/functions/m/date-year.md): Returns the calendar year of a date or datetime. #### Date and time - [DateTime.Date](https://learnsql.cpetoday.com/functions/m/datetime-date.md): Returns the date part of a date and time, with the time removed. - [DateTime.FixedLocalNow](https://learnsql.cpetoday.com/functions/m/datetime-fixedlocalnow.md): Returns the current local date and time as one fixed value for the whole evaluation of a query. - [DateTime.From](https://learnsql.cpetoday.com/functions/m/datetime-from.md): Converts a value, such as a date, a time zone value or text, to a date and time. - [DateTime.LocalNow](https://learnsql.cpetoday.com/functions/m/datetime-localnow.md): Returns the current date and time in the local time zone of the machine running the query. - [DateTime.Time](https://learnsql.cpetoday.com/functions/m/datetime-time.md): Returns the time part of a date and time, with the date removed. - [DateTime.ToText](https://learnsql.cpetoday.com/functions/m/datetime-totext.md): Formats a date and time as text using a format string, such as "yyyy-MM-dd HH:mm". - [DateTimeZone.SwitchZone](https://learnsql.cpetoday.com/functions/m/datetimezone-switchzone.md): Moves a datetime with a time zone to a different fixed zone offset, given in hours and optional minutes. - [DateTimeZone.UtcNow](https://learnsql.cpetoday.com/functions/m/datetimezone-utcnow.md): Returns the current date and time in UTC, the universal reference time, with a zone offset of zero. - [Time.From](https://learnsql.cpetoday.com/functions/m/time-from.md): Converts a value, such as a datetime or text, to a time of day. #### Duration - [Duration.Days](https://learnsql.cpetoday.com/functions/m/duration-days.md): Returns the whole number of days in a duration, such as the days between two dates. - [Duration.From](https://learnsql.cpetoday.com/functions/m/duration-from.md): Converts a number to a duration, where the number is read as days. - [Duration.Hours](https://learnsql.cpetoday.com/functions/m/duration-hours.md): Returns the hours part of a duration, from 0 to 23, not counting whole days. - [Duration.Minutes](https://learnsql.cpetoday.com/functions/m/duration-minutes.md): Returns the minutes part of a duration, from 0 to 59, after whole hours are taken out. - [Duration.TotalDays](https://learnsql.cpetoday.com/functions/m/duration-totaldays.md): Returns the whole duration as a number of days, including any part of a day as a decimal. - [Duration.TotalHours](https://learnsql.cpetoday.com/functions/m/duration-totalhours.md): Returns the whole duration as a number of hours, including any part of an hour as a decimal. #### Errors and types - [Error.Record](https://learnsql.cpetoday.com/functions/m/error-record.md): Creates an error value with a reason, a message and detail, which you raise with the error keyword. - [Type.TableSchema](https://learnsql.cpetoday.com/functions/m/type-tableschema.md): Returns a table that describes the columns of a table type: their names and their types. - [Value.Is](https://learnsql.cpetoday.com/functions/m/value-is.md): Tests whether a value is of a given type, such as number or text, and returns true or false. - [Value.Type](https://learnsql.cpetoday.com/functions/m/value-type.md): Returns the type of a value, such as number, text or a table type, so you can see what Power Query thinks it holds. #### Language - [each](https://learnsql.cpetoday.com/functions/m/each.md): A short way to write a function that takes one argument, named _, and uses it in an expression. - [error](https://learnsql.cpetoday.com/functions/m/error.md): Raises an error on purpose, using an error record, so that the query stops with a clear message. - [if](https://learnsql.cpetoday.com/functions/m/if.md): Chooses one of two values based on a test, and always needs an else part. - [let](https://learnsql.cpetoday.com/functions/m/let.md): Names a series of steps so that each one can be read, and then gives the result in the in clause. - [otherwise](https://learnsql.cpetoday.com/functions/m/otherwise.md): Gives a fallback value to use when the expression after try fails. - [try](https://learnsql.cpetoday.com/functions/m/try.md): Evaluates an expression and, instead of failing, returns a record that says whether it worked and what the value or error was. - [type](https://learnsql.cpetoday.com/functions/m/type.md): Names a type, such as number, text or date, so you can tell Power Query what a value or column holds. #### List: pick and build - [List.Contains](https://learnsql.cpetoday.com/functions/m/list-contains.md): Returns true if a value appears anywhere in a list. - [List.ContainsAny](https://learnsql.cpetoday.com/functions/m/list-containsany.md): Returns true if any of several values appears in a list. - [List.Dates](https://learnsql.cpetoday.com/functions/m/list-dates.md): Builds a list of dates: a start date, a number of dates, and the gap between them. - [List.Difference](https://learnsql.cpetoday.com/functions/m/list-difference.md): Returns the items in the first list that are not in the second list. - [List.First](https://learnsql.cpetoday.com/functions/m/list-first.md): Returns the first item in a list. - [List.FirstN](https://learnsql.cpetoday.com/functions/m/list-firstn.md): Returns the first few items of a list, either a set number or all the items at the start that pass a test. - [List.Intersect](https://learnsql.cpetoday.com/functions/m/list-intersect.md): Returns the values that appear in every one of the lists you give. - [List.Last](https://learnsql.cpetoday.com/functions/m/list-last.md): Returns the last item in a list. - [List.LastN](https://learnsql.cpetoday.com/functions/m/list-lastn.md): Returns the last few items of a list, either a set number or all the items at the end that pass a test. - [List.Numbers](https://learnsql.cpetoday.com/functions/m/list-numbers.md): Builds a list of numbers: a starting number, how many numbers, and an optional step between them. - [List.Positions](https://learnsql.cpetoday.com/functions/m/list-positions.md): Returns the position number of every item in a list, starting from zero. - [List.Range](https://learnsql.cpetoday.com/functions/m/list-range.md): Returns a section of a list, starting at a position and running for a number of items. - [List.Repeat](https://learnsql.cpetoday.com/functions/m/list-repeat.md): Repeats a list a set number of times and joins the copies into one list. - [List.Skip](https://learnsql.cpetoday.com/functions/m/list-skip.md): Returns a list with the first items removed, either a set number or the run of items at the start that pass a test. - [List.Union](https://learnsql.cpetoday.com/functions/m/list-union.md): Combines several lists into one list of the values that appear in any of them, with duplicates removed. #### List: summarize - [List.AllTrue](https://learnsql.cpetoday.com/functions/m/list-alltrue.md): Returns true only if every item in a list of logical values is true. - [List.AnyTrue](https://learnsql.cpetoday.com/functions/m/list-anytrue.md): Returns true if at least one item in a list of logical values is true. - [List.Average](https://learnsql.cpetoday.com/functions/m/list-average.md): Returns the mean (average) of the numbers in a list. - [List.Count](https://learnsql.cpetoday.com/functions/m/list-count.md): Returns how many items are in a list, including any nulls. - [List.Max](https://learnsql.cpetoday.com/functions/m/list-max.md): Returns the largest value in a list. - [List.Median](https://learnsql.cpetoday.com/functions/m/list-median.md): Returns the middle value of a list of numbers, half of the items above it and half below. - [List.Min](https://learnsql.cpetoday.com/functions/m/list-min.md): Returns the smallest value in a list. - [List.Product](https://learnsql.cpetoday.com/functions/m/list-product.md): Multiplies all the numbers in a list together. - [List.StandardDeviation](https://learnsql.cpetoday.com/functions/m/list-standarddeviation.md): Returns a standard deviation, a measure of how far the numbers in a list spread out from their average. - [List.Sum](https://learnsql.cpetoday.com/functions/m/list-sum.md): Adds up all the numbers in a list. #### List: transform and filter - [List.Accumulate](https://learnsql.cpetoday.com/functions/m/list-accumulate.md): Walks through a list one item at a time, carrying a running value from each step to the next. - [List.Buffer](https://learnsql.cpetoday.com/functions/m/list-buffer.md): Loads a list into memory once, so later steps reuse the same values instead of recalculating them. - [List.Combine](https://learnsql.cpetoday.com/functions/m/list-combine.md): Joins a list of lists into one list, end to end. - [List.Distinct](https://learnsql.cpetoday.com/functions/m/list-distinct.md): Returns a list with duplicate values removed, keeping one copy of each value. - [List.Generate](https://learnsql.cpetoday.com/functions/m/list-generate.md): Builds a list by starting from an initial value and repeatedly applying a rule until a condition fails. - [List.RemoveNulls](https://learnsql.cpetoday.com/functions/m/list-removenulls.md): Removes every null (empty) item from a list. - [List.ReplaceValue](https://learnsql.cpetoday.com/functions/m/list-replacevalue.md): Replaces each item that matches a value with a new value, using a replacer function to decide what counts as a match. - [List.Reverse](https://learnsql.cpetoday.com/functions/m/list-reverse.md): Returns the items of a list in the opposite order. - [List.Select](https://learnsql.cpetoday.com/functions/m/list-select.md): Keeps only the items in a list for which a condition is true. - [List.Sort](https://learnsql.cpetoday.com/functions/m/list-sort.md): Returns the items of a list in order, smallest to largest by default. - [List.Transform](https://learnsql.cpetoday.com/functions/m/list-transform.md): Applies a function to every item in a list and returns a new list of the results. - [List.Zip](https://learnsql.cpetoday.com/functions/m/list-zip.md): Pairs up items from several lists by position, so the first items go together, then the second items, and so on. #### Number - [Number.Abs](https://learnsql.cpetoday.com/functions/m/number-abs.md): Returns the absolute value of a number, which is its size without its sign. - [Number.From](https://learnsql.cpetoday.com/functions/m/number-from.md): Converts a value of another type, such as text or a logical, to a number. - [Number.FromText](https://learnsql.cpetoday.com/functions/m/number-fromtext.md): Reads a number from text, using the separators of the culture you name. - [Number.IntegerDivide](https://learnsql.cpetoday.com/functions/m/number-integerdivide.md): Divides one number by another and keeps only the whole number of times it fits. - [Number.IsNaN](https://learnsql.cpetoday.com/functions/m/number-isnan.md): Tests whether a number is NaN, which stands for "not a number" and comes from an invalid calculation. - [Number.Mod](https://learnsql.cpetoday.com/functions/m/number-mod.md): Returns the remainder left after dividing one number by another. - [Number.Power](https://learnsql.cpetoday.com/functions/m/number-power.md): Raises a number to a power, such as a growth factor for compounding over several periods. - [Number.Round](https://learnsql.cpetoday.com/functions/m/number-round.md): Rounds a number to a set number of decimal places, using a rounding rule you can choose. - [Number.RoundDown](https://learnsql.cpetoday.com/functions/m/number-rounddown.md): Rounds a number down to the next value at a set number of digits. - [Number.RoundUp](https://learnsql.cpetoday.com/functions/m/number-roundup.md): Rounds a number up to the next value at a set number of digits. - [Number.Sign](https://learnsql.cpetoday.com/functions/m/number-sign.md): Returns -1 for a negative number, 1 for a positive number and 0 for zero. - [Number.Sqrt](https://learnsql.cpetoday.com/functions/m/number-sqrt.md): Returns the square root of a number. - [Number.ToText](https://learnsql.cpetoday.com/functions/m/number-totext.md): Formats a number as text, using a format string such as a fixed number of decimal places or a currency style. #### Record - [Record.AddField](https://learnsql.cpetoday.com/functions/m/record-addfield.md): Returns a copy of a record with one new field added. - [Record.Combine](https://learnsql.cpetoday.com/functions/m/record-combine.md): Joins a list of records into one record, with all their fields together. - [Record.Field](https://learnsql.cpetoday.com/functions/m/record-field.md): Returns the value of one named field in a record. - [Record.FieldNames](https://learnsql.cpetoday.com/functions/m/record-fieldnames.md): Returns the names of all the fields in a record, as a list of text. - [Record.FieldValues](https://learnsql.cpetoday.com/functions/m/record-fieldvalues.md): Returns the values of all the fields in a record, as a list. - [Record.FromTable](https://learnsql.cpetoday.com/functions/m/record-fromtable.md): Builds a record from a table with a Name column and a Value column. - [Record.HasFields](https://learnsql.cpetoday.com/functions/m/record-hasfields.md): Returns true if a record contains all of the fields you name. - [Record.RemoveFields](https://learnsql.cpetoday.com/functions/m/record-removefields.md): Returns a copy of a record without the fields you name. - [Record.RenameFields](https://learnsql.cpetoday.com/functions/m/record-renamefields.md): Returns a copy of a record with some of its fields given new names. - [Record.SelectFields](https://learnsql.cpetoday.com/functions/m/record-selectfields.md): Returns a copy of a record that keeps only the fields you name. - [Record.ToTable](https://learnsql.cpetoday.com/functions/m/record-totable.md): Converts a record into a two-column table, with one row for each field, a Name column and a Value column. - [Record.TransformFields](https://learnsql.cpetoday.com/functions/m/record-transformfields.md): Returns a copy of a record in which chosen fields have been changed by a function. #### Splitters and combiners - [Combiner.CombineTextByDelimiter](https://learnsql.cpetoday.com/functions/m/combiner-combinetextbydelimiter.md): Returns a combiner that joins several text values into one, with a chosen delimiter between them. - [Comparer.OrdinalIgnoreCase](https://learnsql.cpetoday.com/functions/m/comparer-ordinalignorecase.md): A comparer that treats text as equal when the letters match, ignoring capital and lower case. - [Replacer.ReplaceText](https://learnsql.cpetoday.com/functions/m/replacer-replacetext.md): Replaces part of a text value, every place where the old text appears, with the new text. - [Replacer.ReplaceValue](https://learnsql.cpetoday.com/functions/m/replacer-replacevalue.md): Replaces a whole value with a new value when it matches exactly, such as turning "N/A" into null. - [Splitter.SplitByNothing](https://learnsql.cpetoday.com/functions/m/splitter-splitbynothing.md): Returns a splitter that does not split at all: the whole text stays as one piece. - [Splitter.SplitTextByDelimiter](https://learnsql.cpetoday.com/functions/m/splitter-splittextbydelimiter.md): Returns a splitter that cuts text into pieces wherever a chosen delimiter appears, such as a comma, a tab or a semicolon. - [Splitter.SplitTextByEachDelimiter](https://learnsql.cpetoday.com/functions/m/splitter-splittextbyeachdelimiter.md): Returns a splitter that cuts text at each of several delimiters in turn, using a list of delimiter values. - [Splitter.SplitTextByPositions](https://learnsql.cpetoday.com/functions/m/splitter-splittextbypositions.md): Returns a splitter that cuts text at fixed character positions, for fixed-width files and layouts without delimiters. #### Table: build and convert - [Table.FromColumns](https://learnsql.cpetoday.com/functions/m/table-fromcolumns.md): Builds a table from a list of columns, where each column is a list of values. - [Table.FromList](https://learnsql.cpetoday.com/functions/m/table-fromlist.md): Turns a list into a table with one column, or splits each item into several columns with a splitter. - [Table.FromRecords](https://learnsql.cpetoday.com/functions/m/table-fromrecords.md): Builds a table from a list of records, where each record is one row with named fields. - [Table.FromRows](https://learnsql.cpetoday.com/functions/m/table-fromrows.md): Builds a table from a list of rows, where each row is itself a list of values. - [Table.ToColumns](https://learnsql.cpetoday.com/functions/m/table-tocolumns.md): Converts a table into a list of columns, where each column is a list of its values. - [Table.ToList](https://learnsql.cpetoday.com/functions/m/table-tolist.md): Joins each row into one text value, using a combiner you choose, and returns the list of those values. - [Table.ToRecords](https://learnsql.cpetoday.com/functions/m/table-torecords.md): Converts a table into a list of records, one record for each row, with the column names as field names. - [Table.ToRows](https://learnsql.cpetoday.com/functions/m/table-torows.md): Converts a table into a list of rows, where each row is a list of its values. #### Table: columns - [Table.AddColumn](https://learnsql.cpetoday.com/functions/m/table-addcolumn.md): Adds a new column whose value is calculated for each row. - [Table.AddIndexColumn](https://learnsql.cpetoday.com/functions/m/table-addindexcolumn.md): Adds a column that numbers the rows, from 0 by default, or from a start value and step you choose. - [Table.ColumnCount](https://learnsql.cpetoday.com/functions/m/table-columncount.md): Returns how many columns a table has. - [Table.ColumnNames](https://learnsql.cpetoday.com/functions/m/table-columnnames.md): Returns the column names of a table as a list. - [Table.CombineColumns](https://learnsql.cpetoday.com/functions/m/table-combinecolumns.md): Joins several columns into one new column, using a combiner such as Combiner.CombineTextByDelimiter. - [Table.DuplicateColumn](https://learnsql.cpetoday.com/functions/m/table-duplicatecolumn.md): Copies a column into a new column with a name you choose. - [Table.PrefixColumns](https://learnsql.cpetoday.com/functions/m/table-prefixcolumns.md): Adds a prefix and a dot to every column name in the table. - [Table.RemoveColumns](https://learnsql.cpetoday.com/functions/m/table-removecolumns.md): Deletes one or more columns from a table. - [Table.RenameColumns](https://learnsql.cpetoday.com/functions/m/table-renamecolumns.md): Changes the names of columns, leaving the values as they are. - [Table.ReorderColumns](https://learnsql.cpetoday.com/functions/m/table-reordercolumns.md): Moves the named columns to the front, in the order you list them. - [Table.Schema](https://learnsql.cpetoday.com/functions/m/table-schema.md): Returns a table that describes each column, including its name, its type and whether it allows blanks. - [Table.SelectColumns](https://learnsql.cpetoday.com/functions/m/table-selectcolumns.md): Keeps only the columns you name, in the order you name them. - [Table.SplitColumn](https://learnsql.cpetoday.com/functions/m/table-splitcolumn.md): Splits one column into several columns, by a delimiter, by position or by a splitter you define. - [Table.TransformColumnNames](https://learnsql.cpetoday.com/functions/m/table-transformcolumnnames.md): Changes every column name with one function, such as trimming spaces or changing capital letters. - [Table.TransformColumns](https://learnsql.cpetoday.com/functions/m/table-transformcolumns.md): Changes the values in one or more columns, using a function you choose for each value. - [Table.TransformColumnTypes](https://learnsql.cpetoday.com/functions/m/table-transformcolumntypes.md): Sets the data type of one or more columns, such as text, number, date or whole number. - [Table.TransformRows](https://learnsql.cpetoday.com/functions/m/table-transformrows.md): Applies a function to each row, given as a record, and returns a list of the results. #### Table: combine and join - [Table.Combine](https://learnsql.cpetoday.com/functions/m/table-combine.md): Stacks several tables with matching columns into one longer table. - [Table.ExpandListColumn](https://learnsql.cpetoday.com/functions/m/table-expandlistcolumn.md): Turns each item in a column of lists into a row of its own. - [Table.ExpandRecordColumn](https://learnsql.cpetoday.com/functions/m/table-expandrecordcolumn.md): Opens a column of records, such as the details inside a JSON file, and turns the chosen fields into columns. - [Table.ExpandTableColumn](https://learnsql.cpetoday.com/functions/m/table-expandtablecolumn.md): Opens a column of nested tables and turns the chosen columns into normal columns, one row for each nested row. - [Table.Join](https://learnsql.cpetoday.com/functions/m/table-join.md): Joins two tables on matching key columns and returns one flat table with the columns of both. - [Table.NestedJoin](https://learnsql.cpetoday.com/functions/m/table-nestedjoin.md): Matches rows from two tables and adds a column that holds the matching rows from the second table, which you can then expand. #### Table: headers and shape - [Table.Buffer](https://learnsql.cpetoday.com/functions/m/table-buffer.md): Loads the table into memory once so that later steps reuse it, and fixes the row order from that point. - [Table.DemoteHeaders](https://learnsql.cpetoday.com/functions/m/table-demoteheaders.md): Turns the current column headers into the first row of data and gives the columns generic names. - [Table.Pivot](https://learnsql.cpetoday.com/functions/m/table-pivot.md): Turns the values of one column into new column headers, filled with the values from another column. - [Table.Profile](https://learnsql.cpetoday.com/functions/m/table-profile.md): Returns a table of statistics about each column, such as counts of blank and distinct values. - [Table.PromoteHeaders](https://learnsql.cpetoday.com/functions/m/table-promoteheaders.md): Uses the first row of the table as the column headers. - [Table.Transpose](https://learnsql.cpetoday.com/functions/m/table-transpose.md): Swaps rows and columns, so each column becomes a row and each row becomes a column. - [Table.Unpivot](https://learnsql.cpetoday.com/functions/m/table-unpivot.md): Turns several columns into rows, with one row for each combination of a column and its value. - [Table.UnpivotOtherColumns](https://learnsql.cpetoday.com/functions/m/table-unpivotothercolumns.md): Turns every column except the ones you keep into rows. #### Table: rows - [Table.Distinct](https://learnsql.cpetoday.com/functions/m/table-distinct.md): Removes duplicate rows, or keeps the first row for each distinct value in the columns you name. - [Table.FirstN](https://learnsql.cpetoday.com/functions/m/table-firstn.md): Keeps the first rows, either a set number or while a condition holds. - [Table.Group](https://learnsql.cpetoday.com/functions/m/table-group.md): Groups rows by one or more key columns and calculates totals or counts for each group. - [Table.IsEmpty](https://learnsql.cpetoday.com/functions/m/table-isempty.md): Returns true if the table has no rows, and false if it has at least one. - [Table.LastN](https://learnsql.cpetoday.com/functions/m/table-lastn.md): Keeps the last rows, either a set number or while a condition holds from the bottom. - [Table.Max](https://learnsql.cpetoday.com/functions/m/table-max.md): Returns the row that has the highest value in a column. - [Table.Min](https://learnsql.cpetoday.com/functions/m/table-min.md): Returns the row that has the lowest value in a column. - [Table.RemoveFirstN](https://learnsql.cpetoday.com/functions/m/table-removefirstn.md): Removes the first rows, by a count or until a test stops being true. - [Table.RemoveLastN](https://learnsql.cpetoday.com/functions/m/table-removelastn.md): Removes the last rows, by a count or while a test holds from the bottom up. - [Table.RemoveMatchingRows](https://learnsql.cpetoday.com/functions/m/table-removematchingrows.md): Removes every row that matches one of the sample records in a list. - [Table.RemoveRows](https://learnsql.cpetoday.com/functions/m/table-removerows.md): Removes a block of rows by position, starting at the row position you give. - [Table.RemoveRowsWithErrors](https://learnsql.cpetoday.com/functions/m/table-removerowswitherrors.md): Drops every row that has an error in one or more of the columns you check. - [Table.RowCount](https://learnsql.cpetoday.com/functions/m/table-rowcount.md): Returns how many rows a table has. - [Table.SelectRows](https://learnsql.cpetoday.com/functions/m/table-selectrows.md): Keeps only the rows where a condition is true. - [Table.SelectRowsWithErrors](https://learnsql.cpetoday.com/functions/m/table-selectrowswitherrors.md): Keeps only the rows that contain an error in one or more columns. - [Table.Skip](https://learnsql.cpetoday.com/functions/m/table-skip.md): Drops the first rows, either a set number or while a condition holds, and keeps the rest. - [Table.Sort](https://learnsql.cpetoday.com/functions/m/table-sort.md): Puts the rows in order by one or more columns, ascending or descending. #### Table: values and fill - [Table.FillDown](https://learnsql.cpetoday.com/functions/m/table-filldown.md): Fills blank cells in the listed columns with the value from the row above. - [Table.FillUp](https://learnsql.cpetoday.com/functions/m/table-fillup.md): Fills blank cells in the listed columns with the value from the row below. - [Table.ReplaceErrorValues](https://learnsql.cpetoday.com/functions/m/table-replaceerrorvalues.md): Replaces error values in the columns you name with a value you choose. - [Table.ReplaceValue](https://learnsql.cpetoday.com/functions/m/table-replacevalue.md): Replaces every occurrence of one value with another, in the columns you list. #### Text - [Text.AfterDelimiter](https://learnsql.cpetoday.com/functions/m/text-afterdelimiter.md): Returns the part of a text value that comes after a delimiter. - [Text.BeforeDelimiter](https://learnsql.cpetoday.com/functions/m/text-beforedelimiter.md): Returns the part of a text value that comes before a delimiter. - [Text.BetweenDelimiters](https://learnsql.cpetoday.com/functions/m/text-betweendelimiters.md): Returns the text that sits between a start delimiter and an end delimiter. - [Text.Clean](https://learnsql.cpetoday.com/functions/m/text-clean.md): Removes non-printing control characters, such as line breaks and tabs, from a text value. - [Text.Combine](https://learnsql.cpetoday.com/functions/m/text-combine.md): Joins a list of text values into one text value, with a separator between them. - [Text.Contains](https://learnsql.cpetoday.com/functions/m/text-contains.md): Tests whether a text value contains a given piece of text. - [Text.End](https://learnsql.cpetoday.com/functions/m/text-end.md): Returns the last characters of a text value. - [Text.EndsWith](https://learnsql.cpetoday.com/functions/m/text-endswith.md): Tests whether a text value ends with a given piece of text. - [Text.Format](https://learnsql.cpetoday.com/functions/m/text-format.md): Builds a text message by putting values into numbered placeholders. - [Text.From](https://learnsql.cpetoday.com/functions/m/text-from.md): Converts a number, date, time or logical value to text. - [Text.Insert](https://learnsql.cpetoday.com/functions/m/text-insert.md): Inserts a piece of text into a value at a zero-based position. - [Text.Length](https://learnsql.cpetoday.com/functions/m/text-length.md): Counts the characters in a text value. - [Text.Lower](https://learnsql.cpetoday.com/functions/m/text-lower.md): Converts all letters in a text value to lower case. - [Text.Middle](https://learnsql.cpetoday.com/functions/m/text-middle.md): Returns characters from the middle of a text value, starting at a zero-based position. - [Text.PadEnd](https://learnsql.cpetoday.com/functions/m/text-padend.md): Adds characters to the end of a text value until it reaches a set length. - [Text.PadStart](https://learnsql.cpetoday.com/functions/m/text-padstart.md): Adds characters to the start of a text value until it reaches a set length. - [Text.PositionOf](https://learnsql.cpetoday.com/functions/m/text-positionof.md): Returns the zero-based position where a piece of text first (or last) appears in a value. - [Text.Proper](https://learnsql.cpetoday.com/functions/m/text-proper.md): Capitalizes the first letter of each word and lower cases the rest. - [Text.Remove](https://learnsql.cpetoday.com/functions/m/text-remove.md): Removes every occurrence of the listed characters from a text value. - [Text.Replace](https://learnsql.cpetoday.com/functions/m/text-replace.md): Replaces every occurrence of one piece of text with another. - [Text.Reverse](https://learnsql.cpetoday.com/functions/m/text-reverse.md): Returns the characters of a text value in reverse order. - [Text.Select](https://learnsql.cpetoday.com/functions/m/text-select.md): Keeps only the listed characters from a text value and removes the rest. - [Text.Split](https://learnsql.cpetoday.com/functions/m/text-split.md): Splits a text value into a list of pieces at each separator. - [Text.Start](https://learnsql.cpetoday.com/functions/m/text-start.md): Returns the first characters of a text value. - [Text.StartsWith](https://learnsql.cpetoday.com/functions/m/text-startswith.md): Tests whether a text value begins with a given piece of text. - [Text.ToList](https://learnsql.cpetoday.com/functions/m/text-tolist.md): Splits a text value into a list with one character in each item. - [Text.Trim](https://learnsql.cpetoday.com/functions/m/text-trim.md): Removes spaces, tabs and line breaks from the start and end of a text value. - [Text.TrimEnd](https://learnsql.cpetoday.com/functions/m/text-trimend.md): Removes spaces from the end of a text value only. - [Text.TrimStart](https://learnsql.cpetoday.com/functions/m/text-trimstart.md): Removes spaces from the start of a text value only, leaving the end alone. - [Text.Upper](https://learnsql.cpetoday.com/functions/m/text-upper.md): Converts all letters in a text value to upper case. ## Tools Free, in-browser tools (no sign-in, nothing stored on the server). Each page explains the tool and links to the functions it uses: - [Chart lab: turn a SQL query into a chart](https://learnsql.cpetoday.com/tools/chart-lab): Pick a business question or write your own SELECT, run it on the sample databases, and see the result as a chart, a table and the SQL. - [Date table generator](https://learnsql.cpetoday.com/tools/date-table): Build a date table with calendar and fiscal columns, week numbers and working-day flags as Power Query M, DAX, T-SQL or MariaDB SQL. - [DAX formatter and formula checker](https://learnsql.cpetoday.com/tools/dax-formatter): Format a DAX formula into a standard layout and check it for common mistakes, all in your browser. - [Visual SQL query builder](https://learnsql.cpetoday.com/tools/sql-builder): Pick a database and tables, add joins, columns, filters and totals, and get the SQL for SQL Server or MariaDB, with a plain-English explanation and a live run. - [Data stories: six charts from real business data](https://learnsql.cpetoday.com/tools/data-stories): Six short stories, each with a live chart built from a real query on the sample data and the SQL underneath. - [Measure builder](https://learnsql.cpetoday.com/tools/measure-builder): Pick a business question, map its columns to your model, and get the DAX measure, an equivalent T-SQL query and a Power Query step. - [Power Query M explainer](https://learnsql.cpetoday.com/tools/m-explainer): Paste a Power Query M query and get every step explained in plain English, with reference links, folding hints and a re-indent button. - [SQL recipes for accounting and finance](https://learnsql.cpetoday.com/tools/sql-recipes): Ready-to-run SQL for month-end close, receivables, payables, cash, margin, budgets, controls, payroll, nonprofit and inventory, on live sample data. - [SQL formatter and query outline](https://learnsql.cpetoday.com/tools/sql-formatter): Format a SQL Server or MariaDB query, see in plain words what it does, and get a list of common mistakes to check. ## Optional - [Sample databases](https://learnsql.cpetoday.com/databases): the databases the guides use - [Sitemap](https://learnsql.cpetoday.com/sitemap.xml)