Aspirer des données depuis un site web avec Excel, Power Query et Kimono

Mise  à jour 02/05/2014 : cet article existe aussi maintenant en webcast! Merci le GUSS 😉

Vous le savez surement, Power Query est l’add-in Excel publié par Microsoft via son offre Power BI, dédié à l’import de données de sources multiples. On en avait déjà parlé pour importer des fichiers identiques dans un même XLSX, une grosse galère sous Excel nature, une balade avec Power Query.

Logo Power BI

Si nous sommes nombreux à voir en Power Query un outil avec un potentiel exceptionnel, il y a un domaine où il est encore assez faible : l’import de données affichées sur une page web. Enfin ce n’est pas vraiment Power Query qui est faible, c’est plutôt qu’après 2500 lignes de JavaScript, le HTML final des sites est souvent complétement inexploitable…

Logo Kimono Labs

Et un outil fantastique pour contourner le problème c’est kimono. Avec kimono, vous allez générer de manière complétement graphique une API à partir d’un site web, directement depuis de votre navigateur.

On fait juste une pause pour bien laisser ça descendre…

Générer une API, de manière graphique, directement dans le navigateur.

On ne vit pas dans le futur avec ça franchement ? Pour moi c’est le scénario d’usage final. Et pour revenir au sujet du jour, ça les API web, Power Query il maîtrise.

Le mieux pour comprendre tout ça étant certainement de prendre un exemple, on va pratiquer en rapatriant des données depuis MetaCritic, le site web qui agrège les notes données dans la presse (papier ou web) à entre-autres les jeux vidéo.

La source : les meilleurs jeux PC de 2014

Capture d'écran de MetaCritic

On va commencer en essayant de se connecter directement à MetaCritic via Power Query, pour constater l’inutilisabilité de la chose :

1 – Je lance Excel, direction l’onglet Power Query, option importer depuis un site web, puis je renseigne l’adresse du site source :

Power Query - Connexion à un site web

2 – Hum, le navigateur de Power Query ne s’en sort pas tout seul, on va creuser (Edit) :

Power Query - Connexion à un site web

3 – Et boum, bon courage ! Si quelqu’un a trouvé une méthode pour s’y retrouver, je suis preneur, moi j’abandonne là en général

Power Query - Connexion à un site web

Alors à la place on va se connecter à kimono (on s’enregistre, on ajoute le bookmarklet à sa barre de raccourci) et créer l’API de manière graphique, c’est parti!

1 – J’ouvre mon navigateur, je vais sur le site source et j’utilise kimonify (le bookmarklet). La barre d’outils Kimono apparaît en haut de la page, et je commence par importer les noms des jeux juste en cliquant sur eux dans la page de MetaCritic. Le moteur de Kimono reconnait alors l’attribut HTML et identifie les 88 noms suivant, je renomme le champ « Nom du jeu » :

Kimono Labs : Premier paramètre
2 – J’appuie sur + dans la barre Kimono, je sélectionne la première note (92 pour DS2), la deuxième (88 pour Hearthstone), et à nouveau Kimono identifie les 88 valeurs suivantes. Je renomme le champs « Note »:

Kimono Labs : Deuxième paramètre
3 – Je répète la manip pour le Score Utilisateur, cette fois-ci le moteur de Kimono hésite un peu, il me propose plusieurs séries et à force de sélectionner d’autres scores et refuser d’autres attributs (ainsi que le label « User Score »), il m’en trouve bien 85 (en effet 3 jeux n’ont pas ce score, mais ça ne casse pas la reconnaissance):

Kimono Labs : Troisième paramètre
4 – J’ai assez d’info pour le moment, je valide (Done dans la barre Kimono), je donne un nom à mon API, et une période de rafraichissement (temps réel pour la démo, mais on peut alléger la charge sur la source en ne rafraichissant le dataset que périodiquement):

Kimono Labs : Validation de l'API
5 – Et Kimono me renvoie vers le tableau de bord de mon API:

Kimono Labs : Interface de gestion de l'API
6 – Via l’onglet « How To Use » je retrouve les éléments nécessaires pour accéder à l’API, y compris les URL (endpoints) que je vais pouvoir transmettre à Power Query en JSON, CSS ou RSS :

