Todos los informáticos respetados han oído hablar de las funciones recursivas (más detalles en Wiki: https://en.wikipedia.org/wiki/Recursión). Hay muchos algoritmos que se mencionan como ejemplo de tales funciones, es decir, la secuencia de Fibonacci como el ejemplo más simple:
mentira (0) = 0
mentira(1) = 1
Para todos los enteros n > 1, Fib(n) = Fib(n-1) + Fib(n-2)
¿Cómo reescribimos este pseudocódigo en una función en PowerQuery?
let
fnFibonacci = (value as number) as number =>
if value = 0 then
0
else if value = 1 then
1
else
@fnFibonacci(value - 1) + @fnFibonacci(value - 2),
Source = List.Transform({0..10}, fnFibonacci)
in
Source
La parte más importante es el uso de @ antes de llamar a la función recursiva. Le decimos a PowerQuery que haga referencia a su propio nombre desde dentro de sí mismo.
Probemos nuestra función:
Bueno, eso es genial pero no muy útil en la vida real. He estado buscando durante mucho tiempo un escenario que pueda usar funciones recursivas de manera significativa. ¡Y he encontrado uno!
En PowerQuery, puede seleccionar o cambiar el nombre de muchas columnas en un solo paso. Además, puede cambiar sus tipos de datos a la vez. Pero las modificaciones repetitivas de una tabla ya no son tan fáciles.
En mi proceso ETL escrito en PowerQuery, quería dividir cada columna de fecha y hora en una tabla de hechos en 2 columnas separadas: fecha y hora. Tiene al menos 2 grandes ventajas. En primer lugar, los valores de fecha y hora se convierten en una clave externa para mis dimensiones de fecha y hora; segundo, el tamaño de mi conjunto de datos disminuirá drásticamente. Esto significa que en lugar de muchos valores únicos, obtendré solo 365 valores únicos por año para la dimensión de fecha y 86 400 valores únicos para la dimensión de tiempo con una precisión de un segundo.
¿Cuáles son mis opciones? Puedo crear una nueva columna personalizada de tipo de datos de fecha para cada columna de fecha y hora y otra columna personalizada para la hora. Sí puedo, pero no me gusta hacer un trabajo repetitivo. Intentemos automatizarlo.
En primer lugar, necesitamos algunos datos de prueba.
// create a table with some datetime columns
Source = Table.FromRecords(
{
[OrderID = 1, CustomerID = 1, OrderPlaced_dt = #datetime(2019,6,16,10,0,0), OrderPaid_dt = #datetime(2019,6,16,10,5,0), OrderShipped_dt = #datetime(2019,6,16,11,0,0), Price = 100.0],
[OrderID = 2, CustomerID = 1, OrderPlaced_dt = #datetime(2019,6,16,12,12,12), OrderPaid_dt = #datetime(2019,6,16,13,13,13), OrderShipped_dt = null, Price = 200.0]
},
type table[OrderID = Int64.Type, CustomerID = Int64.Type, OrderPlaced_dt = DateTime.Type, OrderPaid_dt = DateTime.Type, OrderShipped_dt = DateTime.Type, Price = Decimal.Type]
),
A continuación, creemos una función, que tiene 2 parámetros: una tabla de origen y un nombre de una columna de fecha y hora. Esta función hace lo mismo que harías en la interfaz de usuario. Crea 2 nuevas columnas y elimina la original.
// split a datetime column into 2 columns: date and time
fnSplitDateTimeColumn = (parTbl as table, parColumnName as text) as table =>
let
// add a new column with date
transformDateExpression = Expression.Evaluate("each Date.From([" & parColumnName & "])", #shared),
addDateColumn = Table.AddColumn(parTbl, parColumnName & "_date", transformDateExpression, type date),
// add a new column with time
transformTimeExpression = Expression.Evaluate("each try #time(Time.Hour([" & parColumnName & "]),Time.Minute([" & parColumnName & "]),Time.Second([" & parColumnName & "])) otherwise null", #shared),
addTimeColumn = Table.AddColumn(addDateColumn, parColumnName & "_time", transformTimeExpression, type time),
// remove datetime column
removeDateTimeColumn = Table.RemoveColumns(addTimeColumn, parColumnName)
in
removeDateTimeColumn,
Y finalmente, en el último paso creamos otra función que es recursiva. Esta función también tiene 2 parámetros: una tabla de origen y una lista de todos los nombres de columnas de fecha y hora que aún no hemos transformado. La función toma el primer elemento de la lista (un nombre de columna de fecha y hora), divide esta columna de fecha y hora en 2 columnas y se llama a sí misma de forma recursiva. La llamada recursiva usa el último paso como la nueva tabla de origen y una lista de todos los nombres de columnas de fecha y hora, excepto el primero (que ya está procesado). Una vez que la lista está vacía, la recursión termina. La función de recursión devuelve una tabla de origen modificada: en lugar de una columna de fecha y hora, ahora hay 2 columnas.
// recursive function which splits all datetime columns into date and time columns
// parTbl is a source table to modify, parColumnNameList is a list of columns to split
fnSplitAllDateTimeColumns = (parTbl as table, parColumnNameList as list) as table =>
// if parColumNameList is empty, terminate the recursion
if List.IsEmpty(parColumnNameList) then
parTbl
else
let
// get one column name to process
currentColumnName = List.First(parColumnNameList),
// remove first item from the parColumnNameList
nextColumNameList = List.RemoveFirstN(parColumnNameList, 1),
// split current column
splitOneColumnTable = fnSplitDateTimeColumn(parTbl, currentColumnName),
// call itself recursively with a new created table and a shortend column name list
nextIterationTable = @fnSplitAllDateTimeColumns(splitOneColumnTable, nextColumNameList)
in
nextIterationTable,
Y todo junto:
let
// create a table with some datetime columns
Source = Table.FromRecords(
{
[OrderID = 1, CustomerID = 1, OrderPlaced_dt = #datetime(2019,6,16,10,0,0), OrderPaid_dt = #datetime(2019,6,16,10,5,0), OrderShipped_dt = #datetime(2019,6,16,11,0,0), Price = 100.0],
[OrderID = 2, CustomerID = 1, OrderPlaced_dt = #datetime(2019,6,16,12,12,12), OrderPaid_dt = #datetime(2019,6,16,13,13,13), OrderShipped_dt = null, Price = 200.0]
},
type table[OrderID = Int64.Type, CustomerID = Int64.Type, OrderPlaced_dt = DateTime.Type, OrderPaid_dt = DateTime.Type, OrderShipped_dt = DateTime.Type, Price = Decimal.Type]
),
// split a datetime column into 2 columns: date and time
fnSplitDateTimeColumn = (parTbl as table, parColumnName as text) as table =>
let
// add a new column with date
transformDateExpression = Expression.Evaluate("each Date.From([" & parColumnName & "])", #shared),
addDateColumn = Table.AddColumn(parTbl, parColumnName & "_date", transformDateExpression, type date),
// add a new column with time
transformTimeExpression = Expression.Evaluate("each try #time(Time.Hour([" & parColumnName & "]),Time.Minute([" & parColumnName & "]),Time.Second([" & parColumnName & "])) otherwise null", #shared),
addTimeColumn = Table.AddColumn(addDateColumn, parColumnName & "_time", transformTimeExpression, type time),
// remove datetime column
removeDateTimeColumn = Table.RemoveColumns(addTimeColumn, parColumnName)
in
removeDateTimeColumn,
// recursive function which splits all datetime columns into date and time columns
// parTbl is a source table to modify, parColumnNameList is a list of columns to split
fnSplitAllDateTimeColumns = (parTbl as table, parColumnNameList as list) as table =>
// if parColumNameList is empty, terminate the recursion
if List.IsEmpty(parColumnNameList) then
parTbl
else
let
// get one column name to process
currentColumnName = List.First(parColumnNameList),
// remove first item from the parColumnNameList
nextColumNameList = List.RemoveFirstN(parColumnNameList, 1),
// split current column
splitOneColumnTable = fnSplitDateTimeColumn(parTbl, currentColumnName),
// call itself recursively with a new created table and a shortend column name list
nextIterationTable = @fnSplitAllDateTimeColumns(splitOneColumnTable, nextColumNameList)
in
nextIterationTable,
// get all columns having the datatype datetime
DateTimeColumnNames = Table.ColumnsOfType(Source, {type datetime}),
// split all datetime columns
SplitAllDateTimeColumns = fnSplitAllDateTimeColumns(Source, DateTimeColumnNames)
in
SplitAllDateTimeColumns
Y el resultado:
¿Es mucho código para una tarea tan simple que puede administrar en PowerQuery Editor con una interfaz de usuario brillante? Si y no. Si tiene muchas tablas de hechos con muchas columnas de fecha y hora y hace un ETL, estará muy feliz de tener la oportunidad de automatizar eso. Pero si solo tiene una tabla con 2 columnas de fecha y hora, es una exageración.
¿Conoce algún otro caso de uso pragmático para funciones recursivas en PowerQuery? Por favor, házmelo saber en los comentarios. 😊
