Funciones de transformación en Power Query para la limpieza y transformación de datos

SE APLICA A: Azure Data Factory Azure Synapse Analytics

Sugerencia

Pruebe Data Factory en Microsoft Fabric, una solución de análisis todo en uno para empresas. Microsoft Fabric abarca todo, desde el movimiento de datos hasta la ciencia de datos, el análisis en tiempo real, la inteligencia empresarial y los informes. ¡Obtenga más información sobre cómo iniciar una nueva evaluación gratuita!

La limpieza y transformación de datos en Azure Data Factory permite la ágil preparación, y limpieza y transformación de datos sin código a escala de nube mediante la traducción de scripts M de Power Query al script de Data Flow. ADF se integra en Power Query online y pone las funciones M de Power Query a disposición para la limpieza y transformación de datos a través de la ejecución de Spark con la infraestructura de Spark de flujo de datos.

Actualmente no se admiten todas las funciones de Power Query M para la limpieza y transformación de datos, a pesar de estar disponibles durante la creación. Al compilar las recopilaciones, aparecerá el siguiente mensaje de error si no se admite una función:

UserQuery : Expression.Error: The transformation logic is not supported as it requires dynamic access to rows of data, which cannot be scaled out.

A continuación se muestra una lista de funciones admitidas de Power Query M.

Administración de columnas

• Selección: Table.SelectColumns
• Eliminación: Table.RemoveColumns
• Cambio de nombre: Table.RenameColumns, Table.PrefixColumns, Table.TransformColumnNames
• Reordenación: Table.ReorderColumns

Filtrado de filas

Use la función de M Table.SelectRows para filtrar según las siguientes condiciones:

• Igualdad y desigualdad
• Comparaciones numéricas, de texto y de fechas (pero no de fecha y hora)
• Información numérica, como Number.IsEven/Odd
• Contención de texto mediante Text.Contains, Text.StartsWith o Text.EndsWith
• Intervalos de fechas (incluidas todas las funciones de fecha "IsIn")
• Combinaciones de estas mediante las condiciones and, or o not

Adición y transformación de columnas

Las siguientes funciones de M agregan o transforman columnas: Table.AddColumn, Table.TransformColumns, Table.ReplaceValue, Table.DuplicateColumn. A continuación se muestran las funciones de transformación admitidas.

