Nous avions vus dans un article précédent comment le module Power Query, commun aux logiciels Excel et Power Bi, permettait de réaliser des appels API de la solution Piano Analytics et ainsi récupérer ses données analytics dans ses rapports/dashboards. 

Je vous conseille de faire un tour sur cet article si vous n’êtes pas à l’aise avec Power Query  ou l’API Piano avant de continuer cette lecture.

La limite des 10 000 lignes

Nous savons comment récupérer les données via l’API, mais nous pouvons être confrontés à un problème lorsque nos appels sont détaillés : atteindre la limite des 10 000 lignes récupérables dans un appel.

En effet l’API Piano analytics fonctionne avec un système de pagination, où vous définissez le nombre de résultats souhaités dans l’appel (10 000 maximum), et quelle page de résultats vous souhaitez récupérer.

Par exemple, si j’indique un nombre de résultats à 50 et la page de résultat à 2 (« max-results »:50, »page-num »:2), j’obtiendrais les lignes de résultats 51 à 100.

Comme la limite est de 10 000 lignes, si une requête me retourne 30 000 résultats, je devrais alors réaliser 3 appels API sur les pages 1,2 et 3 pour récupérer l’ensemble de mes données. Or, dans une source de données Power Query classique, je ne peux effectuer qu’un seul appel à la fois. 

Nous pourrions alors tout à fait imaginer créer 3 sources de données et simplement faire varier le numéro de page pour avoir l’ensemble des données : 

• « max-results »:10000, »page-num »:1
• « max-results »:10000, »page-num »:2
• « max-results »:10000, »page-num »:3

Il suffirait ensuite d’utiliser la fonction « ajouter des requêtes » de Power Query pour ne créer qu’un seul tableau regroupant ces 3 requêtes.

Cette possibilité n’est cependant pas optimale car elle va générer quelques désagréments : 

• Cela n’est pas extensible à l’infinie, si vous devez par exemple récupérer 200 000 lignes (maximum autorisé) vous devrez faire 20 appels différents…
• Cela va « polluer » votre vue dans power Query, puisque vous aurez potentiellement des dizaines d’appels
• Cela n’est pas dynamique. Si sur avril la requête vous retournait 30 000 résultats, vous n’êtes pas à l’abris qu’elle passe à 50 000 sur le mois de mai

Pour ces raisons, nous allons essayer de développer une technique permettant de requêter dynamiquement autant de pages de résultats nécessaires en fonction du volume de données à récupérer sur la période. Et quand je parle de « technique » je parle en réalité d’une fonction personnalisée Power Query que nous allons écrire ensemble.

Pour ceux souhaitant simplement récupérer le code de la fonction mais sans en comprendre le fonctionnement, vous pouvez vous rendre directement à la fin de l’article.

Objectifs de la fonction personnalisée

Avant de nous lancer dans l’écriture de la fonction, nous devons définir ce qu’elle doit faire. Voici une liste des étapes à réaliser : 

• La fonction va devoir en premier lieu déterminer le nombre de lignes de résultats présentes dans notre requête API (l’API Piano Analytics dispose de fonctionnalités pour récupérer cette information, nous verrons cela par la suite)
• A partir de ce nombre de lignes, en déduire ne nombre d’appels qu’il faudra réaliser, sachant que le nombre maximum de lignes récupérables est de 10 000
• Générer dynamiquement autant d’appels API qu’il y a de pages de résultats et en récupérer les données
• Fusionner les résultats de l’ensemble des requêtes dans un tableau

Ecriture de la fonction

Comprendre le fonctionnement de l’API « getRowCount »

Quand on se trouve dans Data Query, l’interface nous indique bien le nombre de résultats d’une requête (dans mon cas 62 452) : 

Vous ne trouverez en revanche pas cette information lors d’un appel API classique (Bouton « partage » > «API GET ») :

https://api.atinternet.io/v3/data/getData?param={"columns":["date","src","page","m_visits"],"sort":["-m_visits"],"space":{"s":[592201]},"period":{"p1":[{"type":"D","start":"2022-01-01","end":"2022-03-31"}]},"max-results":50,"page-num":1,"options":{}}

Fort heureusement, la documentation API nous propose une méthode pour obtenir cette information : le getRowCount

Il suffit alors de modifier le terme getData par getRowCount dans l’url de l’API : 

https://api.atinternet.io/v3/data/getRowCount?param={"columns":["date","src","page","m_visits"],"sort":["-m_visits"],"space":{"s":[592201]},"period":{"p1":[{"type":"D","start":"2022-01-01","end":"2022-03-31"}]},"max-results":50,"page-num":1,"options":{}}

