Power Query & Power BI

 

On parle de plus de plus de BI self-service ou de BI dans les mains des utilisateurs finaux, Power Query s’inscrit dans cette lignée. Anciennement nommé Data Explorer c’est un Add in Excel permettant aux utilisateurs de regrouper, combiner et affiner les données pour une meilleure analyse dans Excel. En gros : un ETL pour les Ends Users !

D’autres outils dans Excel viennent combler la chaine décisionnelle pour les utilisateurs finaux, c’est la Power BI :
  • Power Map (Projet GeoFlow) : permets d’explorer vos données en 3D sur une map mondiale.
  • Power Pivot : on ne le présente plus ! Intégré à Excel 2013, il permet la gestion de l’In Memory (dorénavant appelé Xvelocity), du support du multi source de données et de la définition de lien entre les tables…
  • Power View : Intégré à Excel 2013, il permet l’exploration interactive des données et fournit un Reporting adhoc.

POWER_BI

Une application BI pour Windows 8 est désormais disponible dans le Windows Store : Microsoft Power BI. Celle-ci vous permet de visualiser, d’explorer avec une expérience tactile vos rapports présents dans Office 365. Ça me rappelle un POC réalisé « BI Mobile – POC App Win8 » 🙂

 

Démonstration

Afin de présenter les possibilités de la Power BI, rien de mieux qu’une démo !

Le marché de la donnée gratuite (Open Data) est en pleine expansion et les autorités, sociétés françaises sont en retard sur le sujet. Regarder de par vous-même le Graphe des thématiques de l’Open Data français : http://graph.data-publica.com/#age.

J’ai tout de même réussi à trouver un jeu de données avec lequel jouer 😉

Je vais dans un premier temps récupérer des données provenant du Web, les filtrer, les transformer et les regrouper, créer un modèle de données pour enfin les analyser.

 

Power Query

POWER_QUERY

Avec Power Query, un nouveau langage de formule et de requête apparait : le M.

Pour bien démarrer, je vous invite à lire les documents suivants : Microsoft Power Query for Excel Formula Language Specification, Power Query Formula Library Specification.

 

Rapatriement des données sur la ponctualité des TGV à leurs arrivés depuis SNCF Open Data : http://test.data-sncf.com/index.php/sncf-voyages.html.

Importation du dossier avec Power Query :

POWERQUERY_IMPORTFOLDER

POWERQUERY_QUERY_EDITOR

En cliquant sur les deux petites flèches de la colonne “Content”, les données de tous les fichiers du dossier sont combinées :
POWERQUERY_QUERY_EDITOR2

Formatage des données :
POWERQUERY_QUERY_FORMAT

Après quelques modifications, voici le résultat :
POWER_QUERY_EXCEL

Voici la requête générée :

let
    Source = Folder.Files("D:\PULSWEB\Ponctualité TGV à l'arrivée"),
    CombinedBinaries = Binary.Combine(Source[Content]),
    ImportedCSV = Csv.Document(CombinedBinaries),
    SplitColumnDelimiter = Table.SplitColumn(ImportedCSV,"Column1",Splitter.SplitTextByDelimiter(";"),5),
    FirstRowAsHeader = Table.PromoteHeaders(SplitColumnDelimiter),
    FilteredRows = Table.SelectRows(FirstRowAsHeader, each ([Mois] <> "Mois")),
    RenamedColumns = Table.RenameColumns(FilteredRows,{{"D�part", "Depart"}, {"Arriv�e", "Arrive"}, {"Nombre de circulations assur�es", "Nombre de circulations"}, {"Nombre de trains en retard � l'arriv�e", "Nombre de trains en retard"}}),
    ChangedType = Table.TransformColumnTypes(RenamedColumns,{{"Mois", type date}, {"Nombre de circulations", type number}, {"Nombre de trains en retard", type number}})
in
    ChangedType

Remarque : si un nouveau fichier CSV est placé dans le dossier, il sera automatiquement rappatrié !

Il existe d’autres solutions permettant de regrouper plusieurs fichiers sources :

let
    Query1 = let
    Source1 = Csv.Document(File.Contents("D:\PULSWEB\Ponctualité TGV à l'arrivée\Ponctualité TGV à l'arrivée - avril 2013.csv")),
    Source2 = Csv.Document(File.Contents("D:\PULSWEB\Ponctualité TGV à l'arrivée\Ponctualité TGV à l'arrivée - mars 2013.csv")),
    SplitColumnDelimiter = Table.SplitColumn(Source,"Column1",Splitter.SplitTextByDelimiter(";"),5),
    Source = Table.Combine({Source1,Source2})
