Power Query


On peut se représenter un vecteur comme un tableau d’une seule colonne dont les valeurs sont toutes du même type (numérique, texte etc…) Ce qui est particulièrement intéressant c’est que chaque élément du vecteur est ordonnancé, ce qui permet d’y accéder directement en connaissant sa position dans le vecteur. Pour créer un vecteur il faut utiliser la notation c() : c(« jean », »paul », »raymond ») Chaque vecteur peut être affecté à une variable pour être stocké en mémoire et utilisé ultérieurement : a= c(1,2,3) b= c(« jean », »paul », »raymond ») c = c(TRUE,FALSE,TRUE) Nous pouvons affecter un nom à chaque  valeur d’un vecteur en utilisant la fonction names(), cela permet en quelque sorte d’avoir un nom d’entête de ligne. poker_vecteur <- c(140, -50, 20, -120, 240); roulette_vecteur <- c(-24, -50, 100, -350, 10); names(poker_vecteur) <- c(« Lundi », « Mardi », « Mercredi », « Jeudi », « Vendredi »); names(roulette_vecteur) <- c(« Lundi », « Mardi », « Mercredi », « Jeudi », « Vendredi »); df = data.frame(names(poker_vecteur),poker_vecteur) En utilisant la source de donnée Script R dans PowerBI et en écrivant le code précédent dans la fenêtre de l’éditeur, on obtient la table suivante : Sélections de lignes d’un vecteur La sélection des lignes d’un vecteur s’opère en utilisant les crochets et en encadrant le numéro de ligne, pour une sélection simple : poker_vecteur <- c(140, -50, […]

Power Query – Script R – Vecteurs



Dans cet article, nous allons voir comment réaliser des opérations simples en utilisant un script R dans power query. Pour générer une table de données nous allons devoir créer ce que se nomme un dataframe, dans le langage R. Un dataframe est utilisé pour stocker les tables de données, elles-mêmes constituées de colonnes (ou vecteurs en R), de même longueur. En R, la notation pour un vecteur est c(). c(1,2,3) représente donc une colonne de données dont les valeurs des lignes sont 1,2 et 3. Pour affecter un vecteur à une variable, nous pouvons utiliser le signe = ou la notation <-. x = c(1,2,3) x <- c(1,2,3) Pour afficher le résultat sous forme de table, dans Power query, nous devrons utiliser la fonction data.frame(). n = c(2, 3, 5) df = data.frame(n) Ce qui donne dans l’éditeur de powerquery, le code suivant : let Source = R.Execute(« df=c(2,3,5);dataset=data.frame(df) »), dataset = Source{[Name= »dataset »]}[Value] in dataset La constitution d’une table suit la même logique : a = c(3, 4, 5) b = c(« aa », « bb », « cc ») c = c(TRUE, FALSE, TRUE) df = data.frame(a, b, c) Une colonne du dataframe peut aussi être le fruit d’un calcul comme : a = c(3, 4, 5) […]

Power Query – Script R – Créer une table de ...



1
Historiquement nous utilisions la fonction SUMMARIZE() pour créer une table agrégée, en d’autres termes pour créer une table ressemblant aux résultats d’un tableau croisé dynamique, sur laquelle de nouveaux calculs pourraient être appliqués. Cette fonction prend une table en premier argument (la table dont les valeurs de certaines colonnes seront agrégées), puis les colonnes à agréger (ces colonnes peuvent provenir de la table ou de toute table liée à cette dernière par une relation un à plusieurs). Exemple : Si nous avons deux tables : ‘Produits’ et ‘Ventes’, nous mettrons la Table vente (côté plusieurs) en premier argument de la fonction, puis ajouterons les colonnes dont les valeurs identiques doivent être agrégées, avant de finir par la formule de calcul (Nb : ne pas oublier de préciser un nom pour cette nouvelle colonne, ici ‘Quantités vendues’) Quantités vendues par couleur et par fournisseur = SUMMARIZE( ‘ventes’; ‘Produits'[Couleur]; ‘Produits'[Fournisseur]; « Quantités vendues »; SUM(‘Ventes'[Quantité]) )   Pour des questions de performance et de compatibilité, il est préférable de n’utiliser SUMMARIZE(), que pour les colonnes d’agrégat, et pas pour la formule de calcul, cette dernière devant être ajoutée par la formule ADDCOLUMNS() :   ADDCOLUMNS( SUMMARIZE( ‘ventes’; ‘Produits'[Couleur]; ‘Produits'[Fournisseur]; ) ; « Quantités vendues »; CALCULATE(SUM(‘Ventes'[Quantité])) ) […]