On va cependant obtenir ce message d’erreur si nous n’effectuons que cette modification :

En effet le getRowcount va refuser une partie des informations contenues dans notre appel de base, car ne faisant pas sens dans ce contexte : 

• Sort
• Page-num
• Max-results

Nous le supprimons donc de notre appel pour ne conserver que les informations liées aux données (columns, space, période, segment, filter et options) :

https://api.atinternet.io/v3/data/getRowCount?param={"columns":["date","src","page","m_visits"],"space":{"s":[592201]},"period":{"p1":[{"type":"D","start":"2022-01-01","end":"2022-03-31"}]} ,"options":{}}

Modification d’un appel classique en appel rowcount dans Power Query

Nous savons maintenant comment récupérer notre rowCount ! Notons nous maintenant ce que Power Query devra faire à partir de l’url API donnée par Data Query : 

• Changer le terme getData par getRowCount dans l’url de l’API 
• Ne conserver que les propriétés columns, space, période, segment, filter et options 
• Lancer l’appel Api et récupérer la valeur présente dans le json.

Isoler la partie paramètres d’un appel

Avant de créer directement une fonction personnalisée, nous allons nous entrainer en ne traitant qu’une seule requête fixe. Je vous invite donc à générer également de votre côté un appel API retournant plus de 10 000 lignes pour suivre les instructions. 

Dans Power Query, créez une nouvelle source vide (accueil > nouvelles source > requête vide). Collez maintenant votre URL API dans le champ d’édition de l’étape par défaut « source » :

Nous allons maintenant essentiellement travailler dans l’éditeur avancé afin de construire les retraitements souhaités. On va créer 2 variables via la méthode « split() » : une contenant la racine de l’url API, l’autre contenant le json des paramètres de requête, cela nous permettra de retravailler les informations séparément. On va retourner dans une liste pour vérifier le résultat :

Récupérer les paramètres nécessaires au getRowCount

Notre objectif va être maintenant de récupérer les propriétés dont nous avons besoin pour l’appel rowCount :

• columns
• space
• period
• filter
• options

Comme cette partie de l’url est du JSON, nous allons pouvoir utiliser la méthode Json.document pour mieux manipuler et isoler les informations : 

Il est maintenant très facile de récupérer une propriété de cet objet, en ajoutant son nom à la fin de l’expression (ici « columns » ):

Power Query a par contre transformer notre json en un format non exploitable (value), nous allons donc devoir reconvertir la valeur en JSON (via json.fromValue), puis le JSON en string (text.FromBinary) :

Nous avons presque nous ce qu’il nous faut ! il suffit maintenant d’ajouter le nom initiale de la propriété afin que le format soit prêt à être réinjecter dans un appels API :

Nous savons maintenant comment récupérer la propriété de notre choix depuis un appel API ! Nous allons donc appliquer cette logique sur l’ensemble des paramètres.

Nous allons donc créer des variables contenant chacune des propriétés dont nous avons besoin, afin de les reconcaténer ensuite dans notre appel rowcount :

Gérer les paramètres optionnels

Dans notre appel d’exemple, nous n’avions pas de filtre et de segment car ce sont des propriétés optionnelles. Il faut cependant quand même gérer le cas ou elles seraient présentes dans un de nos appels. Si on tente de les récupérer avec la même méthode alors qu’elles ne sont pas présentes, la variable tombe en erreur :

Nous allons donc devoir vérifier leur présence en amont. Pour cela nous allons transformer à la volée notre liste de paramètres en tableau, puis d’utiliser la méthode List.Contains sur la valeur recherchée, qui nous retournera un booléen.

Exemple avec la propriété filter :

A partir de là, il suffit décrire une condition demandant à retourner une valeur null lorsque notre test retourne FALSE ou de nous retourner le filtre s’il est présent (true). On applique cette logique à nos paramètres optionnels. Au moment de créer la liste des propriétés dans la variable results, on applique finalement la méthode RemoveNulls, qui va supprimer les éléments vides.

Grace à la fonction text.combine, nous allons pouvoir concaténer toutes nos propriétés dans une string, en ajoutant « , » comme caractère séparateur. Nous n’avons plus qu’à réutiliser cette méthode pour construire notre appel rowCount :

Requêter l’API getRowcount et déterminer le nombre d’appels à réaliser

La prochaine étape va maintenant être de requêter cette API (web.Contents) et de parcourir le JSON (json.document) pour récupérer la valeur qui nous intéresse.

L’API nous retourne un objet « Rowcounts », contenant une liste à 1 valeur, contenant elle-même une propriété RowCount. Nous allons donc sélectionner cette propriété et récupérer la première valeur associée :

