M


J’ai récemment été sollicité pour construire un modèle de données permettant de réaliser des analyses sur des cours de bourse. La principale difficulté a consisté à générer les URL spécifiques permettant de récupérer les bonnes valeurs (cours de bourse d’un titre spécifique entre deux dates). Lorsque vous sélectionnez un titre sur Yahoo finance, vous pouvez trouver un lien de téléchargement des donnée (un fichier CSV.) C’est l’URL du lien de téléchargement qui va nous falloir étudier dans le détail : https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1556291578&period2=1587913978&interval=1d&events=history On observant bien on peut identifier trois variables : Le code du titre : AAPL; La date de début des cotations : 1556291578; La date de fin de cotations : 1587913978; Ces deux dates sont au format timestamp, elles sont exprimées en secondes écoulées depuis le premier janvier 1970. Nous allons donc devoir créer une formule permettant de convertir une date en timestamp dans power Query. Ci dessous un exemple pour la date du 01/02/2020 Number.From ( DateTime.From ( #datetime(2020, 1, 2, 0, 0, 0) ) – #duration ( 25569,0,0,0 ) ) * 86400 Création des requêtes dans power query Tout d’abord nous allons créer une requête web : Puis coller l’Url présentée précédemment : Tout d’abord il faut […]

Récupérer les cours de bourse sur Yahoo finance



Comme nous l’avons vu dans les articles précédents, Power Query permet, depuis son interface utilisateur de réaliser très facilement la plupart des taches de chargement et de transformation de vos données. Néanmoins, si vous souhaitez aller plus loin, il vous faudra étudier le langage qui tourne en arrière plan de cette interface utilisateur, le langage M. 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. Pour commencer, le plus simple est d’ajouter une nouvelle colonne : Après avoir renommé la colonne, vous pouvez écrire une formule en langage M, par exemple : Pour vous faciliter la tache, vous n’avez pas à écrire le nom de chaque colonne, puisque l’éditeur vous présente les colonnes disponibles, sur lesquelles vous n’avez qu’à double cliquer. La langage M est très différent des formules Excel auxquelles nous sommes habitués : Les formules sont sensibles à la casse (Majuscules et minuscules sont considérées comme différentes.) Le comptage se fait à partir de zéro (à partir de un dans Excel. Il n’y a pas de conversion […]

Power query – M – Conversions de type



Power Query et son interface permettent de réaliser facilement des transformations simples.   En programmant en langage M, nous pouvons réaliser des transformations plus complexes, mais il faut bien reconnaître que la difficulté dans la programmation devient vite prohibitive. Microsoft l’a semble-t-il bien compris, en intégrant le langage R à Power Bi. Ce langage, utilisé par les data scientists du monde entier, permet de réaliser ces opérations bien plus facilement. Pour utiliser R dans Power Query, vous devrez tout d’abord mettre en place l’environnement sur votre ordinateur. Une fois l’environnement installé, nous allons devoir installer un package R nommé ‘dplyr’ Ouvrez R studio Tapez Fermez Rstudio Voila c’est fait ! Maintenant nous pouvons créer une nouvelle requête dans Power BI : Obtenir les données Web Entrez cette Url : https://raw.githubusercontent.com/genomicsclass/dagdata/master/inst/extdata/msleep_ggplot2.csv Modifier. Transformer –> Utiliser la première ligne pour les entêtes. Sélectionnez les colonnes contenant des chiffres et transformez leur type en Décimal. Normalement vous devriez obtenir ceci :   Maintenant nous allons pouvoir créer une nouvelle étape de transformation en créant un script R.     Copiez-collez ce script : Cliquez sur Ok   Ce script permet d’afficher les six premières lignes du Dataset et de l’affecter à la variable […]

Power Query & R – Transformation avec Dplyr



Il peut être utile (pour documenter une base de données ou visualiser la structure d’un nouveau dataset) d’obtenir un descriptif d’un table. Si vous êtes familiers de R vous connaissez la fonction str(dataset). Avec M et powerquery, nous pouvons obtenir un résultat voisin, en utilisant la fonction Table.Schema(). Dans cet exemple, nous avons référencé la requête iris comme source de données de la seconde requête. Cette dernière nous donne un descriptif de la structure du dataset iris. Pour aller plus loin, nous pouvons utiliser la fonction Table.Profile() qui nous fournira un ensemble de statistiques descriptives sommaires pour chaque critère (similaire à la fonction summary() dans R.) Cette fonction est très utile pour identifier la distribution des données, la volumétrie, les normalisations à réaliser etc…

PowerQuery – Decrire facilement des tables



Power Query met à notre disposition plusieurs objets que vous devez apprendre  à manipuler pour pouvoir programmer correctement. L’objet Table 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 –> Ch19 ExamplesPower Query Objects.xlsx Selectionnez Sales Cliquez sur Modifier Dans l’écran Etapes appliquées, cliquez sur source Nous pouvons voir que la colonne [Data] est de type Table. Nous pouvons avoir le détail de chacune en cliquant à coté du mot Table. En aperçu nous pouvons voir que l’objet est composé de colonnes et de lignes. Nous pouvons générer la table en cliquant sur le mot Table ou développer toutes les tables par fusion, en cliquant sur la flèche double de l’entête de  la colonne [Data]. Listes Une liste est une colonne de données qui peuvent être de type différents. Commençons par créer une liste simple : Obtenir les données –> Requête simple ={1,2,3} Nous pouvons voir que Power query a bien créé une liste et non une table, en identifiant le symbole de liste […]

Power Query – M – Objets



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 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