Power QueryExercises

Simulator compatibility

Exercises run in an independently implemented educational simulator that supports a documented subset of Power Query M. It is not Microsoft's engine. When something isn't supported, the simulator says so instead of guessing. This page is generated from the engine itself (version 0.1.0).

Expected results are based on Microsoft's published Power Query M documentation and our own reviewed examples.

Supported functions (296)

Binary (1)

Binary.Length

Character (2)

Character.FromNumber, Character.ToNumber

Combiner (1)

Combiner.CombineTextByDelimiter

Comparer (4)

Comparer.Equals, Comparer.FromCulture, Comparer.Ordinal, Comparer.OrdinalIgnoreCase

Constructors (6)

#date, #datetime, #datetimezone, #duration, #table, #time

Csv (1)

Csv.Document

Currency (1)

Currency.From

Date (27)

Date.AddDays, Date.AddMonths, Date.AddQuarters, Date.AddWeeks, Date.AddYears, Date.Day, Date.DayOfWeek, Date.DayOfWeekName, Date.DayOfYear, Date.DaysInMonth, Date.EndOfMonth, Date.EndOfQuarter, Date.EndOfYear, Date.From, Date.FromText, Date.IsInPreviousNMonths, Date.IsLeapYear, Date.Month, Date.MonthName, Date.QuarterOfYear, Date.StartOfMonth, Date.StartOfQuarter, Date.StartOfWeek, Date.StartOfYear, Date.ToText, Date.WeekOfYear, Date.Year

DateTime (8)

DateTime.Date, DateTime.FixedLocalNow, DateTime.From, DateTime.FromText, DateTime.LocalNow, DateTime.Time, DateTime.ToText, DateTime.UtcNow

DateTimeZone (4)

DateTimeZone.FixedUtcNow, DateTimeZone.From, DateTimeZone.LocalNow, DateTimeZone.UtcNow

Decimal (1)

Decimal.From

Double (1)

Double.From

Duration (10)

Duration.Days, Duration.From, Duration.FromText, Duration.Hours, Duration.Minutes, Duration.Seconds, Duration.TotalDays, Duration.TotalHours, Duration.TotalMinutes, Duration.TotalSeconds

Error (1)

Error.Record

Excel (1)

Excel.Workbook

Int32 (1)

Int32.From

Int64 (1)

Int64.From

Json (1)

Json.Document

Lines (2)

Lines.FromBinary, Lines.FromText

List (51)

List.Accumulate, List.AllTrue, List.AnyTrue, List.Average, List.Buffer, List.Combine, List.Contains, List.ContainsAll, List.ContainsAny, List.Count, List.Dates, List.Difference, List.Distinct, List.First, List.FirstN, List.Generate, List.Intersect, List.IsEmpty, List.Last, List.LastN, List.MatchesAll, List.MatchesAny, List.Max, List.MaxN, List.Median, List.Min, List.MinN, List.Mode, List.NonNullCount, List.Numbers, List.PositionOf, List.Product, List.Range, List.RemoveFirstN, List.RemoveItems, List.RemoveLastN, List.RemoveMatchingItems, List.RemoveNulls, List.Repeat, List.ReplaceValue, List.Reverse, List.Select, List.Skip, List.Sort, List.Split, List.StandardDeviation, List.Sum, List.Transform, List.TransformMany, List.Union, List.Zip

Logical (4)

Logical.From, Logical.FromText, Logical.Not, Logical.ToText

Number (22)

Number.Abs, Number.Exp, Number.From, Number.FromText, Number.IntegerDivide, Number.IsEven, Number.IsNaN, Number.IsOdd, Number.Ln, Number.Log, Number.Log10, Number.Mod, Number.Power, Number.Random, Number.Round, Number.RoundAwayFromZero, Number.RoundDown, Number.RoundTowardZero, Number.RoundUp, Number.Sign, Number.Sqrt, Number.ToText

Percentage (1)

Percentage.From

Record (15)

Record.AddField, Record.Combine, Record.Field, Record.FieldCount, Record.FieldNames, Record.FieldOrDefault, Record.FieldValues, Record.FromList, Record.HasFields, Record.RemoveFields, Record.RenameFields, Record.SelectFields, Record.ToList, Record.ToTable, Record.TransformFields

