Transformations courantes
En bref
- Objectifs : distinguer une transformation d'un ajout de colonne ; trouver la commande adaptée dans le ruban ; enchaîner plusieurs transformations pour remettre à plat un tableau complexe.
- Prérequis : pages « Découvrir l'éditeur Power Query » et « Profiler et nettoyer les données ».
- Niveau : découverte à intermédiaire.
Pourquoi ?
La plupart des préparations de données se font sans écrire une ligne de code : supprimer des lignes d'en-tête, remplir des cellules fusionnées, fractionner un code, regrouper des lignes. Encore faut-il savoir où se trouve chaque commande et ce qu'elle produit, en particulier si elle modifie la colonne existante ou en crée une nouvelle.
Notions clés
Transformer ou ajouter une colonne ?
Beaucoup de commandes existent à la fois dans l'onglet Transformer et dans l'onglet Ajouter une colonne (menus Format, Extraire, Date, Arrondi...).
- Depuis l'onglet Transformer, la commande modifie la colonne sélectionnée ; aucune colonne n'est ajoutée. Le clic droit sur un en-tête propose les commandes de cet onglet.
- Depuis l'onglet Ajouter une colonne, le résultat est placé dans une nouvelle colonne et la colonne d'origine reste intacte ; vous pourrez la supprimer ensuite.
Exemple avec Format > MAJUSCULES depuis l'onglet Transformer : la colonne Départements passe en majuscules, aucune colonne n'est ajoutée.
La même commande depuis l'onglet Ajouter une colonne crée une colonne nommée MAJUSCULES ; la colonne Départements n'est pas modifiée.
Référentiel des commandes du ruban
| Onglet et groupe | Commandes | Voir aussi |
| Accueil > Fermer | Fermer et appliquer, Appliquer, Fermer | Découvrir l'éditeur |
| Accueil > Nouvelle requête | Nouvelle source, Sources récentes, Entrer des données | Partie 1 |
| Accueil > Sources de données | Paramètres de la source de données | Paramètres de requête |
| Accueil > Paramètres | Gérer les paramètres, Modifier les paramètres, Nouveau paramètre | Paramètres de requête |
| Accueil > Requête | Actualiser l'aperçu, Propriétés, Éditeur avancé, Gérer (Supprimer, Dupliquer, Référence) | Découvrir l'éditeur |
| Accueil > Gérer les colonnes | Choisir les colonnes, Atteindre la colonne, Supprimer les colonnes, Supprimer les autres colonnes | |
| Accueil > Réduire les lignes | Conserver les lignes (premières, dernières, plage, doublons, erreurs), Supprimer les lignes (du haut, du bas, en alternance, doublons, lignes vides, erreurs) | Profiler et nettoyer |
| Accueil > Trier | Tri croissant, Tri décroissant | |
| Accueil > Combiner | Fusionner des requêtes, Ajouter des requêtes (chacune aussi « comme nouvelle »), Combiner les fichiers | Fusionner, Ajouter, Combiner des fichiers |
| Transformer > Tableau | Regrouper par, Utiliser la première ligne pour les en-têtes, Transposer, Inverser les lignes, Compter les lignes | |
| Transformer > Toute colonne | Type de données, Détecter le type de données, Renommer, Remplacer les valeurs, Remplacer les erreurs, Remplir (vers le bas, vers le haut), Pivoter la colonne, Dépivoter les colonnes, Convertir en liste | Dépivoter |
| Transformer et Ajouter une colonne > Colonne de texte | Fractionner la colonne (par délimiteur, nombre de caractères, position, transitions), Format (minuscules, MAJUSCULES, 1re lettre en majuscule, Supprimer les espaces, Nettoyer, préfixe, suffixe), Fusionner les colonnes, Extraire (longueur, premiers ou derniers caractères, plage, texte avant, après ou entre délimiteurs), Analyser (JSON, XML) | |
| Transformer et Ajouter une colonne > Colonne de nombre | Statistiques, Standard (ajouter, multiplier...), Scientifique, Trigonométrie, Arrondi, Information (pair, impair, signe) | |
| Transformer et Ajouter une colonne > Colonne de date et heure | Date (âge, année, mois, début et fin de période...), Heure, Durée | |
| Transformer > Scripts | Exécuter le script R, Exécuter le script Python | |
| Ajouter une colonne > Général | Colonne à partir d'exemples, Colonne personnalisée, Appeler une fonction personnalisée, Colonne conditionnelle, Colonne d'index (à partir de 0, de 1, personnalisée), Dupliquer la colonne | Langage M |
Quelques transformations à connaître
- Regrouper par (Transformer > Regrouper par) : agrège les lignes par une ou plusieurs colonnes (somme, nombre de lignes, minimum, maximum...). L'option Avancé permet plusieurs regroupements et agrégations. Source
- Remplir vers le bas : recopie la dernière valeur non nulle dans les cellules vides situées en dessous, typiquement pour des cellules fusionnées dans Excel. Source
- Fractionner la colonne par délimiteur : découpe un texte en colonnes, ou en lignes grâce aux Options avancées. Source
- Colonne conditionnelle : construit une colonne par une suite de conditions « Si ... alors ... sinon », sans écrire de code. Source
- Colonne à partir d'exemples : vous saisissez le résultat attendu sur quelques lignes, Power Query propose la formule. Source
- Colonne d'index : numérote les lignes, par exemple pour comparer une ligne à la précédente (calcul d'un délai moyen entre deux pannes).
Chaque commande génère une étape en langage M, visible dans la barre de formule. Par exemple, une colonne d'index à partir de 1, puis une colonne personnalisée :
= Table.AddIndexColumn(#"Type modifié", "Index", 1, 1, Int64.Type)= Table.AddColumn(#"Index ajouté", "Montant TTC", each [Montant HT] * 1.2, type number)Pas à pas : transformations complexes
Cet exemple remet à plat un tableau d'immatriculations de véhicules : régions et départements en lignes, énergie puis puissance sur deux lignes d'en-têtes, avec des sous-totaux.
1. Dans Power BI Desktop, Accueil > Obtenir les données > Classeur Excel, puis sélectionnez le fichier.
2. Dans le Navigateur, cochez la feuille data, puis cliquez sur Transformer les données.
3. Appliquez les transformations suivantes :
| Étape | Comment faire |
| 1. Supprimer l'étape Type modifié | Croix à gauche de l'étape Type modifié |
| 2. Supprimer l'étape En-têtes promus | Croix à gauche de l'étape En-têtes promus |
| 3. Supprimer les 2 premières lignes | Accueil > Supprimer les lignes > Supprimer les lignes du haut, saisir 2 |
| 4. Transposer la table (les deux premières lignes contiennent l'énergie et la puissance, qu'il faut obtenir en colonnes) | Transformer > Transposer |
| 5. Remplir vers le bas Column1 | Sélectionner Column1, Transformer > Remplir > Vers le bas |
| 6. Exclure « Total » de Column1 | Décocher « Total » dans le filtre de Column1 |
| 7. Fusionner Column1 et Column2 avec un point-virgule comme séparateur | Sélectionner les deux colonnes, Transformer > Fusionner les colonnes, séparateur Point-virgule |
| 8. Transposer de nouveau la table | Transformer > Transposer |
| 9. Remplir vers le bas Column1 | Transformer > Remplir > Vers le bas |
| 10. Exclure null et « Total » de Column2 | Décocher « null » et « Total » dans le filtre de Column2 |
| 11. Promouvoir les en-têtes, puis supprimer l'étape Type modifié ajoutée automatiquement | Transformer > Utiliser la première ligne pour les en-têtes |
| 12. Dépivoter les autres colonnes | Sélectionner Régions et Départements, Transformer > Dépivoter les colonnes > Dépivoter les autres colonnes |
| 13. Fractionner Attribut au point-virgule, puis supprimer l'étape Type modifié ajoutée automatiquement | Transformer > Fractionner la colonne > Par délimiteur, séparateur Point-virgule |
| 14. Supprimer le séparateur resté à droite des noms de colonnes Régions et Départements | Double-cliquer sur chaque en-tête et corriger le nom |
| 15. Renommer Attribut.1 en Énergie et Attribut.2 en Puissance | Double-cliquer sur chaque en-tête |
| 16. Renommer Valeur en Qte | Double-cliquer sur l'en-tête |
| 17. Typer Qte en nombre entier | Icône de type de la colonne Qte > Nombre entier |
4. Renommez la requête « Immatriculations », puis Accueil > Fermer et appliquer.
Pièges et bonnes pratiques
- Les commandes qui dépendent de la position (supprimer les lignes du haut, transposer) supposent que la structure du fichier ne change pas ; contrôlez-la à chaque nouvelle livraison.
- Supprimez les étapes Type modifié générées en cours de route et typez une seule fois, à la fin.
- Filtrez les lignes et supprimez les colonnes inutiles le plus tôt possible : les étapes suivantes traitent moins de données.
- Vérifiez le séparateur choisi lors d'une fusion de colonnes : il doit être absent des données pour que le fractionnement ultérieur fonctionne.
À retenir
- Onglet Transformer : la colonne est modifiée ; onglet Ajouter une colonne : une nouvelle colonne est créée.
- Transposer, Remplir vers le bas et Dépivoter remettent à plat la plupart des tableaux conçus pour l'impression.
- Chaque commande du ruban écrit une étape M que vous pouvez lire dans la barre de formule.
- Filtrer tôt, typer tard.
Pour aller plus loin
- Regrouper par ou résumer des lignes. Source
- Ajouter une colonne à partir d'exemples. Source
- Ajouter une colonne personnalisée. Source
- Bonnes pratiques lors de l'utilisation de Power Query. Source
Exercice
Exo 2.2 | Parc automobile (page Mise en pratique).