Kimono Labs : Interface d'appel de l'API
7 – On peut d’ailleurs tester le EndPoint CSV (ouais je suis oldschool) tout de suite :

Kimono Labs : test du endpoint CSV
8 – Mais le mieux c’est de l’appeler directement depuis Power Query (From Web toujours) :

Power Query : Appel de l'API Kimono
9 – Et après quelques petites manipulations (on retire la première ligne, on sépare les colonnes par délimiteur, on utiliser la première ligne comme nom de colonne, on nettoie le User Score, on filtre les lignes de déchet), on obtient le bon dataset. Note : si vous optez pour le JSON et que vous galérez, regardez cette vidéo, si ça ne marche pas comme ça, c’est que l’API est mal formée, le mieux est de la casser et recommencer (Kimono est encore en beta hein… Celle-là fonctionne et elle est publique).

Power Query : Résultat de l'import
10 – Pour obtenir les données attendues dans Excel :

Power Query et Kimono : résultat de l'import dans Excel

Personnellement je trouve ça juste énorme ! Et en plus tout est en live, la Query dans Excel et l’API côté Kimono, donc il suffit de rafraîchir pour que les données soient mises à jour depuis la source.

Si on rajoute la gestion des paramètres pour l’année (2014, 2013…) côté Power Query, la gestion de la pagination côté Kimono, on a les briques de base pour extraire toute la donnée dont on peut avoir besoin !

Joli non? Et sinon oui, c’est le bon moment pour prendre des actions dans Kimono Labs 😉

Mettre bout à bout (enchainer) des sources de données dans Excel avec Power Query (Data Explorer)

Un cas d’usage qui pouvait être reproché à Excel, et à Power Pivot également, c’était l’impossibilité de prendre 2 ou plus sources de données identiques et de les mettre bout à bout (en mode UNION pour les SQLeux). Typiquement : j’ai un fichier CSV par mois dans un répertoire, et j’aimerais tous les mettre dans le même tableau Excel pour travailler dessus. Jusqu’alors, la seule solution c’était les copiés/collés successifs à la main, avec tous les risques que ça comporte.

Heureusement Power Query (anciennement Data Explorer) vient à la rescousse ! C’est quoi ? C’est un add-in Excel gratuit, édité par Microsoft, qui sert de mini-ETL à Excel. Sincèrement, c’est peut-être l’outil de BI Self-Service qui enflamme le plus les consultants décisionnel Microsoft en ce moment, alors allez l’installer!

Le mieux pour vous convaincre étant sans doute de passer à la pratique, voici comment employer la chose :

Scénario 1 : 3 tableaux dans le même fichier Excel

  • Pour l’exemple j’ai créé 3 tableaux dans Excel 2013, dans la même feuille ou pas, ça ne change rien, qui contiennent des données à la structure identique:

Append_1

  • Le but : créer une « Query », une requête dans Power Query, sur chacune des tables, pour ensuite les associer en un seul résultat
  • Etape 1 (à répéter 3 fois, une fois par tableau) : a. Je clique dans un tableau (si ce n’est pas fait, n’oubliez pas de « Mettre sous Forme de Tableau » vos données), b. Je vais dans l’onglet Power Query, c. Je créé une Query depuis un Tableau (From Table). Notez que si l’onglet Power Query n’apparait pas, le mieux est de valider qu’il est bien activé (Fichier > Options > Compléments > Gérer : Compléments COM : « Microsoft Power Query for Excel » doit être coché)

Append_2

  • En bleu : l’interface de transformation de données de Power Query, ici on a besoin de rien, donc on valide (Done):

Append_3

  • La Query est créée, elle correspond à un tableau dans un nouvel onglet Excel:

Append_4

  • On répète l’opération 3 fois, une fois par tableau pour obtenir les onglets suivants (oui mon Excel est en anglais, Sheet ou Feuille c’est pareil ;)) :

Append_5

  • Etape 2 : a. On va dans l’onglet Power Query, et b. on choisit « Append »