Dans mon cas, j’ai donc plus de 60 000 résultats. Si je veux déterminer automatiquement le nombre de pages de 10 000 de résultats que je devrais récupérer, il me suffit de diviser cette valeur par 10 00 et d’arrondir à l’entier supérieur :

 Voilà pour cette partie, qui a finalement été assez complexe ! 

La prochaine étape visera à lancer ces 7 appels API, pour chacune des pages de données à récupérer ! 

Mais avant cela, nous avons besoin de gérer le cas ou la requête ne retourne aucun résultat. En effet la suite de la procédure va partir du principe qu’il y a au moins une page de résultats à récupérer, il faut donc s’assurer que la page 1 soit à minima requêtée. Nous allons donc construire une condition très simple pour cela :

Récupération des pages de résultats

Construction des appels API à réaliser

La première étape va être de créer une liste de 1 à 7 (dans mon cas), pour reconstruire un appel api complet pour chaque page de résultats. Pour cela nous allons pouvoir utiliser la méthode list.Generate, qui va prendre 3 paramètres : 

• La valeur de départ de la liste (ici 1)
• La valeur maximale (notre rowCountAdjusted)
• L’incrément qu’on souhaite faire à chaque nouvelle valeur de la liste (+1)

Nous allons ensuite placer notre numéro dans l’expression complète de l’objet page-num de l’API. La méthode list.transform permet d’appliquer des retraitements sur chaque valeur de la liste et la méthode text.combine permet de faire des concaténations :

Toujours à l’aide de list.transform, on remplace le page-num de l’appel d’origine (stocké dans source) par notre liste.

Nos URLs sont finalement prêtes à être appelées ! 

Déterminer les colonnes qui seront présentes dans le tableau final

Mais (et oui, encore un mais), Pour préparer au mieux la future création du tableau de données, nous allons avoir besoin de savoir dès maintenant les colonnes qui y sont présentes. Fort heureusement, l’API retourne un objet « columns » nous donnant une multitude d’informations, dont 2 qui nous intéressent  : 

• Name, qui indique la property key de la colonne dans l’appel API
• Label, qui indique le nom de la colonne (celui présenté dans l’interface)

On requête donc l’API d’origine et on isole l’objet columns :

Chaque colonne est représentée par un objet « Record » qui contient les informations souhaitées :

On réutilise la méthode list.transform pour extraire Name et Label :

Réaliser l’appel de données

On peut enfin appeler nos données, en repartant de notre liste d’URL API et en y appliquant la méthode web.contents. l’objet de l’API contenant les lignes de données est l’objet « Rows » :

Le format « list » n’est plus adaptée à partir de cette étape, nous allons donc la convertir en tableau de données grâce à la méthode table.FromList :

Power Query a automatiquement créé un tableau à une colonne « column1 ». Si vous rentrez dans une des listes, vous constaterez qu’elle contient une multitude d’objets « Record », symbolisant chaque ligne de notre futur tableau. Nous allons donc simplifier tout cela en supprimant un niveau d’imbrication. La méthode exandListColumn va permettre de faire remonter les objets « Record » directement dans la colonne « Column1 » :

Il ne nous reste plus qu’à déployer nos objets records en tant que colonnes, via la méthode expandRecordColumn. Cette méthode prend 4 arguments : 

• La table à modifier (expandTable)
• La colonne contenant les records (column1)
• Les noms des colonnes à extraire (nous les avons déjà récupérés tout à l’heure dans la variable columnsName
• Le nom qui sera donné à nos colonnes (nous allons reprendre ceux affichés dans l’interface, que nous avons dans la variable columnsLabel)

Nous venons donc récupérer plus de 60 000 lignes de données en une seule requête Power Query ! 

Construction de la fonction finale

La dernière étape consiste à transformer l’ensemble de notre côté en une fonction, que nous pourrons réutiliser pour autant d’appels que nous souhaitons. Plusieurs choses à faire pour cela : 

• Mettre au format « fonction fléchée » notre expression
• Ajouter un paramètre « requestedAPI » qui symbolisera l’API indiqué par l’utilisateur

Vous devirez obtenir quelques choses de ce type :

Placez maintenant l’appel API de votre choix dans le champ « requestedAPI » et cliquez sur appeler. Votre tableau de données va se créer automatiquement dans une nouvelle requête :

Conclusion

Une fois la fonction créée, elle peut être utilisée à l’infini et partagée à d’autres utilisateurs sans avoir besoin de connaissance technique particulière. Si vous avez suivi l’ensemble de cet démonstration vous avez maintenant une très bonne connaissance de l’API Piano Analytics ainsi qu’une bonne compréhension des possibilités offertes par le code « m » de power Query

Le fichier d’exemple contant la fonction finalisée :