Funciones auxiliares
Este artículo contiene una serie de funciones auxiliares que se usan habitualmente en extensiones de M. Estas funciones se podrían mover finalmente a la biblioteca oficial de M, pero por ahora se pueden copiar en el código del archivo de la extensión. No debe marcar ninguna de estas funciones como shared en el código de la extensión.
Tablas de navegación
Table.ToNavigationTable
Esta función agrega los metadatos de tipo de tabla necesarios para que la extensión devuelva un valor de tabla que Power Query pueda reconocer como árbol de navegación. Para obtener más información, consulte Tablas de navegación.
Table.ToNavigationTable = (
table as table,
keyColumns as list,
nameColumn as text,
dataColumn as text,
itemKindColumn as text,
itemNameColumn as text,
isLeafColumn as text
) as table =>
let
tableType = Value.Type(table),
newTableType = Type.AddTableKey(tableType, keyColumns, true) meta
[
NavigationTable.NameColumn = nameColumn,
NavigationTable.DataColumn = dataColumn,
NavigationTable.ItemKindColumn = itemKindColumn,
Preview.DelayColumn = itemNameColumn,
NavigationTable.IsLeafColumn = isLeafColumn
],
navigationTable = Value.ReplaceType(table, newTableType)
in
navigationTable;
| Parámetro | Detalles |
|---|---|
| table | La tabla de navegación. |
| keyColumns | Lista de nombres de columna que actúan como la clave principal para la tabla de navegación. |
| nameColumn | El nombre de la columna que se debe usar como nombre para mostrar en el navegador. |
| dataColumn | El nombre de la columna que contiene la tabla o función que se va a mostrar. |
| itemKindColumn | Nombre de la columna que se usará para determinar el tipo de icono que se va a mostrar. Los valores válidos para la columna se muestran en el artículo Control de la navegación. |
| itemNameColumn | Nombre de la columna que se usará para determinar el tipo de información sobre herramientas que se va a mostrar. Los valores válidos para la columna son Table y Function. |
| isLeafColumn | Nombre de la columna que se utiliza para determinar si se trata de un nodo hoja o si el nodo se puede expandir para contener otra tabla de navegación. |
Ejemplo de uso:
shared MyExtension.Contents = () =>
let
objects = #table(
{"Name", "Key", "Data", "ItemKind", "ItemName", "IsLeaf"},{
{"Item1", "item1", #table({"Column1"}, {{"Item1"}}), "Table", "Table", true},
{"Item2", "item2", #table({"Column1"}, {{"Item2"}}), "Table", "Table", true},
{"Item3", "item3", FunctionCallThatReturnsATable(), "Table", "Table", true},
{"MyFunction", "myfunction", AnotherFunction.Contents(), "Function", "Function", true}
}),
NavTable = Table.ToNavigationTable(objects, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
NavTable;
Manipulación de URI
Uri.FromParts
Esta función crea una dirección URL completa basada en campos individuales del registro. Actúa como el inverso de Uri.Parts.
Uri.FromParts = (parts) =>
let
port = if (parts[Scheme] = "https" and parts[Port] = 443) or (parts[Scheme] = "http" and parts[Port] = 80) then "" else ":" & Text.From(parts[Port]),
div1 = if Record.FieldCount(parts[Query]) > 0 then "?" else "",
div2 = if Text.Length(parts[Fragment]) > 0 then "#" else "",
uri = Text.Combine({parts[Scheme], "://", parts[Host], port, parts[Path], div1, Uri.BuildQueryString(parts[Query]), div2, parts[Fragment]})
in
uri;
Uri.GetHost
Esta función devuelve el esquema, el host y el puerto predeterminado (para HTTP/HTTPS) para una dirección URL determinada. Por ejemplo, https://bing.com/subpath/query?param=1¶m2=hello se convertiría en https://bing.com:443.
Esto es especialmente útil para crear ResourcePath.
Uri.GetHost = (url) =>
let
parts = Uri.Parts(url),
port = if (parts[Scheme] = "https" and parts[Port] = 443) or (parts[Scheme] = "http" and parts[Port] = 80) then "" else ":" & Text.From(parts[Port])
in
parts[Scheme] & "://" & parts[Host] & port;
ValidateUrlScheme
Esta función comprueba si el usuario ha especificado una dirección URL HTTPS y genera un error si no lo ha hecho. Esto es necesario cuando se trata de direcciones URL especificadas por el usuario para conectores certificados.
ValidateUrlScheme = (url as text) as text => if (Uri.Parts(url)[Scheme] <> "https") then error "Url scheme must be HTTPS" else url;
Para aplicarlo, solo tiene que encapsular el parámetro url en la función de acceso a los datos.
DataAccessFunction = (url as text) as table =>
let
_url = ValidateUrlScheme(url),
source = Web.Contents(_url)
in
source;
Recuperando datos
Value.WaitFor
Esta función es útil cuando realiza una solicitud HTTP asincrónica y debe sondear el servidor hasta que se complete la solicitud.
Value.WaitFor = (producer as function, interval as function, optional count as number) as any =>
let
list = List.Generate(
() => {0, null},
(state) => state{0} <> null and (count = null or state{0} < count),
(state) => if state{1} <> null then {null, state{1}} else {1 + state{0}, Function.InvokeAfter(() => producer(state{0}), interval(state{0}))},
(state) => state{1})
in
List.Last(list);
Table.GenerateByPage
Esta función se usa cuando una API devuelve datos con un formato incremental o paginado, que es común para muchas API REST. El argumento getNextPage es una función que toma un único parámetro, que es el resultado de la llamada anterior a getNextPage y debe devolver nullable table.
getNextPage = (lastPage) as nullable table => ...;
getNextPage se llama repetidamente hasta que devuelve null. La función intercala todas las páginas en una sola tabla. Cuando el resultado de la primera llamada a getNextPage es NULL, se devuelve una tabla vacía.
// The getNextPage function takes a single argument and is expected to return a nullable table
Table.GenerateByPage = (getNextPage as function) as table =>
let
listOfPages = List.Generate(
() => getNextPage(null), // get the first page of data
(lastPage) => lastPage <> null, // stop when the function returns null
(lastPage) => getNextPage(lastPage) // pass the previous page to the next function call
),
// concatenate the pages together
tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"Column1"}),
firstRow = tableOfPages{0}?
in
// if we didn't get back any pages of data, return an empty table
// otherwise set the table type based on the columns of the first page
if (firstRow = null) then
Table.FromRows({})
// check for empty first table
else if (Table.IsEmpty(firstRow[Column1])) then
firstRow[Column1]
else
Value.ReplaceType(
Table.ExpandTableColumn(tableOfPages, "Column1", Table.ColumnNames(firstRow[Column1])),
Value.Type(firstRow[Column1])
);
Notas adicionales:
- La función
getNextPagetiene que recuperar la dirección URL de la página siguiente (o el número de página o cualquier otro valor que se use para implementar la lógica de paginación). Por lo general, este proceso se realiza agregando valoresmetaa la página antes de devolverla. - Las columnas y el tipo de la tabla combinada (es decir, todas las páginas juntas) se derivan de la primera página de datos. La función
getNextPagedebe normalizar cada página de datos. - La primera llamada a
getNextPagerecibe un parámetro NULL. getNextPagedebe devolver NULL cuando no quedan páginas.
Puede ver un ejemplo de uso de esta función en el ejemplo de GitHub y en el ejemplo de paginación de TripPin.
Github.PagedTable = (url as text) => Table.GenerateByPage((previous) =>
let
// If we have a previous page, get its Next link from metadata on the page.
next = if (previous <> null) then Value.Metadata(previous)[Next] else null,
// If we have a next link, use it, otherwise use the original URL that was passed in.
urlToUse = if (next <> null) then next else url,
// If we have a previous page, but don't have a next link, then we're done paging.
// Otherwise retrieve the next page.
current = if (previous <> null and next = null) then null else Github.Contents(urlToUse),
// If we got data back from the current page, get the link for the next page
link = if (current <> null) then Value.Metadata(current)[Next] else null
in
current meta [Next=link]);
SchemaTransformTable
EnforceSchema.Strict = 1; // Add any missing columns, remove extra columns, set table type
EnforceSchema.IgnoreExtraColumns = 2; // Add missing columns, don't remove extra columns
EnforceSchema.IgnoreMissingColumns = 3; // Don't 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 are 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;
Table.ChangeType
let
// table should be an actual Table.Type, or a List.Type of Records
Table.ChangeType = (table, tableType as type) as nullable table =>
// we only operate on table types
if (not Type.Is(tableType, type table)) then error "type argument should be a table type" else
// if we have a null value, just return it
if (table = null) then table else
let
columnsForType = Type.RecordFields(Type.TableRow(tableType)),
columnsAsTable = Record.ToTable(columnsForType),
schema = Table.ExpandRecordColumn(columnsAsTable, "Value", {"Type"}, {"Type"}),
previousMeta = Value.Metadata(tableType),
// make sure we have a table
parameterType = Value.Type(table),
_table =
if (Type.Is(parameterType, type table)) then table
else if (Type.Is(parameterType, type list)) then
let
asTable = Table.FromList(table, Splitter.SplitByNothing(), {"Column1"}),
firstValueType = Value.Type(Table.FirstValue(asTable, null)),
result =
// if the member is a record (as expected), then expand it.
if (Type.Is(firstValueType, type record)) then
Table.ExpandRecordColumn(asTable, "Column1", schema[Name])
else
error Error.Record("Error.Parameter", "table argument is a list, but not a list of records", [ ValueType = firstValueType ])
in
if (List.IsEmpty(table)) then
#table({"a"}, {})
else result
else
error Error.Record("Error.Parameter", "table argument should be a table or list of records", [ValueType = parameterType]),
reordered = Table.SelectColumns(_table, schema[Name], MissingField.UseNull),
// process primitive values - this calls Table.TransformColumnTypes
map = (t) => if Type.Is(t, type table) or 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(reordered),
removeMissing = Table.SelectRows(omitted, each List.Contains(existingColumns, [Name])),
primativeTransforms = Table.ToRows(removeMissing),
changedPrimatives = Table.TransformColumnTypes(reordered, primativeTransforms),
// Get the list of transforms we use for Record types
recordColumns = Table.SelectRows(schema, each Type.Is([Type], type record)),
recordTypeTransformations = Table.AddColumn(recordColumns, "RecordTransformations", each (r) => Record.ChangeType(r, [Type]), type function),
recordChanges = Table.ToRows(Table.SelectColumns(recordTypeTransformations, {"Name", "RecordTransformations"})),
// Get the list of transforms we use for List types
listColumns = Table.SelectRows(schema, each Type.Is([Type], type list)),
listTransforms = Table.AddColumn(listColumns, "ListTransformations", each (t) => List.ChangeType(t, [Type]), Function.Type),
listChanges = Table.ToRows(Table.SelectColumns(listTransforms, {"Name", "ListTransformations"})),
// Get the list of transforms we use for Table types
tableColumns = Table.SelectRows(schema, each Type.Is([Type], type table)),
tableTransforms = Table.AddColumn(tableColumns, "TableTransformations", each (t) => @Table.ChangeType(t, [Type]), Function.Type),
tableChanges = Table.ToRows(Table.SelectColumns(tableTransforms, {"Name", "TableTransformations"})),
// Perform all of our transformations
allColumnTransforms = recordChanges & listChanges & tableChanges,
changedRecordTypes = if (List.IsEmpty(allColumnTransforms)) then changedPrimatives else Table.TransformColumns(changedPrimatives, allColumnTransforms, null, MissingField.Ignore),
// set final type
withType = Value.ReplaceType(changedRecordTypes, tableType)
in
if (List.IsEmpty(Record.FieldNames(columnsForType))) then table else withType meta previousMeta,
// If given a generic record type (no predefined fields), the original record is returned
Record.ChangeType = (record as record, recordType as type) =>
let
// record field format is [ fieldName = [ Type = type, Optional = logical], ... ]
fields = try Type.RecordFields(recordType) otherwise error "Record.ChangeType: failed to get record fields. Is this a record type?",
fieldNames = Record.FieldNames(fields),
fieldTable = Record.ToTable(fields),
optionalFields = Table.SelectRows(fieldTable, each [Value][Optional])[Name],
requiredFields = List.Difference(fieldNames, optionalFields),
// make sure all required fields exist
withRequired = Record.SelectFields(record, requiredFields, MissingField.UseNull),
// append optional fields
withOptional = withRequired & Record.SelectFields(record, optionalFields, MissingField.Ignore),
// set types
transforms = GetTransformsForType(recordType),
withTypes = Record.TransformFields(withOptional, transforms, MissingField.Ignore),
// order the same as the record type
reorder = Record.ReorderFields(withTypes, fieldNames, MissingField.Ignore)
in
if (List.IsEmpty(fieldNames)) then record else reorder,
List.ChangeType = (list as list, listType as type) =>
if (not Type.Is(listType, type list)) then error "type argument should be a list type" else
let
listItemType = Type.ListItem(listType),
transform = GetTransformByType(listItemType),
modifiedValues = List.Transform(list, transform),
typed = Value.ReplaceType(modifiedValues, listType)
in
typed,
// Returns a table type for the provided schema table
Schema.ToTableType = (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),
previousMeta = Value.Metadata(schema)
in
type table (toType) meta previousMeta,
// Returns a list of transformations that can be passed to Table.TransformColumns, or Record.TransformFields
// Format: {"Column", (f) => ...} .... ex: {"A", Number.From}
GetTransformsForType = (_type as type) as list =>
let
fieldsOrColumns = if (Type.Is(_type, type record)) then Type.RecordFields(_type)
else if (Type.Is(_type, type table)) then Type.RecordFields(Type.TableRow(_type))
else error "GetTransformsForType: record or table type expected",
toTable = Record.ToTable(fieldsOrColumns),
transformColumn = Table.AddColumn(toTable, "Transform", each GetTransformByType([Value][Type]), Function.Type),
transformMap = Table.ToRows(Table.SelectColumns(transformColumn, {"Name", "Transform"}))
in
transformMap,
GetTransformByType = (_type as type) as function =>
if (Type.Is(_type, type number)) then Number.From
else if (Type.Is(_type, type text)) then Text.From
else if (Type.Is(_type, type date)) then Date.From
else if (Type.Is(_type, type datetime)) then DateTime.From
else if (Type.Is(_type, type duration)) then Duration.From
else if (Type.Is(_type, type datetimezone)) then DateTimeZone.From
else if (Type.Is(_type, type logical)) then Logical.From
else if (Type.Is(_type, type time)) then Time.From
else if (Type.Is(_type, type record)) then (t) => if (t <> null) then @Record.ChangeType(t, _type) else t
else if (Type.Is(_type, type table)) then (t) => if (t <> null) then @Table.ChangeType(t, _type) else t
else if (Type.Is(_type, type list)) then (t) => if (t <> null) then @List.ChangeType(t, _type) else t
else (t) => t
in
Table.ChangeType