Power Query pour Microsoft Excel est un programme conçu pour prendre de grands ensembles de données et vous permettre d'apporter des modifications plus facilement. Il a été créé pour simplifier les tâches compliquées.
L'importation de données et l'exécution de modifications de base dans Microsoft Power Query constituent un bon moyen de créer un script et de commencer à apprendre leur fonctionnement. Allons étape par étape pour générer un script, apporter des modifications et voir les résultats.
Avant de plonger, un peu pour en savoir plus sur le langage qui exécute Power Query. Microsoft a créé le langage "M" pour créer Power Query.
Si vous n'êtes pas programmeur, ne vous inquiétez pas. Vous n'avez pas besoin de savoir comment coder en utilisant le langage "M" immédiatement, mais il est utile de connaître son format lorsque vous commencez à apprendre Power Query.
Dans sa forme la plus élémentaire, le langage "M" est structuré comme une "instruction let-in", comme ceci :
Dans un script Power Query, laissez est l'endroit où vous placez le code que vous souhaitez exécuter (entrée) et dans est l'endroit où vous souhaitez l'exécuter (sortie). Le code qui est exécuté à l'intérieur du
let L'instruction peut être composée de variables, d'expressions et de valeurs.
Les variables peuvent être nommées de deux manières. Le premier est avec des mots et sans espaces. Par exemple :"Variable" ou "MaVariable".
Si vous souhaitez utiliser un espace dans le nom de votre variable, commencez simplement par un
# . Par exemple :#"Ma Variable" ou #"Nouvelle Variable". Chacune de ces variables a un espace dans le nom mais est toujours valide.
Gardant cela à l'esprit, commençons par notre script Power Query.
Pour cet exemple, prenons une table que nous créons appelée Fruits.xlsx qui affiche divers fruits et leurs prix. Il y a quelques colonnes simples pour
"Fruits" ,
"Ville" , et
"Prix" .
Le but de ce tutoriel est de prendre le
"Prix" colonne et convertissez les données d'un nombre en un format monétaire à l'aide de Power Query.
Nous avons cette table enregistrée sur le bureau. Créons un nouveau classeur Excel appelé Fruit Data.xlsx pour importer nos données.
Dans la barre d'outils, sélectionnez Données et cliquez sur Obtenir des données . Cela vous donnera des options pour choisir votre source de données, nous voulons qu'elle soit Du fichier> Du classeur .
Cela ouvrira votre explorateur de fichiers. Allez-y et sélectionnez le Fruits.xlsx classeur à importer, puis cliquez sur OK .
Une fois que vous avez cliqué sur OK, le navigateur Power Query s'affiche pour choisir les données dans le classeur. Sélectionnez le Fruit table du classeur, un aperçu des données que vous sélectionnez apparaîtra sur la droite, pour confirmer votre sélection.
Cliquez sur Transformer les données pour ouvrir l'éditeur Power Query.
À première vue, il se passe beaucoup de choses, mais concentrons-nous sur l'obtention du script.
Avec l'éditeur Power Query ouvert, dans votre barre d'outils sous Accueil cliquez sur Éditeur avancé pour ouvrir le script. L'éditeur avancé s'ouvrira et à l'intérieur, vous verrez le script Power Query en langage M.
Le contenu semble très verbeux, alors décomposons le script avant de faire un changement.
Tout d'abord, notez que le script est au format "let/in" que nous avons utilisé avec le langage M. À l'intérieur de "let" se trouvent les lignes de code qui effectuent vos modifications, et le code "in" est l'endroit où les modifications seront générées.
Allons ligne par ligne pour avoir une meilleure idée du code. Cela nécessitera un examen approfondi et il sera peut-être plus facile de comprendre en le regardant plusieurs fois.
letSource =Excel.Workbook(File.Contents("C:\Fruit.xlsx"), null, true),
Fruit_Sheet =Source{[Item="Fruit",Kind="Sheet"]}[Données],
#"En-têtes promus" =Table.PromoteHeaders(Fruit_Sheet, [PromoteAllScalars=true]),
#"Changed Type" =Table.TransformColumnTypes(#"Promoted Headers",{{"Fruit", type text}, {"City", type text}, {"Price", type number}})
dans
#"Type modifié"
Le code commence par un
let instruction, qui commence chaque bloc de code. La première chose que fera Power Query est d'importer le fichier dans une variable appelée
Source qui est simplement le Fruit.xlsx fichier que nous avons choisi d'importer.
Notez la virgule à la fin. Chaque instruction se terminera par une virgule, qui indique simplement au code de lire la ligne suivante.
À partir de là, Power Query prend la feuille de Fruit.xlsx et le définit sur une variable appelée
Fruit_Sheet .
La ligne trois utilise une fonction
Table.PromoteHeaders qui prend les en-têtes de colonne que nous utilisions dans Excel et en fait des en-têtes dans Power Query.
La ligne suivante commençant par
#"Type modifié", Power Query modifie le type des données de chaque colonne. Le
"Fruits" la colonne est changée en
saisir du texte ,
"Ville" est
saisir du texte , et
"Prix" est
numéro de type . Power Query est intelligent et a essayé de déterminer quel type de données est stocké dans la table.
Enfin, le
dans instruction renvoie le code à notre éditeur, en le transmettant à la dernière variable qu'il peut reconnaître qui est
#"Type modifié" .
Il est important de reconnaître une chose ici :chaque variable créée sur une ligne est utilisée d'une manière ou d'une autre sur la ligne suivante. Considérez-le comme une chaîne où chaque maillon est connecté au dernier.
Maintenant que le script est en vue et que nous avons revu le code, faisons notre modification. Rappelez-vous, le but était de prendre toutes les données dans le
"Prix" colonne et changez-la en devise.
Heureusement, nous avons déjà une idée de la façon d'y parvenir. Rappelez-vous cette ligne de code :
#"Changed Type" =Table.TransformColumnTypes(#"En-têtes promus",{{"Fruit", type text}, {"Ville", type text}, {"Prix ", tapez le numéro}})
Nous avons le code qui a changé les types de données pour toute la table ! C'est là que nous pouvons faire notre ajustement et obtenir notre résultat.
Chaque colonne se voit attribuer un type un par un, alors faisons le changement directement dans le code. Actuellement, le
"Prix" la colonne est affectée
numéro de type . Nous voulons que ce soit une devise, alors changeons ce code en
Currency.Type. Voici à quoi cela ressemble :
Cliquez sur Terminé pour revenir à l'écran principal. Le symbole du type est maintenant un signe dollar, ce qui signifie que le type a été converti en devise.
Cela ne faisait qu'effleurer la surface des scripts Power Query utilisant le langage M. Avec plus de puissance pour travailler avec Excel, il est plus facile que jamais de convertir des données en tableaux croisés dynamiques à utiliser pour l'analyse de données ou de créer un graphique dans Excel.