Replacer (2)

Replacer.ReplaceText, Replacer.ReplaceValue

Single (1)

Single.From

Splitter (6)

Splitter.SplitByNothing, Splitter.SplitTextByAnyDelimiter, Splitter.SplitTextByDelimiter, Splitter.SplitTextByEachDelimiter, Splitter.SplitTextByLengths, Splitter.SplitTextByPositions

Table (72)

Table.AddColumn, Table.AddIndexColumn, Table.Buffer, Table.Column, Table.ColumnCount, Table.ColumnNames, Table.ColumnsOfType, Table.Combine, Table.CombineColumns, Table.Contains, Table.DemoteHeaders, Table.Distinct, Table.DuplicateColumn, Table.ExpandListColumn, Table.ExpandRecordColumn, Table.ExpandTableColumn, Table.FillDown, Table.FillUp, Table.First, Table.FirstN, Table.FromColumns, Table.FromList, Table.FromRecords, Table.FromRows, Table.Group, Table.HasColumns, Table.InsertRows, Table.IsDistinct, Table.IsEmpty, Table.Join, Table.Last, Table.LastN, Table.MatchesAllRows, Table.MatchesAnyRows, Table.Max, Table.Min, Table.NestedJoin, Table.Pivot, Table.PositionOf, Table.PrefixColumns, Table.Profile, Table.PromoteHeaders, Table.Range, Table.RemoveColumns, Table.RemoveFirstN, Table.RemoveLastN, Table.RemoveRowsWithErrors, Table.RenameColumns, Table.ReorderColumns, Table.Repeat, Table.ReplaceErrorValues, Table.ReplaceValue, Table.ReverseRows, Table.RowCount, Table.Schema, Table.SelectColumns, Table.SelectRows, Table.SelectRowsWithErrors, Table.SingleRow, Table.Skip, Table.Sort, Table.SplitColumn, Table.ToColumns, Table.ToList, Table.ToRecords, Table.ToRows, Table.TransformColumnNames, Table.TransformColumnTypes, Table.TransformColumns, Table.Transpose, Table.Unpivot, Table.UnpivotOtherColumns

Text (37)

Text.AfterDelimiter, Text.At, Text.BeforeDelimiter, Text.BetweenDelimiters, Text.Clean, Text.Combine, Text.Contains, Text.Contains.Any, Text.End, Text.EndsWith, Text.Format, Text.From, Text.FromBinary, Text.Insert, Text.Length, Text.Lower, Text.Middle, Text.NewGuid, Text.PadEnd, Text.PadStart, Text.PositionOf, Text.Proper, Text.Range, Text.Remove, Text.Repeat, Text.Replace, Text.Reverse, Text.Select, Text.Split, Text.SplitAny, Text.Start, Text.StartsWith, Text.ToList, Text.Trim, Text.TrimEnd, Text.TrimStart, Text.Upper

Time (4)

Time.From, Time.Hour, Time.Minute, Time.Second

Value (7)

Value.As, Value.Compare, Value.Equals, Value.Is, Value.NullableEquals, Value.ReplaceType, Value.Type

Supported syntax

Not supported here

Blocked functions (they raise an UnsupportedFeature error): Access.Database, ActiveDirectory.Domains, AzureStorage.Blobs, Binary.Buffer, Diagnostics.Trace, Excel.CurrentWorkbook, Exchange.Contents, Expression.Constant, Expression.Evaluate, Expression.Identifier, File.Contents, Folder.Contents, Folder.Files, Function.InvokeAfter, Hdfs.Files, Json.FromValue, MySQL.Database, OData.Feed, Odbc.DataSource, Odbc.Query, Oracle.Database, Pdf.Tables, PostgreSQL.Database, Python.Execute, R.Execute, SharePoint.Contents, SharePoint.Files, SharePoint.Tables, Sql.Database, Sql.Databases, Table.FuzzyGroup, Table.FuzzyJoin, Table.FuzzyNestedJoin, Value.NativeQuery, Web.BrowserContents, Web.Contents, Web.Page.

Cultures

en-US, en-GB, en-AU, en-CA, de-DE, fr-FR, es-ES, nl-NL

