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 :

L’API Piano Analytics permet très facilement d’extraire une importante quantité d’informations stockées dans la solution.

On peut vouloir importer les données Piano Analytics dans Excel pour réaliser une analyse ponctuelle, réaliser des tableaux de bord ou encore nourrir un reporting Power Point.

Dans un autre contexte, on peut également utiliser Power Bi pour créer des rapports décisionnels avec ses données analytics et toute autre source de données.

Nous allons voir à travers cet article que la solution est la même dans les 2 cas : Power Query.

En effet cet outil est disponible aussi bien dans Excel que dans Power Bi, et permet (entre autres) le requêtage de toute API externe pour importer les données et les remanipuler dans exploitation via un langage de programmation (le mashup).

Lancer Power Query dans Power BI ou dans Excel

Les présentations étant maintenant faites, rentrons dans le vif du sujet ! Pour suivre ce guide, vous pouvez utiliser de manière indifférente Power Bi ou Excel

Dans power Bi, Power Query est disponible dans l’onglet Accueil > Transformer les données :

Dans Excel, l’outil est disponible dans l’onglet Données > Obtenir des données > Lancer l’éditeur Power Query :

Création de la clé API

Pour requêter vos données Piano Analytics depuis l’extérieur de l’interface, vous allez devoir obtenir une clé API. Cette clé est liée à votre compte Piano Analytics, vous n’aurez donc pas la même que votre collègue. Vous aurez également accès au même périmètre que celui disponible dans votre interface (même liste de niveaux 1).

Une fois connecté à votre compte, allez dans votre profil puis dans l’onglet Api Keys.

 Générez une nouvelle clé et conservez bien l’Access key et surtout la secret key, qui ne vous sera plus donnée par la suite.

Récupération de l’appel API

A partir de la documentation API, il est tout à fait possible de créer votre appel de zéro. Il est cependant plus confortable de partir de l’outil Data Query, qui sera capable de vous restituer un appel API clé en main.

Rendez-vous donc dans l’interface Data Query, où je vous invite à créer l’appel de votre choix, peu importe la période (nous verrons cette question plus tard). Dans mon cas je vais simplement prendre le nombre de pages vues et de visites pour mes pages avec aux moins 10 pages vues :

Pour récupérer l’appel API correspondant, ouvrez l’onglet de partage et sélectionnez « Copier l’URL API (GET) » (nous reviendrons sur la notion d’API Post dans un autre article).

L’url est maintenant présente dans votre presse papier. Si vous la coller dans un bloc-notes, vous verrez que celle-ci est encodée. Pour y voir plus clair, je vous conseille de la désencoder via l’outil de votre choix. De mon côté je vais utiliser l’outil du site Meyerweb. On obtient donc ce résultat :

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

Cet article n’est pas là pour apprendre à décortiquer la structure de cet appel (pour rappel, la documentation est ici), retenez simplement que le paramètre « param » de l’url est au format Json et est composé de plusieurs propriétés (columns, sort, period, max-results…).

Test de l’API Piano Analytics

On peut maintenant tester si notre clé API fonctionne ! Pour cela collez l’url dans votre navigateur. Un popup va s’afficher et vous demander un nom d’utilisateur et un mot de passe. Mettez votre Access key en tant que Nom d’utilisateur et la secret Key en mot de passe :

Vous obtiendrez normalement un json, que vous pourrez rendre plus lisible avec une petite extension chrome du type Json Viewer. On retrouve bien dans la propriété « rows » nos pages avec leurs données :

Créer son appel Power Query

Ouvrez Power Query et créez une nouvelle source de données avec le type « web », qui correspond au requêtage des API et des pages web :

Placez votre requête API dans le champ et sélectionnez le mode avancé :

Nous allons maintenant gérer l’authentification de l’API, qui se fait via le header de la requête.

On ajoute pour cela un paramètre « x-api-key », avec pour valeur la concaténation de votre access key et de votre secret key séparées d’un « _ » : accessKey_secretKey

Power Query vous demandera alors le type de connexion que vous souhaitez mettre en place. Comme le système d’authentification est déjà présent dans l’appel en lui-même via x-api-key, on peut rester sur le mode anonyme :

Power Query vous affiche maintenant les données qu’il a récupérées, et nous allons tâcher de les déployer afin de pouvoir les utiliser. Procédez comme ceci :

• Cliquez sur la valeur « Record » à gauche de « DataFeed » (Non nécéssaire pour Power Bi)
• Cliquez sur la valeur « List » associée à « Rows »
• Dans l’onglet « Transformer », sélectionnez « convertir vers la table » puis « OK » dans la popin « vers la table » qui s’affiche
• A droite de l’entête de colonne « Column1 » cliquez sur le petit icône avec les 2 flèches qui s’opposent et sélectionnez les colonnes que vous souhaitez extraire.

Vous avez maintenant vos données qui s’affichent dans Power Query. Vous pouvez renommer les colonnes qui ont toutes le préfixe « columns1 », pour plus de clarté. Vous pouvez maintenant cliquer sur « Fermer et charger » (« fermer et appliquer » pour Power Bi) pour que les données soient chargées dans le document :

