Fonctions de transformation dans Power Query pour le data wrangling

  • Article

S’APPLIQUE À : Azure Data Factory Azure Synapse Analytics

Conseil

Essayez Data Factory dans Microsoft Fabric, une solution d’analyse tout-en-un pour les entreprises. Microsoft Fabric couvre tous les aspects, du déplacement des données à la science des données, en passant par l’analyse en temps réel, l’aide à la décision et la création de rapports. Découvrez comment démarrer un nouvel essai gratuitement !

Le data wrangling dans Azure Data Factory vous permet d’effectuer un rassemblement de données brutes à l’analyse et une préparation agile de données sans code à l’échelle du cloud en convertissant des scripts Power Query M en un script de flux de données. ADF s’intègre à Power Query Online et rend les fonctions Power Query M disponibles pour le data wrangling via l’exécution Spark en utilisant l’infrastructure Spark du flux de données.

Actuellement, toutes les fonctions Power Query M ne sont pas prises en charge pour le rassemblement de données brutes à l’analyse, bien qu’elles soient disponibles pendant la création. Lors de la génération de vos compositions (« mash-up ») , le message d’erreur suivant s’affiche si une fonction n’est pas prise en charge :

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

Voici une liste des fonctions Power Query M prises en charge.

Gestion des colonnes

Filtrage de lignes

Utilisez la fonction M Table.SelectRows pour filtrer avec les conditions suivantes :

Ajout et transformation de colonnes

Les fonctions M suivantes ajoutent ou transforment les colonnes : Table.AddColumn, Table.TransformColumns, Table.ReplaceValue, Table.DuplicateColumn. Voici ci-après les fonctions de transformation prises en charge.

Fusion/jointure de tables

  • Power Query génère une jointure imbriquée (table.NestedJoin ; les utilisateurs peuvent également écrire manuellement Table.AddJoinColumn). Les utilisateurs doivent alors développer la colonne de jointure imbriquée dans une jointure non imbriquée (aucune prise en charge de Table.ExpandTableColumn dans un autre contexte).
  • La fonction M Table.Join peut être écrite directement afin d’éviter la nécessité d’une étape d’expansion supplémentaire, mais l’utilisateur doit s’assurer qu’il n’existe aucun nom de colonne en double au sein des tables jointes
  • Types de jointures pris en charge : Inner, LeftOuter, RightOuter, FullOuter
  • Value.Equals et Value.NullableEquals sont pris en charge en tant que comparateurs d’égalité clés

Regrouper par

Utilisez Table.Group pour agréger des valeurs.

Tri

Utilisez Table.Sort pour trier les valeurs.

Réduction de lignes

Conserver et supprimer les premiers éléments, Conserver la plage (fonctions M correspondantes, qui prennent uniquement en charge les nombres, pas les conditions : Table.FirstN, Table.Skip, Table.RemoveFirstN, Table.Range, Table.MinN, Table.MaxN)

Fonctions connues non prises en charge

Fonction Statut
Table.PromoteHeaders Non pris en charge. Le même résultat peut être obtenu en définissant « Première ligne comme en-tête » dans le jeu de données.
Table.CombineColumns Il s’agit d’un scénario courant qui n’est pas directement pris en charge mais qui peut être obtenu en ajoutant une nouvelle colonne qui concatène deux colonnes données. Par exemple, Table.AddColumn(RemoveEmailColumn, "Name", each [FirstName] & " " & [LastName])
Table.TransformColumnTypes Ce scénario est généralement pris en charge. Les scénarios suivants ne sont pas pris en charge : transformation de chaîne en type Devise, transformation de chaîne en type Heure, transformation de chaîne en type Pourcentage et transformation avec des paramètres régionaux.
Table.NestedJoin Effectuer une simple jointure entraînera une erreur de validation. Les colonnes doivent être développées pour que l’opération fonctionne.
Table.RemoveLastN La suppression des lignes du bas n’est pas prise en charge.
Table.RowCount Non pris en charge, mais peut être obtenu en ajoutant une colonne personnalisée contenant la valeur 1, puis en agrégeant cette colonne avec List.Sum. Table.Group est pris en charge.
Gestion des erreurs au niveau des lignes La gestion des erreurs au niveau des lignes n’est pas prise en charge actuellement. Par exemple, pour exclure les valeurs non numériques d’une colonne, une méthode consiste à transformer la colonne de texte en nombre. Les cellules qui ne peuvent pas être transformées sont dans un état d’erreur et doivent être filtrées. Ce scénario n’est pas possible dans M mis à l’échelle.
Table.Transpose Non pris en charge

Solutions de contournement de script M

SplitColumn

Une alternative pour le fractionnement par longueur et par position est reprise ci-dessous

  • Table.AddColumn(Source, "First characters", each Text.Start([Email], 7), type text)
  • Table.AddColumn(#"Inserted first characters", "Text range", each Text.Middle([Email], 4, 9), type text)

Cette option est accessible à partir de l’option Extraire du ruban

Power Query Add Column

Table.CombineColumns

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

Pivots

  • Sélectionnez la Transformation de tableau croisé dynamique dans l’éditeur PQ et sélectionnez la colonne de votre tableau croisé dynamique

Power Query Pivot Common

  • Ensuite, sélectionnez la colonne valeur et la fonction d’agrégation

Power Query Pivot Selector

  • Lorsque vous cliquez sur OK, vous verrez les données dans l’éditeur mis à jour avec les valeurs croisées dynamiques
  • Vous verrez également un message d’avertissement indiquant que la transformation ne peut pas être prise en charge
  • Pour résoudre cet avertissement, développez la liste pivotée manuellement à l’aide de l’éditeur PQ
  • Sélectionner Éditeur avancé option depuis le ruban
  • Développer manuellement la liste des valeurs croisées dynamiques
  • Remplacez List.Distinct() par la liste de valeurs comme suit :
#"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"

Mise en forme des colonnes de date/heure

Pour définir le format de date/heure lors de l’utilisation d’ADF Power Query, suivez ces jeux.

Power Query Change Type

  1. Sélectionnez la colonne dans l’interface utilisateur Power Query, puis choisissez Modifier le type > Date/Heure.
  2. Un message d’avertissement s’affiche
  3. Ouvrez Éditeur avancé et remplacez TransformColumnTypes par TransformColumns. Spécifiez le format et la culture en fonction des données d’entrée.

Power Query Editor

#"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}})

Contenu connexe

Découvrez comment créer un data wrangling Power Query dans ADF.