Tutorial Power Query

Tutorial Power Query

Image

Contexte

L’objectif de ce tutorial est de pouvoir analyser et croiser toutes les informations concernant:

  • La ponctualité des TGV
  • L’information Transilien (les équipements, les services qui sont proposés dans les gares desservies par le Transilien)
  • Le nombre de voyageurs moyen par gare

Maquette

A l’issu de cet atelier nous obtiendrons une maquette semblable à celle ci-dessous :

Image

Dans ce scenario, nous utiliserons plusieurs requêtes(QUERY) afin de charger des données issues de différentes sources (csv, dossier, web) et réaliserons quelques transformations.

NB : toutes les formules utilisées dans ce tutorial sont codées en langage M, spécifique à Power Query.

QUERY 1 (Gare) : Chargement des données à partir d’un fichier csv

Dans cette requête nous utiliserons les transformations suivantes : « Source csv », « Use First Row As Headers », « Custom Colunm », « Replace Values », « data type ».

Pour charger les données à partir d’un fichier csv, je vous invite à suivre les étapes suivantes :

Dans Power Query :

  • Sélectionnez « From File »  ->  « From CSV » dans « Get External Data »
  • Sélectionnez ensuite le fichier  « Liste des gares de voyageurs du RFN avec coordonnées et adresses postales.csv »

Image

  • Activez « Formula Bar »

Image

  • Modifiez la source afin d’avoir un bon format de données en 1252 et un split par « ; » de type = Csv.Document(File.Contents(« C:\Users\Administrator\Documents\Source SNCF\Liste des gares de voyageurs du RFN avec coordonnées et adresses postales.csv »), null, « ; » , null, 1252)

 

cliquez sur « Use First Row As Headers » afin d’utiliser la première ligne comme en-tête.

Image

Pour créer une colonne calculée réalisant une concaténation :

  • Faites clics  droits -> Insert Custom Colunm

Image

  • Saisissez la formule suivante : Text.Combine({« 87 »,[UIC]})  afin de faire une concaténation du « 87 » et le code UIC pour faciliter le merge avec d’autre table

Image

Pour modifier les types de données :

  • Sélectionnez la colonne « Code UIC »  -> « Data Type » et choisissez le type Number

Image

Sur la colonne « Nom de la gare », remplacez le «-» par un espace afin d’améliorer le mapping avec les fichiers de ponctualité. Pour cela :

  • Utilisez « Replace Values » sur la colonne Région, remplacez la valeur « -CA » par «  Cote d’Azur »

Image

  • Validez les transformations et renommez « Query » par « Gare ».

Résultat attendu :

Image

QUERY 2 (Ponctualité) : Chargement des données à partir d’un dossier

Dans cette requête nous utiliserons : « Source dossier », « Split Column », « Use First Row As Headers », « Custom Colunm », « Transform », « data type », « Calcul de Ratio ».

Parmi les avantages de Power Query, il y a la possibilité de charger un dossier sans avoir besoin d’utiliser une boucle :

Dans Power Query :

  • Sélectionnez « From File » -> « From Folder » dans « Get External Data »
  • Sélectionnez ensuite le Dossier  « Source Ponctualite» (A voir avec le formateur pour le chemin).

Image

  • Parcourez le dossier « Source Ponctualite».

Image

  • Cliquez sur les doubles flèches dans la case « Content » afin de regrouper toutes les données des différents fichiers.

Image

Image

Afin de Splitter les différentes colonnes :

  • Cliquez sur « Split Column » -> « By Delimiter »

Image

  • Sélectionnez le « ; » comme  séparateur de colonne

Image

Image

Pour utiliser la première ligne comme en-tête :

  • Cliquez sur « Use First Row As Headers »

Image

  • Faites un filtre sur la colonne « Départ » afin de retirer la valeur « Départ » qui représentait la ligne d’en-tête de chaque fichier.
  • Pour les colonnes « Nb de circulations » et « Nb trains en retard à l’arrivée », remplacez les blancs par 0 (comme dans la QUERY 1) puis changez le format en « Number »
  • Afin de matcher avec la table « Gare », utilisez la fonction « Transform » puis « Capitalize Each Word » sur les colonnes « Départ » et « Arrivée ».

Image

  • Remplacez les « – » par des espaces sur ces deux colonnes.

Pour calculer le ratio du nombre de retards par rapport au nombre de circulations :

  • Insérez une nouvelle colonne
  • Saisissez la formule suivante :