Tables agrégées : du bon usage de SUMMARIZECOLUMNS()



Lorsque vous utilisez l’interface de Power Query pour réaliser vos transformations, vous devez savoir, qu’en arrière plan, toutes vos actions sont traduite dans un langage informatique : le M, que nous allons étudier. Si ce n’est déjà fait, vous pouvez télécharger les fichiers sources sur le site de l’excellent  livre M is for (Data) Monkey écrit par Ken Puls et Miguel Escobar  dont nous nous sommes inspiré Télécharger les fichiers pour écrire ce post. Créons une nouvelle requête : Obtenir les données–> Excel–> Ch20 ExamplesUnderstanding M.xlsx Cochez Sales. Cliquez sur modifier. Cliquez sur Editeur. avancé Ce bouton nous permet d’accéder au code M, un peu comme l’explorateur de Macro en visual basic. Décomposons ce bloc de code pour comprendre la structure du langage. let Source = Excel.Workbook(File.Contents(« C:UsersLe mennDesktopMCh20 ExamplesUnderstanding M.xlsx »), null, true), Sales_Sheet = Source{[Item= »Sales »,Kind= »Sheet »]}[Data], # »En-têtes promus » = Table.PromoteHeaders(Sales_Sheet, [PromoteAllScalars=true]), # »Type modifié » = Table.TransformColumnTypes(# »En-têtes promus »,{{« Date », type date}, {« Inventory Item », type text}, {« EmployeeID », Int64.Type}, {« Quantity », Int64.Type}, {« Price », Int64.Type}}) in # »Type modifié » Le code de chaque requête en M doit commencer par le mot clé let. La seconde ligne commence par identifier la première étape en lui affectant un nom, ici Source. Ce bloc de code se contente de récupérer l’ensemble des tables […]

Power Query – M – Structure du langage



Power Query nous permet d’acquérir des données sous forme de tables, à partir du Web. Si ce n’est déjà fait, vous pouvez télécharger les fichiers sources sur le site de l’excellent  livre M is for (Data) Monkey écrit par Ken Puls et Miguel Escobar  dont nous nous sommes inspiré Télécharger les fichiers pour écrire ce post. Commençons tout d’abord par récupérer une Table Html : Obtenir les données –> Web –> https://www.cia.gov/library/publications/the-world-factbook/rankorder/2119rank.html Power Query nous retourne deux éléments : Document et Table 0. Document, retourne le Html de la page, ce qui ne nous intéresse pas, alors que table 0 reprend les données incluses entre les balises Html. Sélectionnons table 0 –> Modifier. Il ne reste plus qu’à faire les transformations d’usage et nous aurons récupéré des informations directement utilisables, depuis une Table Html. Nous pouvons aussi nous connecter directement à des fichiers hébergés sur un serveur Web, à condition de connaitre l’URL Obtenir les données –> Web –> https://www.cia.gov/library/publications/the-world-factbook/rankorder/rawdata_2119.txt L’aperçu nous permet de vérifier que Power Query a correctement déterminé les délimiteurs de colonnes, nous pouvons cliquer sur Modifier et réaliser les modifications d’usage.

Power query – Données Web



