Item access in M

Accessing records from tables, items from lists, and values from records are fundamental to M query development. In the following example, the results of the BI.vDim_Account SQL view are returned to Power BI using slightly different M syntax than the customer dimension query from the previous section:

let
    Source = AdWorksSQLServer,
    AccountRecord = Source{[Name = "BI.vDim_Account"]},
    Account = AccountRecord[Data]
in
    Account

For this query, a record is retrieved from the AdWorksSQLServer staging query based only on the Name column. The Data field of this record is then accessed in a separate variable (Account) to return the results of the BI.vDim_Account SQL view to Power BI. BI teams or the dataset designer can decide on a standard method for accessing the items exposed from a data source staging query.

The following sample code retrieves the "Cherry" string value from an M list:

let
    Source = {"Apple","Banana","Cherry","Dates"},
    ItemFromList = Source{2}
in
    ItemFromList

M is a zero-based system such that Source{0} would return the "Apple" value and Source{4} would return an error since there are only four items in the list. Zero-based access also applies to extracting characters from a text value. For example, the Text.Range("Brett",2,2) M expression returns the et characters.

The list value type in M is an ordered sequence of values. There are many functions available for analyzing and transforming list values, such as List.Count() and List.Distinct(). List functions that aggregate the values they contain (for example, List.Average()) are often used within grouping queries that invoke the Table.Group() function.

Table of Contents for Item access in M

Create new playlist

Sign In

Sign Up

Table of Contents for
Item access in M