Append_6

  • Dans l’interface, je choisis 2 Queries, l’ordre n’importe pas ici. Vous noterez que les noms affichés sont les noms des Queries, qui par hasard correspondent ici aux noms des Tableaux. Si vous avez renommé vos Queries, vous retrouverez les nouveaux noms ici:

Append_7

  • Encore une fois, on passe par l’interface de transformation des données de Power Query, encore une fois ici on ne change rien, on valide (Done) :

Append_8

  • Et hop, je me retrouve avec une nouvelle Query qui est la mise bout à bout des 2 premières tables :

Append_9

  • Je répète l’opération, cette fois on prenant dans l’Append le résultat de mon premier Append (Append 1) et le tableau que je n’avais pas pris la première fois :

Append_10

  • Je valide dans l’éditeur et hop, me voilà avec la table complète !

Append_11

  • Chouette non? Alors on peut se dire que c’était beaucoup d’étapes, alors que 3 copiés/collers suffisaient à faire la même chose. Certes. Mais l’avantage énorme des Queries, c’est qu’elles se mettent à jour (cliquer pour zoomer) :

Append_12

  • C’est quand même bien fait non ? 🙂

Scénario 1 – Avancé : Optimisation sur 3 tableaux dans le même fichier Excel

Alors ok, c’est mignon, mais avec la manipulation précédente on se retrouve quand même avec 5 feuilles, et si on ajoute d’autres sources ça va rapidement faire beaucoup trop (2n-1 feuilles avec n sources). Évidemment on peut faire bien mieux, mais il va falloir remonter les manches 😉

  • D’abord il va falloir activer le mode avancé

Append_13

  • Et tant qu’on y est, activez le Fast Combine, ça vous évitera des soucis plus tard :

Append_14

  • Désormais, vous allez pouvoir directement éditer les formules que Power Query utilise pour transformer les données. On retourne donc dans l’interface de transformation des données de Power Query en utilisant une de ces 2 méthodes (ab,ab’c’), ou en créant une nouvelle Query :

Append_15

  • Et dans cette interface, si vous êtes bien passé en mode avancé (voir plus haut), vous devez avoir un petit parchemin à cliquer :

Append_16

  • Qui amène à l’interface de saisie des formules :

Append_17

  • Je vous laisse avec le guide de référence pour voir tout ce qui est possible de faire, perso j’ai la même démarche que dans cet article, je fais toutes les étapes unitairement, et j’essaye d’optimiser ensuite à la main. Dans notre cas :

Append_18

  • En version copiable, à adapter en fonction des noms de vos tableaux (Table1/Table2/Table3) :
let
Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Source3 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
Source = Table.Combine({Source1,Source2,Source3})
in
Source

Avec cette formule on fait tout en une seule Query, donc un seul onglet, peu importe le nombre de source. Pas mal non ? 🙂

Scénario 2 : 3 fichiers Excel distincts, toujours avec le même format de données

  • Alors de la même manière, on peut faire les fichiers Excel. Je vous donne directement le script, à vous de jouer en manuel pour bien comprendre !

Append_19

let
Source2006 = Excel.Workbook(File.Contents("C:\Temp\2006.xlsx")),
Dataset2006 = Source2006{[Name="Sheet1"]}[Data],
FirstRowAsHeader2006 = Table.PromoteHeaders(Dataset2006),
Source2007 = Excel.Workbook(File.Contents("C:\Temp\2007.xlsx")),
Dataset2007 = Source2007{[Name="Sheet1"]}[Data],
FirstRowAsHeader2007 = Table.PromoteHeaders(Dataset2007),
Source2008 = Excel.Workbook(File.Contents("C:\Temp\2008.xlsx")),
Dataset2008 = Source2008{[Name="Sheet1"]}[Data],
FirstRowAsHeader2008 = Table.PromoteHeaders(Dataset2008),
SourceComplete = Table.Combine({FirstRowAsHeader2006 ,FirstRowAsHeader2007 ,FirstRowAsHeader2008 })
in
SourceComplete

Scénario 3 : 3 fichiers CSV, toujours avec le même format de données

  • Je vous donne directement le script :

Append_20

