Zum Hauptinhalt springen

Table.SelectRows

Selects the rows that meet the condition function.

Syntax

Table.SelectRows(
table as table,
condition as function
) as table

Remarks

Returns a table of rows from the table, that matches the selection condition.

Examples

Example #1

Select the rows in the table where the values in [CustomerID] column are greater than 2.

Table.SelectRows(
Table.FromRecords({
[CustomerID = 1, Name = "Bob", Phone = "123-4567"],
[CustomerID = 2, Name = "Jim", Phone = "987-6543"],
[CustomerID = 3, Name = "Paul", Phone = "543-7890"],
[CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
}),
each [CustomerID] > 2
)

Result:

Table.FromRecords({
[CustomerID = 3, Name = "Paul", Phone = "543-7890"],
[CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
})

Example #2

Select the rows in the table where the names do not contain a "B".

Table.SelectRows(
Table.FromRecords({
[CustomerID = 1, Name = "Bob", Phone = "123-4567"],
[CustomerID = 2, Name = "Jim", Phone = "987-6543"],
[CustomerID = 3, Name = "Paul", Phone = "543-7890"],
[CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
}),
each not Text.Contains([Name], "B")
)

Result:

Table.FromRecords({
[CustomerID = 2, Name = "Jim", Phone = "987-6543"],
[CustomerID = 3, Name = "Paul", Phone = "543-7890"],
[CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
})

Category

Table.Row operations

More examples

SchemaTransformTable

EnforceSchema.Strict = 1;               // Add any missing columns, remove extra columns, set table type
EnforceSchema.IgnoreExtraColumns = 2; // Add missing columns, do not remove extra columns
EnforceSchema.IgnoreMissingColumns = 3; // Do not add or remove columns

SchemaTransformTable = (table as table, schema as table, optional enforceSchema as number) as table =>
let
// Default to EnforceSchema.Strict
_enforceSchema = if (enforceSchema <> null) then enforceSchema else EnforceSchema.Strict,

// Applies type transforms to a given table
EnforceTypes = (table as table, schema as table) as table =>
let
map = (t) => if Type.Is(t, type list) or Type.Is(t, type record) or t = type any then null else t,
mapped = Table.TransformColumns(schema, {"Type", map}),
omitted = Table.SelectRows(mapped, each [Type] <> null),
existingColumns = Table.ColumnNames(table),
removeMissing = Table.SelectRows(omitted, each List.Contains(existingColumns, [Name])),
primativeTransforms = Table.ToRows(removeMissing),
changedPrimatives = Table.TransformColumnTypes(table, primativeTransforms)
in
changedPrimatives,

// Returns the table type for a given schema
SchemaToTableType = (schema as table) as type =>
let
toList = List.Transform(schema[Type], (t) => [Type=t, Optional=false]),
toRecord = Record.FromList(toList, schema[Name]),
toType = Type.ForRecord(toRecord, false)
in
type table (toType),

// Determine if we have extra/missing columns.
// The enforceSchema parameter determines what we do about them.
schemaNames = schema[Name],
foundNames = Table.ColumnNames(table),
addNames = List.RemoveItems(schemaNames, foundNames),
extraNames = List.RemoveItems(foundNames, schemaNames),
tmp = Text.NewGuid(),
added = Table.AddColumn(table, tmp, each []),
expanded = Table.ExpandRecordColumn(added, tmp, addNames),
result = if List.IsEmpty(addNames) then table else expanded,
fullList =
if (_enforceSchema = EnforceSchema.Strict) then
schemaNames
else if (_enforceSchema = EnforceSchema.IgnoreMissingColumns) then
foundNames
else
schemaNames & extraNames,

// Select the final list of columns.
// These will be ordered according to the schema table.
reordered = Table.SelectColumns(result, fullList, MissingField.Ignore),
enforcedTypes = EnforceTypes(reordered, schema),
withType = if (_enforceSchema = EnforceSchema.Strict) then Value.ReplaceType(enforcedTypes, SchemaToTableType(schema)) else enforcedTypes
in
withType;

Parse Logs

(Input as table)=>
let
Source = Table.Buffer(Input),
RemoveColumns = Table.SelectColumns(Source,{"Id", "Start Time", "End Time", "Query", "Data Source Query", "Step", "Category", "Operation", "Exclusive Duration", "Exclusive Duration (%)", "Row Count", "Path"}),
ReplaceNullsInPath = Table.ReplaceValue(RemoveColumns,null,"",Replacer.ReplaceValue,{"Path"}),
GetNodeOperation = (pId, pPath) as text =>
let
FilterRows = Table.SelectRows(ReplaceNullsInPath, each [Id]=pId and [Path]=pPath),
FirstRow = FilterRows{0},
Op = try FirstRow[Operation] otherwise "Trace Gaps"
in
Op,
CreatePath = Table.AddColumn(ReplaceNullsInPath, "Level", each
let
LevelNumber = List.Count(Text.Split([Path],"/")),
CurrentPath = [Path],
CurrentId = [Id],
PosList = {0..(LevelNumber - 1)},
GenerateText = List.Transform(PosList, each CurrentId & " " & Text.BeforeDelimiter(CurrentPath, "/", _) & " " & GetNodeOperation(CurrentId, Text.BeforeDelimiter(CurrentPath, "/", _))),
Output = Text.Combine(GenerateText, "|")
in
Output
),
FindMaxLevels = Table.AddColumn(CreatePath, "NumberOfLevels", each 1+Text.Length( Text.Select([Level],{"|"}))),
MaxLevels = List.Max(FindMaxLevels[NumberOfLevels]),
SplitLevels = Table.SplitColumn(FindMaxLevels, "Level", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),MaxLevels),
RemoveMaxLevels = Table.RemoveColumns(SplitLevels,{"NumberOfLevels"}),
CalculateDuration = Table.TransformColumns(RemoveMaxLevels,{{"Exclusive Duration", Duration.TotalSeconds, type number}})
in
CalculateDuration