WIXLIB

ContentsPower Query M formula languagePower Query M functionsPower Query M functions overviewUnderstanding Power Query M functionsAccessing data functionsAccessing data functions rIdCube.AttributeMemberProperty

ormix.DatabaseJson.Document

t.ContentsSharePoint.Files

ry functionsBinary functions .ToListBinary.ToTextBinaryEncoding.Base64

nalOccurrence.Repeating

Occurrence.Required#binaryCombiner functionsCombiner functions TextByRangesComparer functionsComparer functions Date functionsDate functions fWeek

MonthName

teTime functionsDateTime functions extHourDateTime.IsInNextMinute

oRecordDateTime.ToText#datetimeDateTimeZone functionsDateTimeZone functions neHoursDateTimeZone.ZoneMinutes

#datetimezoneDuration functionsDuration functions n.ToText#durationError handlingError handling ngExpression functionsExpression functions ession.IdentifierFunction values

Function values nes functionsLines functions ryLines.ToTextList functionsList functions imeZonesList.DifferenceList.DistinctList.Durations

ist.RemoveNulls

List.ZipLogical functionsLogical functions Number functionsNumber functions romInt8.FromInt16.From

dNumber.NaNNumber.NegativeInfinity

TowardZeroRoundingMode.UpSingle.FromRecord functionsRecord functions ngField.UseNullRecord.AddFieldRecord.Combine

rmFieldsReplacer functionsReplacer functions plitter functionsSplitter functions tedLengths

Splitter.SplitTextByWhitespaceTable functionsTable functions Expression.ColumnRowExpression.From

le.FirstValueTable.FromColumnsTable.FromList

angeTable.RemoveColumns

e.TransformColumns

tableText functionsText functions xt.InsertText.Length

f8TextEncoding.Utf16TextEncoding.Windows

Time functionsTime functions cordTime.ToText#timeType functionsType functions ableColumn

ionUri functionsUri functions ataStringUri.PartsValue functionsValue functions MetadataValue.ReplaceMetadata

ValueQuick tour of the Power Query M formula languagePower Query M language specificationPower Query M type systemExpressions, values, and let expressionCommentsEvaluation modelOperatorsType conversionMetadataErrors

Power Query M function reference11/25/2019 2 minutes to readThe Power Query M function reference includes articles for each of the over 700 functions. The reference articlesyou see here on docs.microsoft.com are auto-generated from in-product help. To learn more about functions andhow they work in an expression, see Understanding Power Query M functions.Functions by categoryAccessing data functionsBinary functionsCombiner functionsComparer functionsDate functionsDateTime functionsDateTimeZone functionsDuration functionsError handlingExpression functionsFunction valuesList functionsLines functionsLogical functionsNumber functionsRecord functionsReplacer functionsSplitter functionsTable functionsText functionsTime functionsType functionsUri functionsValue functions

Understanding Power Query M functions11/25/2019 2 minutes to readIn the Power Query M formula language, a function is a mapping from a set of input values to a single outputvalue. A function is written by first naming the function parameters, and then providing an expression to computethe result of the function. The body of the function follows the goes-to ( ) symbol. Optionally, type informationcan be included on parameters and the function return value. A function is defined and invoked in the body of a letstatement. Parameters and/or return value can be implicit or explicit. Implicit parameters and/or return value are oftype any. Type any is similar to an object type in other languages. All types in M derive from type any.A function is a value just like a number or a text value, and can be included in-line just like any other expression.The following example shows a function which is the value of an Add variable which is then invoked, or executed,from several other variables. When a function is invoked, a set of values are specified which are logicallysubstituted for the required set of input values within the function body expression.Example – Explicit parameters and return valueletAddOne (x as number) as number x 1,//additional expression stepsCalcAddOne AddOne(5)inCalcAddOneExample – Implicit parameters and return valueletAdd (x, y) x y,AddResults [OnePlusOne Add(1, 1),OnePlusTwo Add(1, 2)]// equals 2// equals 3inAddResultsFind the first element of a list greater than 5, or null otherwiseletFirstGreaterThan5 (list) letGreaterThan5 List.Select(list, (n) n 5),First List.First(GreaterThan5)inFirst,Results [Found FirstGreaterThan5({3,7,9}), // equals 7NotFound FirstGreaterThan5({1,3,4}) // equals null]inResultsFunctions can be used recursively. In order to recursively reference the function, prefix the identifier with @.

letfact (num) if num 0 then 1 else num * @fact (num-1)infact(5) // equals 120Each keywordThe each keyword is used to easily create simple functions. “each .” is syntactic sugar for a function signature thattakes the parameter “( ) .”Each is useful when combined with the lookup operator, which is applied by default toFor example, each [CustomerID ] is the same as each [CustomerID ], which is the same as ( ) [CustomerID ]Example – Using each in table row rID [CustomerID [CustomerID [CustomerID }),each [CustomerID] )[Name]// equals "Jim"1,2,3,4,2NameNameNameName "Bob", Phone "123-4567"],"Jim", Phone "987-6543"] ,"Paul", Phone "543-7890"] ,"Ringo", Phone "232-1550"]