let
Source2006 = Csv.Document(File.Contents("C:\Temp\2006.csv")),
FirstRowAsHeader2006 = Table.PromoteHeaders(Source2006),
Source2007 = Csv.Document(File.Contents("C:\Temp\2007.csv")),
FirstRowAsHeader2007 = Table.PromoteHeaders(Source2007),
Source2008 = Csv.Document(File.Contents("C:\Temp\2008.csv")),
FirstRowAsHeader2008 = Table.PromoteHeaders(Source2008),
SourceComplete = Table.Combine({FirstRowAsHeader2006 ,FirstRowAsHeader2007 ,FirstRowAsHeader2008 }),
ChangedType = Table.TransformColumnTypes(SourceComplete ,{{"Region", type text}, {"Categorie", type text}, {"Annee", type number}, {"Valeur", type text}})
in
ChangedType
  • Une petite remarque en passant : attention avec les CSV, pour le moment l’interface de Power Query ne permet pas de gérer les séparateurs différents de la virgule, ni nos encodages européens facilement. Pour se faire, il faut modifier sa formule source comme suit:
Csv.Document(File.Contents("C:\Users\Florian\Desktop\2006.csv"), null, ";" , null, 1252)

Avec : votre séparateur, et votre encodage (par défaut 1252 sur Windows, voir le PDF de référence pour les autres – recherchez TextEncoding).

Et après ?

Soit on utilise directement le tableau résultat des Queries Append dans un tableau croisé dynamique, soit on l’ajoute au Data Model et c’est parti pour Power Pivot !

Amusez-vous bien, et n’hésitez pas à venir partager vos trouvailles 😉

Petites nouvelles en ce début d’été

En vrac :

  • Vous avez pu constater la mise à jour du design de la BI ça vous gagne (n’hésitez pas à venir voir un coup si vous suivez par flux RSS). Moi je le trouve plutôt joli, beaucoup plus lisible, et puis j’ai pu inclure un plugin Twitter dans la colonne de droite. Globalement c’est cool, non ?
  • En parlant de RSS, suite à la fermeture de Google Reader je suis passé sur feedbin.me. L’interface web est plutôt clean et il est compatible avec Reeder sur iPhone. Malheureusement dans la bataille j’ai perdu Reeder pour Mac, qu’aucun service ne couvre. Vous êtes passé sur quoi de votre côté ?
  • En parlant de Mac, je me tâte pour changer de MacBook Air à la rentrée, profiter des nouveaux SSD et de 8Go de RAM. Je sais que Charly va s’acheter une bête de course portable d’ici peu. Quand je vois ses stats j’hésite à repasser sur PC. Mais le poids, le bruit et la qualité des finitions du MBA me retiennent encore… Ça plus le fait d’avoir un OS par usage, Windows 8 pour le professionnel et OS X pour le personnel, ça m’arrange quand même bien. Je doute.
  • Cette fois sans transition, je vous encourage à vous inscrire au SQL Saturday 251, le samedi 14 septembre au campus EPITA à Paris, organisé par la fine équipe du GUSS (z’avez vu le nouveau site web ?). C’est un samedi donc pas d’excuses de planning 😉 Et d’ailleurs vous pouvez profiter du même lien pour soumettre vos sessions. C’est l’occasion de se faire connaître un peu, profitez-en ! Attention la deadline pour les speakers est bientôt là, et il me semble qu’on cherchait plutôt des sessions techniques niveau 300, si vous voulez maximiser vos chances.
  • Côté Microsoft, si vous avez raté l’annonce sachez que les produits Self-Service BI viennent d’être regroupés dans une offre marketing cohérente : Power BI. Cela inclut Power Pivot, Power View, Power Map (ex Geoflow) et Power Query (ex Data Explorer – le produit BI de l’année). Également dans le pack, la mise à disposition d’un mini portail BI pour partager ses fichiers et ses sources de connexions, directement sur Office 365, et d’autres goodies. Reste à connaître les tarifs et toutes les contraintes entre les systèmes. N’hésitez pas à lire l’avis de Chris Webb sur la question.

Et vous alors ? Ça va ? Quoi de neuf ? 🙂