Libre à vous de créer maintenant autant d’appels que vous le souhaitez en suivant cette méthode !

Dynamiser les périodes d’appel de vos API

Vous l’aurez peut-être remarqué mais la période donnée par Data Query dans l’API est toujours une période dite « fixe », c’est-à-dire avec une date de début et une date de fin, et cela même si vous sélectionnez une période relative (hier, la semaine dernière, le mois en cours) dans l’interface :

period »:{« p1 »:[{« type »:"D","start":"2022-04-12","end":"2022-04-18"}]}

Pour changer la période d’appel, il faudrait donc théoriquement éditer notre requête dans Power Query et changer manuellement la date, ce qui n’est pas très pratique (notamment si vous avez des dizaines d’appels) :

Fort heureusement la documentation nous donne les outils pour récréer une période relative dans l’appel API.

Si on reprend notre json de base gérant la date « period »:{« p1 »:[{« type »: »D », »start »: »2022-04-12″, »end »: »2022-04-18″}]} » voici ce que cela deviendrait pour des périodes relatives :

Comme vous le voyez, le patern se répète et il n’y a en réalité que 3 éléments à changer :

• Granularity, qui indique la granularité de la période (Days, Week, Month, Quarter, Year)
• StartOffset, qui indique la période à laquelle il faut faire remonter l’appel. 0 correspond à la période en cours, -1 la période précédente, -2 la période encore précédente…
• EndOffset, qui indique la période à laquelle il faut arrêter l’appel. Le format est le même que pour StartOffset

Ainsi, « granularity »: « W », »startOffset »: -2, »endOffset »: -1 » veut dire « sélectionne moi les 2 dernières semaines par rapport à la date actuelle ». Ou encore « granularity »: « D », »startOffset »: -366, »endOffset »: -1 » veut dire « sélectionne moi les 365 derniers jours par rapport à la date actuelle ».

Vous pouvez ainsi imaginer une multitude de combinaisons pour vos appels en mixant ces 3 paramètres.

Si on souhaite maintenant modifier la période de départ de notre appel pour la rendre dynamique par rapport à la date actuelle, par exemple en sélectionnant la semaine précédente :

• Ouvrez Power Query et sélectionnez la requête à modifier
• Dans « étapes appliquées », double cliquez sur « Source » pour afficher à nouveau le champ contenant l’appel API
• Remplacez la propriété « period » avec la période relative.

Validez vos changements en cliquant sur « OK » :

Votre appel s’adaptera donc bien maintenant à la date à laquelle vous actualisez votre document !

Bonus 1 : modifiez la période de tous vos appels API en une seule fois

Comme vous l’aurez compris, pour modifier la période de vos appels API, il faudra alors le faire manuellement (pour passer de la période « semaine -1 » à « semaine -2 » par exemple). Si vous devez régulièrement changer les périodes d’appels, le travail peut vite devenir fastidieux.

Une solution pour contourner ce problème est de créer une valeur unique, qui sera automatiquement prise en compte comme période dans tous les appels.

Nous allons utiliser pour cela les paramètres power query :

• Allez dans accueil > Gérer les paramètres > Nouveau Paramètre
• Nommez-la avec le nom de votre choix (« periode_analyse » dans mon cas) 
• placez le json de la période souhaitée dans le champ « valeur actuelle ». dans mon cas je souhaite le mois précédent, donc {« p1 »:[{« type »: « R », »granularity »: « M », »startOffset »: –1, »endOffset »: -1}]}

Maintenant que notre paramètre existe, nous allons remplacer la période présente dans les appels par notre paramètre. Pour cela :

• Sélectionnez la requête à modifier et placez-vous sur l’étape « Source »
• Dépliez via la flèche le code m correspondant à cette étape et repérez la zone où est écrite la période
• Supprimez la période présente et concaténez le début de l’appel API avec notre paramètre période_analyse, puis concaténez à nouveau avec la fin de l’API via le format début_requete’&période_analyse&’fin_requete (cf. capture)

Effectuez cette opération sur l’ensemble des appels à dynamiser. Et voilà ! Vous pouvez maintenant changer à loisir votre période d’analyse, l’ensemble des appels seront mis à jour. A noter que vous pouvez effectuer exactement la même démarche pour une éventuelle période de comparaison.

Bonus 2 : Dynamiser la période à partir de dates fixes

Le format relatif des dates est très pratique, mais très spécifique à Piano Analytics. Si d’autres sources que vos données analytics sont appelées, vous aurez potentiellement besoin d’utiliser le même format de date partout afin d’avoir la même période dans toutes vos tables.

L’idéal serait donc de pouvoir repasser dans un format de dates plus conventionnelles (AAAA-MM-JJ par exemple) mais tout en gardant le système de dynamisation.