1
Power query nous permet d’éviter le travail laborieux réalisé auparavant sous Excel à l’aide de la fonction Recherchev(). Si ce n’est déjà fait, vous pouvez télécharger les fichiers sources sur le site de l’excellent  livre M is for (Data) Monkey écrit par Ken Puls et Miguel Escobar  dont nous nous sommes inspiré Télécharger les fichiers pour écrire ce post. Obtenir les données –> Excel –> Ch09 ExamplesMerge.xlsx Cochez Inventory et Sales Modifier Power query vient de créer deux requêtes distinctes : Inventory et Sales pour acquérir les données. En analysant les deux requêtes, nous pouvons voir qu’elles possèdent une clé commune : [SKU Number]. Nous allons pouvoir fusionner ces requêtes à partir du menu Combiner. Combiner –> Fusionner des requêtes Nous offre deux options : 1) Fusionner des requêtes 2) Fusionner les requêtes comme nouvelles La première option fusionne les données dans l’une des deux requêtes existantes, alors que la seconde crée une nouvelle requête, en maintenant les deux requêtes originales, en l’état. Nous choisirons la seconde option. Choisissez Sales comme première requête et Inventory comme seconde. Puis sélectionnez la colonne [SKU Number] dans les deux requêtes. Dans tpe de jointure, choisissez Externe Droite : Cette jointure nous retournera, tous les […]

Power query – Fusionner des tables



