A
- AccessControlEntry.ConditionToIdentities
- Access.Database
- ActiveDirectory.Domains
- AdobeAnalytics.Cubes
- AdoDotNet.DataSource
- AdoDotNet.Query
- AnalysisServices.Database
- AnalysisServices.Databases
- AzureStorage.BlobContents
- AzureStorage.Blobs
- AzureStorage.DataLake
- AzureStorage.DataLakeContents
- AzureStorage.Tables
B
- BinaryFormat.Binary
- BinaryFormat.Byte
- BinaryFormat.ByteOrder
- BinaryFormat.Choice
- BinaryFormat.Decimal
- BinaryFormat.Double
- BinaryFormat.Group
- BinaryFormat.Length
- BinaryFormat.List
- BinaryFormat.Null
- BinaryFormat.Record
- BinaryFormat.SignedInteger16
- BinaryFormat.SignedInteger32
- BinaryFormat.SignedInteger64
- BinaryFormat.Single
- BinaryFormat.Text
- BinaryFormat.Transform
- BinaryFormat.UnsignedInteger16
- BinaryFormat.UnsignedInteger32
- BinaryFormat.UnsignedInteger64
- BinaryFormat.7BitEncodedSignedInteger
- BinaryFormat.7BitEncodedUnsignedInteger
- Binary data
- Binary.ApproximateLength
- Binary.Buffer
- Binary.Combine
- Binary.Compress
- Binary.Decompress
- Binary.From
- Binary.FromList
- Binary.FromText
- Binary.InferContentType
- Binary.Length
- Binary.Range
- Binary.Split
- Binary.ToList
- Binary.ToText
- Binary.View
- Binary.ViewError
- Binary.ViewFunction
- Byte.From
C
- Cdm.Contents
- Character.FromNumber
- Character.ToNumber
- Combiner.CombineTextByDelimiter
- Combiner.CombineTextByEachDelimiter
- Combiner.CombineTextByLengths
- Combiner.CombineTextByPositions
- Combiner.CombineTextByRanges
- Comparer.FromCulture
- Comparer.Ordinal
- Comparer.OrdinalIgnoreCase
- Controlling byte order
- Csv.Document
- Cube.AddAndExpandDimensionColumn
- Cube.AddMeasureColumn
- Cube.ApplyParameter
- Cube.AttributeMemberId
- Cube.AttributeMemberProperty
- Cube.CollapseAndRemoveColumns
- Cube.Dimensions
- Cube.DisplayFolders
- Cube.MeasureProperties
- Cube.MeasureProperty
- Cube.Measures
- Cube.Parameters
- Cube.Properties
- Cube.PropertyKey
- Cube.ReplaceDimensions
- Cube.Transform
- Currency.From
D
- DateTime.AddZone
- DateTime.Date
- DateTime.FixedLocalNow
- DateTime.From
- DateTime.FromFileTime
- DateTime.FromText
- DateTime.IsInCurrentHour
- DateTime.IsInCurrentMinute
- DateTime.IsInCurrentSecond
- DateTime.IsInNextHour
- DateTime.IsInNextMinute
- DateTime.IsInNextNHours
- DateTime.IsInNextNMinutes
- DateTime.IsInNextNSeconds
- DateTime.IsInNextSecond
- DateTime.IsInPreviousHour
- DateTime.IsInPreviousMinute
- DateTime.IsInPreviousNHours
- DateTime.IsInPreviousNMinutes
- DateTime.IsInPreviousNSeconds
- DateTime.IsInPreviousSecond
- DateTime.LocalNow
- DateTime.Time
- DateTime.ToRecord
- DateTime.ToText
- Date.AddDays
- Date.AddMonths
- Date.AddQuarters
- Date.AddWeeks
- Date.AddYears
- Date.Day
- Date.DayOfWeek
- Date.DayOfWeekName
- Date.DayOfYear
- Date.DaysInMonth
- Date.EndOfDay
- Date.EndOfMonth
- Date.EndOfQuarter
- Date.EndOfWeek
- Date.EndOfYear
- Date.From
- Date.FromText
- Date.IsInCurrentDay
- Date.IsInCurrentMonth
- Date.IsInCurrentQuarter
- Date.IsInCurrentWeek
- Date.IsInCurrentYear
- Date.IsInNextDay
- Date.IsInNextMonth
- Date.IsInNextNDays
- Date.IsInNextNMonths
- Date.IsInNextNQuarters
- Date.IsInNextNWeeks
- Date.IsInNextNYears
- Date.IsInNextQuarter
- Date.IsInNextWeek
- Date.IsInNextYear
- Date.IsInPreviousDay
- Date.IsInPreviousMonth
- Date.IsInPreviousNDays
- Date.IsInPreviousNMonths
- Date.IsInPreviousNQuarters
- Date.IsInPreviousNWeeks
- Date.IsInPreviousNYears
- Date.IsInPreviousQuarter
- Date.IsInPreviousWeek
- Date.IsInPreviousYear
- Date.IsInYearToDate
- Date.IsLeapYear
- Date.Month
- Date.MonthName
- Date.QuarterOfYear
- Date.StartOfDay
- Date.StartOfMonth
- Date.StartOfQuarter
- Date.StartOfWeek
- Date.StartOfYear
- Date.ToRecord
- Date.ToText
- Date.WeekOfMonth
- Date.WeekOfYear
- Date.Year
- DB2.Database
- Decimal.From
- Diagnostics.ActivityId
- Diagnostics.Trace
- DirectQueryCapabilities.From
- Double.From
- Duration.Days
- Duration.From
- Duration.FromText
- Duration.Hours
- Duration.Minutes
- Duration.Seconds
- Duration.ToRecord
- Duration.TotalDays
- Duration.TotalHours
- Duration.TotalMinutes
- Duration.TotalSeconds
- Duration.ToText
E
F
G
H
I
L
- Lines.FromBinary
- Lines.FromText
- Lines.ToBinary
- Lines.ToText
- List.Accumulate
- List.AllTrue
- List.Alternate
- List.AnyTrue
- List.Average
- List.Buffer
- List.Combine
- List.ConformToPageReader
- List.Contains
- List.ContainsAll
- List.ContainsAny
- List.Count
- List.Covariance
- List.Dates
- List.DateTimes
- List.DateTimeZones
- List.Difference
- List.Distinct
- List.Durations
- List.FindText
- List.First
- List.FirstN
- List.Generate
- List.InsertRange
- List.Intersect
- List.IsDistinct
- List.IsEmpty
- List.Last
- List.LastN
- List.MatchesAll
- List.MatchesAny
- List.Max
- List.MaxN
- List.Median
- List.Min
- List.MinN
- List.Mode
- List.Modes
- List.NonNullCount
- List.Numbers
- List.Percentile
- List.PositionOf
- List.PositionOfAny
- List.Positions
- List.Product
- List.Random
- List.Range
- List.RemoveFirstN
- List.RemoveItems
- List.RemoveLastN
- List.RemoveMatchingItems
- List.RemoveNulls
- List.RemoveRange
- List.Repeat
- List.ReplaceMatchingItems
- List.ReplaceRange
- List.ReplaceValue
- List.Reverse
- List.Select
- List.Single
- List.SingleOrDefault
- List.Skip
- List.Sort
- List.Split
- List.StandardDeviation
- List.Sum
- List.Times
- List.Transform
- List.TransformMany
- List.Union
- List.Zip
- Logical.From
- Logical.FromText
Understanding the ItemExpression.Item Function
The ItemExpression.Item function in Power Query is used to extract a value from a list or a record based on the index or the name of the item. This function takes two parameters: the first parameter is the list or record, and the second parameter is the index or the name of the item.
Here is the syntax of the ItemExpression.Item function:
ItemExpression.Item(listOrRecord as list or record, indexOrName as any) as any
The function returns the value at the specified index or name. If the index or name is not found, the function returns a null value.
Using the ItemExpression.Item Function
To understand the M code behind the ItemExpression.Item function, let's consider an example. Suppose we have a list of products with their corresponding prices, and we want to extract the price of a specific product based on its name. Here is the list:
| Product | Price |
|---------|-------|
| Apple | 1.5 |
| Banana | 2 |
| Orange | 3 |
| Mango | 4 |
To extract the price of a specific product, we can use the ItemExpression.Item function as follows:
let
products = #table({“Product”, “Price”}, {{“Apple”, 1.5}, {“Banana”, 2}, {“Orange”, 3}, {“Mango”, 4}}),
productName = “Apple”,
price = products{[Product=productName]}[Price]
in
price
In the above code, we first define a table called "products" that contains the list of products and their prices. We then define a variable called "productName" that contains the name of the product we want to extract the price for. Finally, we use the ItemExpression.Item function to extract the price of the product based on its name.
The expression "products{[Product=productName]}[Price]" uses the curly brackets to access the records in the "products" table. The square brackets are used to filter the records based on the "Product" column that matches the value of the "productName" variable. Finally, we use the square brackets again to extract the value of the "Price" column.
The M Code Behind the ItemExpression.Item Function
Now that we have a basic understanding of how to use the ItemExpression.Item function, let's take a look at the M code behind this function.
let
Source = (listOrRecord as list or record, indexOrName as any) as any =>
if Type.Is(listOrRecord, type list) then
List.PositionOf(listOrRecord, indexOrName)
else if Type.Is(listOrRecord, type record) then
Record.Field(listOrRecord, indexOrName)
else
error “Value is not a list or record”,
Function = Value.ReplaceType(Source, type function(list or record, any) as any)
in
Function
The above code defines a function called "Source" that takes two parameters, "listOrRecord" and "indexOrName", and returns the value at the specified index or name.
The function first checks if the "listOrRecord" parameter is a list or a record using the Type.Is function. If the parameter is a list, the function uses the List.PositionOf function to return the value at the specified index. If the parameter is a record, the function uses the Record.Field function to return the value with the specified name.
Finally, the code defines a variable called "Function" that uses the Value.ReplaceType function to replace the type of the "Source" function with a type that takes a list or record and any type as parameters and returns any type.
The ItemExpression.Item function in Power Query is a powerful tool that enables users to extract values from a list or a record based on the index or the name of the item. By understanding the M code behind this function, users can gain a deeper understanding of how Power Query works and how to use the M Language to perform complex data transformations.