C’est ce que nous allons faire ici ensemble, via l’écriture d’un peu de mashup ! Pour rappel, Power Query n’est « qu’un » générateur de code en langage m (ou aussi appelé Mashup), dont vous pouvez voir l’appercu dans Power Query > Accueil > Editeur avancé. Si on en comprend la nomenclature il est possible d’écrire son propre code sans passer par l’interface.

Notre objectif va être simple : Recréer dynamiquement au format AAAA-MM-JJ les dates de début et de fin du mois précédent afin de pouvoir les placer dans mes appels (à noter que la même logique pourra être appliquée pour les autres types de périodes). Si je suis par exemple le 3 mai 2022, le code devra alors me donner 2022-04-01 et 2022-04-30.

Construction de la requête

La première étape va être pour nous de créer une requête vite. Pour cela allez dans Accueil > Nouvelle Source > Requête vide.

Allez maintenant dans Accueil > Editeur avancé pour afficher le code. Vous devriez avoir quelque chose comme ceci :

La première étape pour nous va être de récupérer la date du jour, afin d’en déduire le mois précédent. Ainsi, à chaque mise à jour du fichier, la date du jour sera automatiquement updatée. Nous allons pouvoir utiliser la fonction DateTime.LocalNow() pour cela. On va également renommer l’étape en « today » afin que cela soit plus explicite.

Vous devriez avoir un code comme ceci, et obtenir la date du jour dans l’éditeur :

On ajoute maintenant une étape, permettant d’enlever 1 mois à la date du jour via la méthode Date.AddMonths et en y plaçant la valeur -1. Nous sommes maintenant dans le bon mois :

Notre objectif va maintenant être de construire les dates de début et de fin, à partir de la variable lastMonth en extrayant l’année et le mois puis en créant le jour. Commençons par la date de début.

Pour extraire l’année d’une date, on va pouvoir utiliser la méthode Date.year. On va également convertir la valeur au format string via la méthode Number.toText car nous en aurons besoin plus tard :

On va appliquer la même logique pour récupérer le mois, avec la méthode date.Month :

On va ici avoir une difficulté qui n’était pas présente sur l’année : on ne souhaite pas récupérer « 3 » comme valeur mais « 03 », sinon la date ne sera pas au bon format (AAAA-MM-JJ). Pour corriger cela il suffirait de concaténer la valeur récupérée avec « 0 » afin d’obtenir le bon format :

startDateMonth = « 0 »& Number.ToText(Date.Month(LastMonth))

Cependant cette méthode ne fonctionnera pas pour les mois contenant 2 chiffres (octobre, novembre, décembre) puisque cela va nous générer des valeurs du type « 010 ».

Pour prendre en compte des mois particuliers, on va demander à tronquer la valeur pour ne conserver que les 2 derniers caractères, via la méthode text.end :

startDateMonth = Text.End(« 0″& Number.ToText(Date.Month(LastMonth)),2)

Finalement la valeur du jour sera toujours la même : « 01 » pas besoin de code spécifique pour cela !

Il ne reste plus qu’à concaténer l’ensemble dans une variable :

startDate = startDateYear& »-« &startDateMonth& »-01″

Construire la date de fin

Récupérer l’année et le mois de la date de fin ne comporte aucune nouveauté, vous pourrez donc récupérer les valeurs de la même manière que la date de début.

Le jour va en revanche nous poser un peu plus de problème. En effet cette valeur doit représenter le dernier jour du mois, il peut donc être égal à 28,29,30 ou 31.

Dans un premier temps nous appliquons la méthode Date.EndOfMonth pour obtenir la date du dernier jour du mois :

    endDateMonth = Date.EndOfMonth(LastMonth)

On extrait maintenant le jour de cette date via la méthode date.Day et on transforme la valeur en texte :

 endDateMonth = Number.ToText(Date.Day(Date.EndOfMonth(LastMonth)))

Il n’y a plus qu’à concaténer l’ensemble des valeurs pour créer la date de fin (je vais le faire ici en un seul bloc :

endDate = Number.ToText(Date.Year(LastMonth))& »-« &Text.End(« 0″ & Number.ToText(Date.Month(LastMonth)),2)& »-« &Number.ToText(Date.Day(Date.EndOfMonth(LastMonth)))

Retourner les 2 valeurs sous la forme d’une liste et les injecter dans nos appels

Il ne reste plus qu’à retourner les 2 dates sous la forme d’une liste pour en finir avec notre code :

Nous allons maintenant pourvoir intjecter les 2 dates crées dans toutes les sources qui nous intéressent et pas uniquement l’API Piano Analytics.

Pour cela, on édite l’appel en question et on va placer chaque item de notre liste au bon emplacement via la sélection {0} et {1} :

Conclusion

Nous avons vu à travers cet article comment effectuer nos appels API Piano Analytics et comment dynamiser les dates d’appels. Ayez bien en tête que n’importe quelle période est dynamisable et si le cas dont vous aviez besoin n’a pas été vu dans cet article, je vous renvoie vers l’ensemble des méthodes M autour des dates.