Power Query possède un ensemble de fonctions pour manipuler les chaines de caractères. Ces fonctions sont hébergées au sein de la classe Text.   Text.Start(« Hello »,2) retourne He   Text.End(« Hello »,2) retourne lo   Text.Length(« Hello ») retourne 5   Text.PositionOf(« Hello », »o ») retourne 4   Text.At(« Hello »,1) retourne e   Text.AfterDelimiter(« Hello », »l ») retourne lo   Text.BeforeDelimiter(« Hello », »l ») retourne He   Text.BetweenDelimiters(« Hello », »e », »o ») retourne ll   Text.PositionOf(« Hello », »y ») retourne -1 (n’existe pas)   (Power Query compte à partir de zéro)   Text.Range(« Hello »,2,2) retourne ll   Text.Range(« Hello »,Text.Length(« Hello »)-2,2) retourne lo (les deux derniers caractères de n’importe quelle chaine.)   Text.Combine({« H », »e », »l », »l », »o »}, »-« ) retourne H-e-l-l-o   Text.Contains(« Hello », »lo ») retourne True   Text.Format(« C’est l’heure de dire #[ZZZ] »,[ZZZ= »Hello »]) retourne C’est l’heure de dire Hello. Text.StartsWith(« Hello », »He ») retourne True   Text.EndsWith(« Hello », »lo ») retourne True   Text.Insert(« Hello »,1, »XXX ») retourne HXXXello   Text.Lower(« Hello ») retourne hello   Text.Upper(« Hello ») retourne HELLO   Text.PadEnd(« Hello »,10, ». ») retourne Hello…..   Text.PadStart(« Hello »,10, ». ») retourne …..Hello   Text.Proper(« hello world I am jack ») retourne Hello World I Am Jack   Text.Remove(« He;l-l-o »,{« -« , »; »}) retourne Hello   Text.RemoveRange(« Hello »,2,2) retourne Heo   Text.Repeat(« Hello »,2) retourne HelloHello   Text.Replace(« Hello », »Hello », »Jack ») retourne Jack   Text.ReplaceRange(« Hello »,2,2, »XX ») retourne HeXXo   Text.Split(« H-ello », »-« ) retourne une liste {« H », »ello »}   Text.Trim(« Hello »,{« H », »e », »o »}) retourne ll   Text.TrimStart(« Hello He »,{« H », »e », »o »}) retourne llo He   Text.TrimEnd(« Hello He »,{« H », »e », »o »}) retourne Hello Toutes ces fonctions peuvent évidemment recevoir des entêtes de colonne en entrée : Text.StartsWith([Champs1], »He »], ou […]

Power Query – M – Fonctions Texte



Commençons par accéder aux données : Si ce n’est déjà fait, vous pouvez télécharger les fichiers sources sur le site de l’excellent  livre M is for (Data) Monkey écrit par Ken Puls et Miguel Escobar  dont nous nous sommes inspiré Télécharger les fichiers pour écrire ce post. Obtenir les données –>Texte/CSV–>Ch07 Examples/GL Jan-Mar.TXT Modifier Power query charge alors les données dans une seule colonne, ce qui est la meilleure solution pour un fichier non structuré. Nous allons devoir pratiquer les modifications manuellement : Les dix premières lignes ne présentent pas d’intérêt, supprimons les : Accueil–>Supprimer les lignes –>Supprimer les lignes du haut–>10 Une fois ces lignes supprimées, nous obtenons une colonne unique avec des données présentées sous forme tabulaire. Il nous reste à déterminer comment fractionner ces colonnes. Il faut d’abord supprimer les espaces inutiles à droite et à gauche : Clic droit–>Transformer–>Supprimer les espaces De plus, nous devons supprimer tous les caractères invisibles : Clic droit –> Transformer –> Nettoyer Maintenant, nous pouvons fractionner les colonnes selon un nombre de caractères : Transformer–>Fractionner la colonne–>Par nombre de caractères–>10–>Une fois, le plus à gauche possible. La colonne date est ainsi isolée, il reste alors à répéter le processus pour les autres […]

Power Query – Fichiers textes non délimités



Bien souvent les données nous parviennent sous forme de tableaux Excel qui ne sont pas directement exploitable par Power BI. Fort heureusement, Power Query est doté de fonctions permettant de traiter ce problème aisément. Commençons par regarder le tableau Excel du Ch06 ExamplesUnPivot.xlsx Pour pouvoir être utilisables par Power Bi, ces données doivent être transformées en une table de deux colonnes : [Category] et [Date] Si ce n’est déjà fait, vous pouvez télécharger les fichiers sources sur le site de l’excellent  livre M is for (Data) Monkey écrit par Ken Puls et Miguel Escobar  dont nous nous sommes inspiré Télécharger les fichiers pour écrire ce post. Chargeons le tableau Excel dans Power Bi. Obtenir les données –> Excel –> Ch06 ExamplesUnPivot.xlsx Selectionnez la feuille Data Click sur modifier Une fois les données chargées dans l’éditeur, nous pouvons pratiques quelques transformations mineures : Supprimer les lignes –> Supprimer les lignes du haut –> 2 Utiliser la première ligne pour les entêtes Supprimer la colonne [Total] Maintenant il est temps de fusionner toutes les colonnes autres que [Sales Category] en une seule. Selectionnez toutes les colonnes autres que [Sales Category] Cliquez sur l’icone –> Supprimer du tableau croisé dynamique uniquement les colonnes sélectionnées Modifier le […]

Power Query – ‘Dé-pivotter’ les données



Nous avons vu dans un article précédent comment agréger des fichiers csv, aujourd’hui nous allons voir comment agréger les feuilles de plusieurs fichiers Excel stockées dans un dossier. Si ce n’est déjà fait, vous pouvez télécharger les fichiers sources sur le site de l’excellent  livre M is for (Data) Monkey écrit par Ken Puls et Miguel Escobar  dont nous nous sommes inspiré Télécharger les fichiers pour écrire ce post. Obtenir les données –> Autres –> Dossier Sélectionnez le dossier Ch05 Examples Cliquez sur Ok Cliquez sur modifier Power Query nous retourne une Table contenant les fichiers binaires dans la premières colonne, ainsi que des informations Lorsque l’on analyse cette table, on se rend compte que Power Query nous a retourné, l’ensemble des fichiers du dossier. Nous devons donc filtrer la table (par extension, ou ici en écartant les fichiers autres que Workbook1 et Workbook2) Ceci fait, nous pouvons supprimer les colonnes autres que [Name] et [Content], le résultat davant ressembler à ceci : A partir de la, nous pouvons développer le contenu en cliquant sur les deux flèches. Power Query a récupéré différents objets contenant des données : des feuilles Excel et des tables, il nous demande de choisir le type […]

Power Query – Combiner des feuilles Excel