• Aritmética numérica
• Concatenación de texto
• Aritmética de fecha y hora (operadores aritméticos, Date.AddDays, Date.AddMonths, Date.AddQuarters, Date.AddWeeks, Date.AddYears)
• Las duraciones se pueden usar para operaciones aritméticas de fecha y hora, pero se deben transformar en otro tipo antes de que se escriban en un receptor (operadores aritméticos, #duration, Duration.Days, Duration.Hours, Duration.Minutes, Duration.Seconds, Duration.TotalDays, Duration.TotalHours, Duration.TotalMinutes, Duration.TotalSeconds)
• La mayoría de las funciones numéricas estándar, científicas y trigonométricas (todas las funciones de Operaciones, Redondeo y Trigonometría, excepto Number.Factorial, Number.Permutations y Number.Combinations)
• Reemplazo (Replacer.ReplaceText, Replacer.ReplaceValue, Text.Replace, Text.Remove)
• Extracción de texto posicional (Text.PositionOf, Text.Length, Text.Start, Text.End, Text.Middle, Text.ReplaceRange, Text.RemoveRange)
• Formato básico de texto (Text.Lower, Text.Upper, Text.Trim/Start/End, Text.PadStart/End, Text.Reverse)
• Funciones de fecha y hora (Date.Day, Date.Month, Date.YearTime.Hour, Time.Minute, Time.Second, Date.DayOfWeek, Date.DayOfYear, Date.DaysInMonth)
• Expresiones if (pero las ramas deben tener tipos coincidentes)
• Filtros de fila como columna lógica
• Constantes de número, texto, lógica, fecha, y fecha y hora

Combinar o unir tablas

• Power Query generará una combinación anidada (Table.NestedJoin; los usuarios también pueden escribir manualmente Table.AddJoinColumn). Los usuarios deben expandir la columna de combinación anidada en una combinación no anidada (Table.ExpandTableColumn no se admite en ningún otro contexto).
• Aunque la función de M Table.join puede escribirse directamente para no tener que realizar un paso adicional de expansión, el usuario debe asegurarse de que no haya nombres de columna duplicados entre las tablas combinadas.
• Tipos de combinación admitidos: Inner, LeftOuter, RightOuter y FullOuter.
• Se admiten Value.Equals y Value.NullableEquals como comparadores de igualdad de claves.

Agrupar por

Utilice Table.Group para agregar valores.

• Debe usarse con una función de agregación
• Funciones de agregación admitidas: List.Sum, List.Count, List.Average, List.Min, List.Max, List.StandardDeviation, List.First y List.Last.

Ordenación

Use Table.Sort para ordenar los valores.

Reducir filas

Mantener y quitar la parte superior, mantener el rango (funciones M correspondientes, solo se admiten recuentos, no condiciones: Table.FirstN, Table.Skip, Table.RemoveFirstN, Table.Range, Table.MinN, Table.MaxN)

Funciones conocidas no admitidas

Función	Estado
Table.PromoteHeaders	No compatible. Se puede lograr el mismo resultado si se establece "Primera fila como encabezado" en el conjunto de resultados.
Table.CombineColumns	Se trata de un escenario habitual que no se admite directamente, pero se puede realizar si se agrega una nueva columna que concatene dos columnas concretas. Por ejemplo, Table.AddColumn(RemoveEmailColumn, "Name", each [FirstName] & " " & [LastName])
Table.TransformColumnTypes	Esto se admite en la mayoría de los casos. No se admiten los siguientes escenarios: transformar una cadena al tipo de moneda, transformar una cadena al tipo de hora, transformar una cadena al tipo de porcentaje y la transformación regional.
Table.NestedJoin	Si realiza una combinación, se producirá un error de validación. Las columnas deben expandirse para que funcione.
Table.RemoveLastN	No se admite la eliminación de las filas inferiores.
Table.RowCount	No se admite, pero se puede lograr agregando una columna personalizada que contenga el valor 1 y sumando después esa columna con List.Sum. Se admite Table.Group.
Control de errores de nivel de fila	El control de errores de nivel de fila no se admite actualmente. Por ejemplo, para filtrar los valores no numéricos de una columna, una opción sería transformar la columna de texto en números. Cada celda que no se pueda transformar tendrá un estado de error y deberá filtrarse. Este escenario no es posible en M con escalabilidad horizontal.
Table.Transpose	No compatible

Soluciones alternativas de script M

SplitColumn

A continuación se muestra una alternativa para dividir por longitud y por posición.

• Table.AddColumn(Source, "Primeros caracteres", each Text.Start([Email], 7), type text)
• Table.AddColumn(#"Primeros caracteres insertados", "Rango de texto", each Text.Middle([Email], 4, 9), type text)

Se puede acceder a esta opción desde "Extraer" en la cinta de opciones.

Table.CombineColumns

• Table.AddColumn(RemoveEmailColumn, "Nombre", each [FirstName] & " " & [LastName])

Elementos dinámicos

• Seleccione la transformación dinámica en el editor de PQ y seleccione la columna dinámica.

• A continuación, seleccione la columna de valor y la función de agregado.

• Al hacer clic en Aceptar, verá los datos en el editor actualizados con los valores dinamizados.
• También verá un mensaje de advertencia que indica que la transformación puede no ser compatible.
• Para corregir esta advertencia, expanda la lista dinámica manualmente mediante el editor de PQ.
• Seleccione la opción Editor avanzado en la cinta de opciones.
• Expansión manual de la lista de valores dinamizados
• Reemplace List.Distinct() por la lista de valores como este:

#"Pivoted column" = Table.Pivot(Table.TransformColumnTypes(#"Changed column type 1", {{"genres", type text}}), {"Drama", "Horror", "Comedy", "Musical", "Documentary"}, "genres", "Rating", List.Average)
in
  #"Pivoted column"

Formato de columnas de fecha y hora

Para establecer el formato de fecha y hora al usar Power Query ADF, siga estos pasos para establecer el formato.

1. Seleccione la columna en la interfaz de usuario de Power Query y elija Tipo de cambio > Fecha y hora.
2. Verá un mensaje de advertencia.
3. Abra el Editor avanzado y cambie TransformColumnTypes a TransformColumns. Especifique el formato y la referencia cultural en función de los datos de entrada.

#"Changed column type 1" = Table.TransformColumns(#"Duplicated column", {{"start - Copy", each DateTime.FromText(_, [Format = "yyyy-MM-dd HH:mm:ss", Culture = "en-us"]), type datetime}})

Contenido relacionado

Obtenga información sobre cómo crear una consulta de Power Query de limpieza y transformación de datos en ADF.