Resource limits

Known differences

AreaThis simulatorNative Power QueryStatus
Table.DistinctKeeps the first row of each duplicate group in current order.Documentation does not guarantee which duplicate survives. Tasks must not depend on it.doc-based
Table.Group orderGroups appear in first-seen order.Output order isn't guaranteed; graders compare as multisets unless a sort is required.doc-based
Join null keysnull keys match null keys (M equality: null = null).Not stated in Table.NestedJoin documentation; requires native-host verification.native-unverified-difference
Right/full outer join orderMatched left rows first, then unmatched right rows.Order isn't guaranteed.doc-based
Type conversion of empty textTable.TransformColumnTypes turns empty/whitespace text into null for non-text types; Number.FromText("") is an error.Observed host behaviour; requires native-host verification.native-unverified-difference
Whole number (Int64)Rounds half to even; values beyond ±9,007,199,254,740,991 raise an error (keep long IDs as text).Native Int64 covers the full 64-bit range.native-unverified-difference
Lazy listsList items are evaluated when the list is built, so an error in any item fails the whole list even if that item is never used: List.Count({1, error "x"}) raises the error here. List.Count, List.First, List.FirstN, List.Skip, List.Range and similar functions are affected. let bindings and record fields are lazy.List items are lazy in M: List.Count({1, error "x"}) returns 2.doc-based
Text orderingOrdinal (code-unit) comparison by default; Comparer.FromCulture uses the browser's collator.Culture collation can differ in edge cases.native-unverified-difference
Csv.Document ragged rowsMissing trailing fields are null; present empty fields are "".Requires native-host verification.native-unverified-difference
Combiner.CombineTextByDelimiterSkips null values.Requires native-host verification.native-unverified-difference
Table.Pivot with aggregationMissing combinations call the aggregator with an empty list (List.Sum → null, List.Count → 0).Requires native-host verification.native-unverified-difference
Number.ToTextSupports F, N, P, D, E with precision and simple 0/#,##0.00 patterns; other formats raise UnsupportedFeature.Full .NET format strings.doc-based
CulturesSupported cultures: en-US, en-GB, en-AU, en-CA, de-DE, fr-FR, es-ES, nl-NL. Others raise UnsupportedFeature.All .NET cultures.doc-based
Excel.WorkbookReads the exercise's parsed workbook model (sheets/tables of scalar values). No formulas, macros, links or embedded queries.Reads real XLSX binaries.doc-based
Equation criteriaColumn names plus Comparer.Ordinal/OrdinalIgnoreCase/FromCulture. Custom comparer functions in joins/distinct are not supported.Arbitrary comparers.doc-based
Currency, accounting and percent textNumber.From, Number.FromText, Currency.From, Int64.From and Table.TransformColumnTypes to type number, Int64.Type or Currency.Type raise DataFormat.Error for text such as "$5", "(5)" and "12%" (en-US). The one exception is a trailing percent sign when the target is a percentage: Percentage.From("12.3%") is 0.123, as in Microsoft's function reference, and Table.TransformColumnTypes to Percentage.Type does the same. Graded data never relies on any of these conversions; strip the symbols first (Text.Remove, Text.Replace) or divide percentages by 100 yourself.Power Query Desktop is commonly reported to accept currency symbols, accounting negatives and percent signs in some conversions. Not verified on a native host (BLK-01), so the simulator doesn't guess.native-unverified-difference
Table.SchemaReturns the native 17-column layout. Name, Position, TypeName (e.g. "Number.Type", "Int64.Type", "Text.Type", "Any.Type") and Kind ("number", "text", …) are produced; IsNullable, the precision/length columns, native type/expression columns, Description, IsWritable and FieldCaption hold an UnsupportedFeature error in every cell, so reading them raises rather than guessing.All 17 columns filled from the column's type facets.doc-based
Table.FromListColumns may be a count, a list of names or a table type (names and ascribed types). The default (4th) and extraValues (5th) arguments raise UnsupportedFeature; rows shorter than the column count are padded with null; a row with more values than columns raises UnsupportedFeature (the native default for extra values is not modelled).default and ExtraValues.List/Error/Ignore control padding and overflow.doc-based