if [Nb de circulations] = 0 then 0 else [# »Nb trains en retard à l’arrivée »]/[Nb de circulations]

Image

  • Renommez cette colonne « Ratio retard » et changez son format en « Number »
  • Validez et renommez la Query « Ponctualité TGV ».

Résultat Attendu :

Image

QUERY 3 (Equipement des gares) : Chargement des données à partir d’une page web

Dans cette requête, nous allons mettre en pratique les composants suivants : « Source web », « Split Column », « Use First Row As Headers », « data type ».

Dans Power Query :

  • Sélectionnez « From Web»

Image

ImageImage

Afin de Splitter les différentes colonnes :

  • Cliquez sur « Split Column » -> « By Delimiter »

Pour utiliser la première ligne comme en-tête :

  • Cliquez sur « Use First Row As Headers »

Faites les transformations qui suivent afin d’améliorer la correspondance avec les autres fichiers

  • Filtrez sur la colonne « Nombre de bornes d’appel » en retirant la valeur « Nombre de bornes d’Appel ».
  • Changez le format de la colonne « Code UIC » et « Nombre de bornes d’appel » en « Number ».
  • Supprimez la colonne « Gare ».
  • Validez et renommez la query « borne ».

Résultat attendu :

Image

QUERY 4 : Chargement des données à partir d’une page web et calcul des ratios

Dans cette requête, nous utiliserons les mêmes composants que dans la précédente mais avec le « Calcul des Ratios » en plus.

Dans Power Query :

  • Sélectionnez « From Web»
  • Rentrez l’adresse suivante :

http://files.transilien.com/hackdays/voyageurs_montant_en_gare.csv

Afin de Splitter les différentes colonnes

  • Cliquez sur « Split Column » -> « By Delimiter »

Pour utiliser la première ligne comme en-tête :

  • Cliquez sur « Use First Row As Headers »
  • Supprimez la colonne « Gare ».

Calcul des ratios :

Pour calculer le ratio permettant d’avoir le nombre de voyageurs minimum avec le code :

  • Insérez une nouvelle colonne
  • Saisissez la formule suivante :

 if [Nombre de voyageurs montant] = « entre  5000 et 15000 » then 5000 else if [Nombre de voyageurs montant] = « entre  1000 et 5000 » then 1000 else  if [Nombre de voyageurs montant] = « entre  300 et 1000 » then 300 else  if [Nombre de voyageurs montant] = « moins de 300 » then 0 else  if [Nombre de voyageurs montant] = « plus de 15000 » then 15000 else  0

  • Faites la même chose  pour la colonne « Nb Voyageurs Max » :

if [Nombre de voyageurs montant] = « entre  5000 et 15000 » then 15000 else if [Nombre de voyageurs montant] = « entre  1000 et 5000 » then 5000 else  if [Nombre de voyageurs montant] = « entre  300 et 1000 » then 1000 else  if [Nombre de voyageurs montant] = « moins de 300 » then 300 else  if [Nombre de voyageurs montant] = « plus de 15000 » then 15000 else  0

  • Idem pour la colonne « Nb Voyageurs Moyen » :

([Nb Voyageurs Min]+[Nb Voyageurs Max])/2

  • Supprimez la colonne « Nombre de voyageurs montant », modifiez le format des colonnes restantes en « Number ».
  • Validez et renommez la Query « Voyageurs ».

Résultat attendu :

Image

QUERY 5 : Chargement des données à partir d’une page web

Dans cette requête nous utiliserons : « Source web », « Use First Row As Headers », « un split par « ; »  », « Filtrer », « Replace Values ».

Dans Power Query :

Modifiez la source afin d’avoir un bon format de données en 1252 et un split par « ; » de type

  • Modifiez le format de la source

=Csv.Document(Web.Contents(« http://files.transilien.com/gare/gare_20120319.csv »),null, »; »,null,1252)

  • Mettez la première ligne en en-tête avec « Use First Row As Headers ».
  • Pour chaque colonne correspondant à des mesures (exemple : nb d’abris à vélo) ainsi que le code UIC, remplacez les blancs par 0 puis modifiez le format en « Number ». Pour le « CAB Passage Elargi contrôlé », remplacez « Pas de CAB » par 0.
  • Il est possible de filtrer sur la colonne « gare non SNCF » et de ne garder que les 0, c’est-à-dire seulement les gares SNCF.
  • Supprimez alors toutes les colonnes mises à part le code UIC et les mesures.
  •  Validez et renommez la Query « Equipement ».

Résultat attendu :

Image

QUERY 6 : Merge de plusieurs sources

Dans cette requête nous allons faire une jointure entre deux fichiers différents pour cela nous allons utiliser le Merge qui est l’équivalent d’INNER JOIN.

Dans Power Query :

  • Appuyez sur « Merge »  

Image

  • Sélectionnez  la table « Equipement » et le champ « Code UIC complet ».
  • Faites la jointure avec la table « Borne » sur le code UIC en cochant la case « Only include matching rows ».

Une nouvelle Query est créée avec la table « Equipement » contenant une nouvelle colonne « NewColumn ».

  • Appuyez sur Expand
  • Cochez le nombre de bornes d’appel.

Image

  • Une fois la colonne ajoutée, validez.

Résultat attendu :

Image

QUERY 7 : Merge de Query voyageurs avec la Query

Dans cette requête, nous allons faire une jointure entre deux fichiers différents. Pour cela, nous allons utiliser le Merge qui est l’équivalent d’INNER JOIN.

Dans Power Query :

  • Faites une nouvelle jointure « Merge » en sélectionnant la table précédemment créée ainsi que la table « Voyageurs » (toujours sur le code UIC).
  •  Cochez de nouveau la case « Only include matching rows » avant validation.
  • Faites un Expand et cochez le « Nb Voyageurs Min », « Nb Voyageurs Max » et « Nb Voyageurs Moyen ».
  • Validez et renommez la query « Transilien ».

Résultat attendu :

Image

Les « QUERY » sont maintenant créées et peuvent être intégrées au Data Model.

  • Cliquez  sur « Load to Data Model » pour les intégrer  et être utilisées afin de créer des rapports sous PowerView.

Laila TABII

3 réflexions sur “Tutorial Power Query

  1. Eric CHEVRON dit :

    Bonjour, et merci pour cette publication.

    J’aurais bien aimé suivre votre tuto, mais je ne trouve aucun lien pour télécharger les fichiers source…

    Bien cordialement,
    Eric

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s