Accessing data functions11/25/2019 8 minutes to readAccessing dataFunctions in this section access data and return table values. Most of these functions return a table value that iscalled a navigation table. A navigation table is a two column table. The first column contains the name of anitem and the corresponding second column contains the value of that item. This shape is primarily used by thePower Query user interface to provide navigation experience over the potentially large hierarchical data ditionToIdentitiesReturns a list of identities that the condition will accept.AccessControlKind.AllowAccess is allowed.AccessControlKind.DenyAccess is denied.Access.DatabaseReturns a structural representation of an Microsoft Accessdatabase.ActiveDirectory.DomainsReturns a list of Active Directory domains in the same forestas the specified domain or of the current machine's domain ifnone is specified.AdobeAnalytics.CubesReturns the report suites in Adobe Analytics.AdoDotNet.DataSourceReturns the schema collection for an ADO.NET data source.AdoDotNet.QueryReturns the schema collection for an ADO.NET data source.AnalysisServices.DatabaseReturns a table of multidimensional cubes or tabular modelsfrom the Analysis Services database.AnalysisServices.DatabasesReturns the Analysis Services databases on a particular host.AzureStorage.BlobContentsReturns the content of the specified blob from an Azurestorage vault.AzureStorage.BlobsReturns a navigational table containing all containers found inthe Azure Storage account. Each row has the container nameand a link to the container blobs.AzureStorage.DataLakeReturns a navigational table containing the documents foundin the specified container and its subfolders from Azure DataLake Storage.AzureStorage.DataLakeContentsReturns the content of the specified file from an Azure DataLake Storage filesystem.

FUNCTIONDESCRIPTIONAzureStorage.TablesReturns a navigational table containing a row for each tablefound at the account URL from an Azure storage vault. Eachrow contains a link to the azure table.Csv.DocumentReturns the contents of a CSV document as a table using thespecified encoding.CsvStyle.QuoteAfterDelimiterQuotes in a field are only significant immediately following thedelimiter.CsvStyle.QuoteAlwaysQuotes in a field are always significant regardless of wherethey appear.Cube.AddAndExpandDimensionColumnMerges the specified dimension table, dimensionSelector, intothe cube’s, cube, filter context and changes the dimensionalgranularity by expanding the specified set, attributeNames, ofdimension attributes.Cube.AddMeasureColumnAdds a column with the name column to the cube thatcontains the results of the measure measureSelector applied inthe row context of each row.Cube.ApplyParameterReturns a cube after applying parameter with arguments tocube.Cube.AttributeMemberIdReturns the unique member identifier from a memberproperty value.Cube.AttributeMemberPropertyReturns the propertyattribute .Cube.CollapseAndRemoveColumnsChanges the dimensional granularity of the filter context forthe cube by collapsing the attributes mapped to the specifiedcolumns columnNames.Cube.DimensionsReturns a table containing the set of available dimensionswithin the cube.Cube.DisplayFoldersReturns a nested tree of tables representing the display folderhierarchy of the objects (e.g. dimensions and measures)available for use in the cube.Cube.MeasurePropertiesReturns a table containing the set of available properties formeasures that are expanded in the cube.Cube.MeasurePropertyReturns the property of a measure.Cube.MeasuresReturns a table containing the set of available measures withinthe cube.Cube.ParametersReturns a table containing the set of parameters that can beapplied to cube.propertyNameof dimension attribute

FUNCTIONDESCRIPTIONCube.PropertiesReturns a table containing the set of available properties fordimensions that are expanded in the cube.Cube.PropertyKeyReturns the key of ormApplies the list cube functions, transforms, on the cube.DB2.DatabaseReturns a table of SQL tables and views available in a Db2database.Essbase.CubesReturns the cubes in an Essbase instance grouped by Essbaseserver.Excel.CurrentWorkbookReturns the tables in the current Excel Workbook.Excel.WorkbookReturns a table representing sheets in the given excelworkbook.Exchange.ContentsReturns a table of contents from a Microsoft Exchangeaccount.Facebook.GraphReturns a record containing content from the Facebook graph.File.ContentsReturns the binary contents of the file located at a path.Folder.ContentsReturns a table containing the properties and contents of thefiles and folders found at path.Folder.FilesReturns a table containing a row for each file found at a folderpath, and subfolders. Each row contains properties of thefolder or file and a link to its content.GoogleAnalytics.AccountsReturns the Google Analytics accounts for the currentcredential.Hdfs.ContentsReturns a table containing a row for each folder and file foundat the folder url, {0}, from a Hadoop file system. Each rowcontains properties of the folder or file and a link to itscontent.Hdfs.FilesReturns a table containing a row for each file found at thefolder url, {0}, and subfolders from a Hadoop file system. Eachrow contains properties of the file and a link to its content.HdInsight.ContainersReturns a navigational table containing all containers found inthe HDInsight account. Each row has the container name andtable containing its files.HdInsight.ContentsReturns a navigational table containing all containers found inthe HDInsight account. Each row has the container name andtable containing its files.