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

APPLIES TO: Azure Data Factory Azure Synapse Analytics

Sugerencia

Data Factory en Microsoft Fabric es la próxima generación de Azure Data Factory, con una arquitectura más sencilla, inteligencia artificial integrada y nuevas características. Si no está familiarizado con la integración de datos, comience con Fabric Data Factory. Las cargas de trabajo de ADF existentes pueden actualizarse a Fabric para acceder a nuevas funcionalidades en ciencia de datos, análisis en tiempo real e informes.

• Start a Fabric evaluación gratuita.
• Upgrade de Azure Data Factory a Data Factory en Microsoft Fabric

La limpieza y transformación de datos en Azure Data Factory permite realizar la preparación y transformación de datos ágiles sin código a escala en la nube mediante la traducción de scripts de Power Query M en Data Flow script. ADF se integra con Power Query Online y hace que las funciones Power Query M estén disponibles para la limpieza de datos a través de la ejecución de Spark mediante la infraestructura de Spark de flujo de datos.

Actualmente no se admiten todas las funciones Power Query M para la limpieza de datos a pesar de estar disponible 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 las funciones Power Query M admitidas.

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íaexcepto 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 Editor avanzado opción 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 conjuntos para establecer el formato.

1. Seleccione la columna en la interfaz de usuario de Power Query y elija Cambiar tipo > fecha y hora.
2. Verá un mensaje de advertencia.
3. Abra 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

Aprenda a crear un Power Query de limpieza de datos en ADF.