Les formules Power Query offrent de nombreuses possibilités de manipulation de données sur Excel et Power Query. Découvrez tout ce que vous devez savoir sur les différents types et leur utilisation…
Avec Power Query, les utilisateurs d’Excel et Power BI peuvent transformer, nettoyer, combiner et interroger les données en provenance de multiples sources.
Son interface intuitive rend l’analyse de données accessible au plus grand nombre au sein d’une entreprise. L’éditeur de requêtes ne nécessite pas de code, et c’est ce qui permet une démocratisation de la Business Intelligence.
L’outil propose même des outils de nettoyage de données automatiques pour la détection et la suppression des doublons, la correction des erreurs de données, la suppression des espaces ou la normalisation des dates.
De plus, Power Query propose également des outils de nettoyage de données automatiques tels que la détection et la suppression des doublons, la correction des erreurs de données, la suppression des espaces, la normalisation des dates, etc. Ces outils permettent aux utilisateurs de nettoyer les données sans avoir besoin d’écrire de formules.
Cependant, afin de manipuler les données de façon plus efficace et précise, les utilisateurs avancés peuvent se servir des formules Power Query. Elles sont écrites en langage M.
Ces formules peuvent servir à effectuer des calculs, des manipulations de texte, des agrégations de données importées ou d’autres opérations comme la fusion, le filtrage, le tri ou encore le formatage.
Puissantes et flexibles, les formules Power Query permettent de personnaliser les transformations de données en fonction des besoins spécifiques. À travers cet article, vous allez découvrir les différentes catégories et leur fonctionnement !
Qu’est-ce que le langage M ?
Le langage de programmation M ou Power Query Formula Language est utilisé dans Power Query pour décrire les étapes de transformation de données. Il s’agit d’un langage fonctionnel, à savoir basé sur des fonctions prenant des arguments et renvoyant des valeurs.
On utilise ce langage sur Power Query pour écrire des formules : des expressions décrivant les transformations de données à effectuer. C’est un puissant outil pour l’ETL et l’analyse de données.
Sa syntaxe est simple et intuitive, ce qui le rend facile à apprendre pour les personnes maniant déjà d’autres langages de programmation.
Les différentes fonctions du langage M sont organisées en bibliothèques, regroupées par thème et par catégorie.
Quelles sont les formules de transformation de texte ?
Pour nettoyer et transformer les données textuelles, on utilise les formules de transformation de texte comme LEFT, RIGHT, UPPER, LOWER, PROPER, SUBSTITUTE, TRIM, CONCATENATE et LEN.
Elles permettent de manipuler très facilement les chaînes de caractères telles que les noms, les adresses et les numéros de téléphone. Il est aussi possible de supprimer des caractères indésirables comme les espaces, les tirets ou les parenthèses.
Par exemple, si une liste de noms contient des espaces inutiles, la formule TRIM permet de les supprimer. De même, la formule SUBSTITUTE permet de supprimer directement tous les espaces.
Les formules de transformation de dates
Pour manipuler les données de date et d’heure, on utilise les formules de transformations de date comme DATE, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, NOW et TODAY.
Ces formules servent à manipuler facilement les données temporelles, ou à les transformer dans différents formats en fonction des besoins.
Par exemple, si vous avez une liste de dates dans différents formats, la formule DATEVALUE permet de les transformer en un format standard.
Un autre exemple de cas d’usage est l’extraction du mois dans chacune de ces dates. Il suffit alors d’utiliser la formule MONTH.
Les formules de transformation de nombres
La manipulation de données numériques s’effectue avec les formules Power Query de transformation de nombres comme SUM, AVERAGE, MIN, MAX, COUNT et IF.
Le but est de calculer des statistiques de base, ou d’effectuer des opérations mathématiques sur les données numériques.
Prenons l’exemple d’une liste de prix contenant une colonne pour chaque produit. Il est possible d’appliquer la formule SUM dans le but de calculer le total des ventes pour chacun d’entre eux.
Et pour extraire les données de prix supérieures à une certaine valeur, rien de plus simple : on peut utiliser la formule IF.
Les formules de recherche et de filtre
Au sein d’une liste de données, il est parfois nécessaire d’extraire des informations spécifiques. Pour ce type de tâches, on peut utiliser les formules de recherche et de filtre comme VLOOKUP, HLOOKUP, INDEX, MATCH, FILTER et SORT.
Elles permettent de trouver très rapidement des informations spécifiques dans un tableau ou autre jeu de données.
Par exemple, sur une liste de noms de produits, la formule VLOOKUP peut servir à trouver le prix de chacun. Il est aussi possible de trier la liste par ordre alphabétique avec SORT.
Les formules de fusion et de jointure
Dans le but de combiner de multiples données issues de sources diverses, on peut utiliser les formules de fusion et jointure comme JOIN, MERGE, APPEND et UNION.
Ceci permet de réunir facilement toutes les données au sein d’un unique tableau. Une opération idéale pour bénéficier d’une vue d’ensemble complète.
Prenons l’exemple d’une liste de ventes provenant de multiples régions différentes. Afin d’y voir plus clair, on peut utiliser la formule MERGE pour combiner toutes les ventes en une seule table. Avec la formule APPEND, il est possible d’ajouter une nouvelle colonne à ce tableau.
Les formules de tableaux croisés dynamiques
Les tableaux croisés dynamiques sont couramment utilisés pour l’analyse de données sur Excel et Power BI.
Or, il est possible de créer de tels tableaux à partir de larges quantités de données à l’aide de formules comme PIVOT, GROUP BY ou AGGREGATE.
Ceci peut s’avérer utile, par exemple si vous avez une vaste liste de ventes et souhaitez créer un tableau pour voir les ventes par région. Il suffit alors d’utiliser la formule PIVOT. De même, la formule GROUP BY permet de regrouper les ventes par mois.
Comment utiliser les formules Power Query ?
Pour utiliser les formules Power Query, voici la marche à suivre. Ouvrez tout d’abord l’éditeur Power Query depuis Excel ou Power BI.
Vous pouvez ensuite charger les données à transformer en utilisant l’une des options de chargement proposée par Power Query, par exemple à partir d’un fichier ou d’une source de données.
Pour ajouter une étape de transformation en utilisant une formule, cliquez ensuite sur l’icône « Ajouter une étape » dans le ruban Power Query.
Dans la fenêtre « Formule », vous pouvez saisir votre formule en utilisant le langage M. Elle peut être basée sur les colonnes existantes du jeu de données, mais il est aussi possible de créer de nouvelles colonnes.
Après avoir ajouté la formule, cliquez sur « OK » pour l’appliquer au jeu de données. Vous pouvez ensuite prévisualiser les résultats de la transformation dans la fenêtre Power Query. Il ne reste plus qu’à enregistrer et charger les données !
Conclusion : les formules M étendent le champ des possibles de Power Query
Bien qu’il soit possible d’utiliser l’interface graphique de l’éditeur de requêtes Power Query, les formules en langage M offrent davantage de possibilités.
Toutefois, pour les utiliser, des compétences techniques sont requises. Afin de les acquérir, vous pouvez choisir les formations DataScientest.
Notre cursus dédié à Power BI permet d’apprendre à maîtriser l’outil en seulement 5 jours. Vous découvrirez Power Query, le langage DAX, la DataViz, mais aussi les notions plus avancées comme les DataFlows et l’actualisation incrémentielle.
À la fin du parcours, vous serez capable d’utiliser Power BI pour collecter, analyser les données et créer des tableaux de bord interactifs. En tant que Microsoft Learning Partner, notre organisme vous prépare aussi à l’examen PL-300 permettant d’obtenir la certification Power BI Data Analyst Associate.
Toutes nos formations s’effectuent intégralement en ligne et à distance, et sont éligibles au Compte Personnel de Formation pour le financement. Découvrez DataScientest !
Vous savez tout sur les formules Power Query. Pour plus d’informations sur le même sujet, découvrez notre guide complet sur Power Query et notre dossier consacré à Power BI.