in
    SplitColumnDelimiter
in
    Query1

 

Rapatriement des données sur les voyageurs montants en gare chaque jour (pour un jour de semaine en période scolaire): http://files.transilien.com/hackdays/Descriptif-fichiers_complementaires_20120525.pdf

POWERQUERY_WEB

Après quelques modifications :

VOYAGEUR

 

Rapatriement des listes des gares : http://data.sncf.com/feedbacks/85497-les-donnees-des-gares-par-rff-et-sncf

Après quelques modifications :
GARES

Remarque : Power Query Formula Language est case sensitive

 

Power Pivot

Maintenant que nous avons toutes les données à disposition, nous allons les charger dans un modèle Power Pivot :

POWERQUERY_QUERY_SETTINGS

Ajout de colonnes calculées, KPI, hierarchies, … :

POWERPIVOT_VOYAGEUR
POWERPIVOT_PONCTUALITE
POWERPIVOT_DIAGRAM

 

Power View

Proportion de trains en retard par régions et par gares par années :

POWERVIEW_1

Répartition du nombre de voyageurs montant en gare chaque jour :

POWERVIEW_2

 

Power Map

POWER_MAP

 

Conclustion

Ces derniers outils accessibles depuis Excel répondent aux besoins de la Self Service BI. Leurs prises en main sont assez rapides et permettent de répondre à un grand nombre de problématiques des utilisateurs finaux. De plus elle peut s’inscrire dans un cycle de développement plus court -> Agilité !

 

7 Comments

  • Hervé Says

    Hello Romain, merci pour cet article intéressant,

    Pouvez-vous svp me dire quelle formule dax avez-vous utilisé pour calculer le Min, Max, moy de la colonne « Nombre de voyageurs montant » (« entre…et… », ou « plus de … ») ?

    Je pense qu’il faut isoler les nombres mais il y a peut-être plus simple…

    Merci d’avance.

  • Re-Bonjour Romain,

    Pour mon commentaire précédent, je suis passé par une mini table de correspondante en amont dans la source pour ramener les Min/Max du nombre de voyageurs montants. Je suis tjrs preneur pour savoir quelle solution avez opté de votre côté.

  • Re-bonjour Romain,

    Je suis finalement passé par une mini-table de correspondance pour ramener les Min/Max des voyageurs montant en gare…Je suis toujours preneur de votre solution pour laquelle vous avez opté.

  • Bonjour Hervé, désolé pour l’attente je rentre de vacance. Pour faire au plus rapide, j’ai écrit le code DAX suivant :
    Min=IF([Nombre de voyageurs montant]= »entre 1000 et 5000″;1000;
    IF([Nombre de voyageurs montant]= »entre 5000 et 15000″;5000;
    If([Nombre de voyageurs montant]= »entre 300 et 1000″;300;
    If([Nombre de voyageurs montant]= »moins de 300″;100;
    If([Nombre de voyageurs montant]= »plus de 15000″;15000;0
    )))))
    De meme pour le Max 😉

  • Bonjour Romain, merci de ta réponse, même tardive…Du coup, ça fait 2 solutions pour travailler les données des voyageurs 🙂

    Pour que cela fonctionne bien, il faut retravailler car nos amis sncf (en plus le transport est très galère cette semaine en RP) ont laissé un caractère en plus dans les textes « entre .. et .. »

    Ce qui fait :

    Min=IF([Nombre de voyageurs montant]= »entre 5000 et 15000″;5000;
    IF([Nombre de voyageurs montant]= »entre 1000 et 5000″;1000;
    If([Nombre de voyageurs montant]= »entre 300 et 1000″;300;
    If([Nombre de voyageurs montant]= »moins de 300″;300;
    If([Nombre de voyageurs montant]= »plus de 15000″;15000;0
    )))))

    Max=IF([Nombre de voyageurs montant]= »entre 1000 et 5000″;5000;
    IF([Nombre de voyageurs montant]= »entre 5000 et 15000″;15000;
    If([Nombre de voyageurs montant]= »entre 300 et 1000″;1000;
    If([Nombre de voyageurs montant]= »moins de 300″;300;
    If([Nombre de voyageurs montant]= »plus de 15000″;15000;0
    )))))

    A l’oeil nu…On ne le voit presque pas 🙂

  • charles kieny Says

    ce serait bien si ça marchait avec des fichiers excel, mais « = Excel.Workbook(CombinedBinaries) » ne retourne que le premier fichier …

  • En effet, il faut passer par une petite fonction pour merger des fichiers Excel. J’ai fait l’exercice sur le fichier suivant : http://1drv.ms/1kPwx5D

Comments are closed.