Bureautique & Productivité

Excel : connecter une API REST avec Power Query

11 min de lecture

Power Query comme client API REST

Power Query peut interroger directement n’importe quelle API REST pour intégrer en temps réel des données externes : cours de devises, météo, statistiques e-commerce, CRM. Plus besoin de copier-coller manuel. Un rafraîchissement clic droit met à jour l’intégralité de votre tableau de bord.

Vue d’ensemble 1 : accéder à l’API

Données > Obtenir des données > À partir d’autres sources > À partir du Web. Saisissez l’URL complète de l’endpoint. Exemple pour récupérer le cours EUR/USD via exchangerate-api :

https://api.exchangerate-api.com/v4/latest/EUR

Vue d’ensemble 2 : parser le JSON

Power Query détecte automatiquement le JSON et affiche le contenu structuré. Cliquez sur Record pour naviguer dans les noeuds. Utilisez Convertir en table pour aplatir la structure.

Vue d’ensemble 3 : authentification

Beaucoup d’API exigent une clé API. Deux modes :

  • Clé en URL : https://api.exemple.com/data?api_key=XXX
  • Clé en header : nécessite le dialogue avancé

Dialogue avancé de la requête Web : onglet Options > Ajouter un en-tête, nom = Authorization, valeur = Bearer XXX.

Vue d’ensemble 4 : gestion de la pagination

Les API limitent souvent à 100 résultats par page. Solution : fonction récursive en M.

let
  GetPage = (page as number) =>
 let
 url = "https://api.exemple.com/clients?page=" & Number.ToText(page),
 data = Json.Document(Web.Contents(url)),
 items = data[results]
 in
 items,
  AllPages = List.Generate(
 () => [page = 1, items = GetPage(1)],
 each List.Count([items]) > 0,
 each [page = [page] + 1, items = GetPage([page] + 1)],
 each [items]
  ),
  Combined = List.Combine(AllPages),
  Table = Table.FromList(Combined, Splitter.SplitByNothing())
in
  Table

Cas pratique 1 : tableau de bord taux de change FCFA

Exporter quotidiennement les taux EUR, USD, GBP vs FCFA. Graphique d’évolution hebdomadaire. Utile pour une PME qui achète en Europe et vend en FCFA.

Cas pratique 2 : CA Stripe en direct

Connexion à l’API Stripe /v1/charges. Récupération des transactions du mois. Consolidation dans un tableau de bord ventes. Remplace l’export manuel du dashboard Stripe.

Cas pratique 3 : leads HubSpot

API HubSpot /crm/v3/objects/contacts. Chaque lundi, rapport sur les leads créés la semaine précédente, répartition par source, lead scoring. Déclenche des actions commerciales ciblées.

Vue d’ensemble 5 : sécurité des clés

Ne jamais coder la clé API en dur dans la requête visible. Solution : stocker la clé dans un paramètre Power Query ou une cellule nommée sur une feuille masquée et protégée par mot de passe.

Vue d’ensemble 6 : gestion des erreurs

Power Query échoue sans gracier si l’API répond 500. Encapsulez avec try…otherwise :

let result = try Json.Document(Web.Contents(url)) otherwise null in result

Vue d’ensemble 7 : rafraîchissement automatique

Données > Propriétés de la requête > cochez Actualiser toutes les X minutes. Idéal pour un écran de monitoring. Alternative : macro VBA déclenchée à l’ouverture du classeur.

Limites

Power Query fait des requêtes synchrones. Pour des volumes importants (plus de 10 000 appels), préférez Python avec pandas puis export CSV consommé par Power Query. La combinaison hybride est souvent optimale.

Conclusion

Power Query transforme Excel en véritable plateforme d’intégration de données. Toutes les API modernes deviennent sources exploitables sans écrire une ligne de Python. Compétence transversale précieuse pour tout analyste.

Voir aussi

Étape 1 : Comprendre pourquoi Power Query est l’outil idéal pour les API REST

Aux Almadies comme à Plateau, beaucoup d’analystes financiers exportent encore les données API en CSV puis les copient-collent dans Excel. Cette méthode artisanale fait perdre une heure par jour. Power Query, intégré nativement à Excel depuis 2016, permet d’interroger une API REST, de transformer le JSON, et de rafraîchir d’un clic. La sortie attendue : un tableau Excel toujours à jour, sans macro VBA.

Power Query utilise le langage M, distinct du langage des formules Excel. M est fonctionnel, sensible à la casse, et ses fonctions clés pour les API REST sont Web.Contents, Json.Document et Table.FromRecords. Cette architecture sépare proprement la collecte (M) de l’analyse (formules Excel et tableaux croisés dynamiques).

Étape 2 : Ouvrir l’éditeur Power Query depuis Excel

Dans Excel, allez dans l’onglet Données, cliquez sur Obtenir des données > À partir d’autres sources > Requête vide. L’éditeur Power Query s’ouvre dans une fenêtre dédiée. À gauche, le panneau des requêtes ; au centre, l’aperçu des données ; à droite, la liste des étapes appliquées. C’est dans ce dernier panneau que vous suivrez chaque transformation.

Cliquez sur Affichage > Éditeur avancé pour basculer en mode code M. C’est là que vous écrirez vos requêtes. Au démarrage, l’éditeur affiche un squelette let ... in ... que vous remplacerez par votre code M complet.

Étape 3 : Appeler une API REST publique avec Web.Contents

Pour un premier test, utilisons l’API publique JSONPlaceholder qui renvoie des données fictives. Dans l’éditeur avancé, remplacez le contenu par :

let
    Source = Web.Contents("https://jsonplaceholder.typicode.com/posts"),
    Json = Json.Document(Source),
    Table = Table.FromRecords(Json)
in
    Table

Cliquez sur Terminé. La sortie attendue : un tableau de 100 lignes avec les colonnes userId, id, title, body. Le test concluant : aucune erreur jaune en haut de la fenêtre, et l’aperçu se remplit immédiatement avec les données du serveur.

Étape 4 : Authentifier avec une clé API en en-tête HTTP

La plupart des API professionnelles (Wave, Mixx by Yas, Orange API, AWS) exigent une authentification par clé en en-tête. Pour ajouter un en-tête Authorization, modifiez l’appel Web.Contents :

Source = Web.Contents(
    "https://api.exemple.com/v1/transactions",
    [Headers = [
        #"Authorization" = "Bearer VOTRE_TOKEN",
        #"Content-Type" = "application/json"
    ]]
)

La syntaxe #"..." est nécessaire pour les noms d’en-têtes contenant des caractères spéciaux ou des tirets. Ne stockez jamais le token en clair dans le code M en production : utilisez la fonctionnalité Informations d’identification de Power Query via Source de données > Modifier les autorisations.

Étape 5 : Gérer les paramètres de requête (query string)

Pour filtrer les résultats côté serveur, par exemple récupérer uniquement les transactions du mois dernier, utilisez l’option Query de Web.Contents :

Source = Web.Contents(
    "https://api.exemple.com/v1/transactions",
    [
        Query = [
            from = "2026-04-01",
            to = "2026-04-30",
            currency = "XOF"
        ],
        Headers = [#"Authorization" = "Bearer VOTRE_TOKEN"]
    ]
)

Power Query encode automatiquement les paramètres en URL et compose l’URL finale. La sortie attendue : seules les transactions d’avril 2026 en FCFA sont rapatriées, ce qui réduit la bande passante consommée et accélère le rafraîchissement.

Étape 6 : Désimbriquer un JSON imbriqué

Les API renvoient souvent du JSON imbriqué : un objet customer dans chaque transaction, lui-même contenant une adresse. Après Json.Document, l’aperçu affiche des cellules Record à développer. Cliquez sur l’icône d’expansion (deux flèches) en haut de la colonne customer, sélectionnez les sous-champs souhaités (name, email, city) et validez.

Pour les listes (par exemple line_items dans une facture), utilisez Développer en lignes. Cela duplique la ligne parent autant de fois qu’il y a d’éléments dans la liste, transformant un JSON imbriqué en table relationnelle exploitable directement par un tableau croisé dynamique.

Étape 7 : Gérer la pagination automatiquement

La plupart des API limitent les réponses à 100 ou 1000 enregistrements et exposent une URL next ou un paramètre page. Pour boucler, écrivez une fonction récursive en M :

let
    GetPage = (page as number) =>
        let
            Source = Web.Contents(
                "https://api.exemple.com/v1/transactions",
                [Query = [page = Number.ToText(page), per_page = "100"]]
            ),
            Json = Json.Document(Source)
        in Json,
    AllPages = List.Generate(
        () => [page = 1, data = GetPage(1)],
        each List.Count([data]) > 0,
        each [page = [page] + 1, data = GetPage([page] + 1)],
        each [data]
    ),
    Combined = List.Combine(AllPages),
    Table = Table.FromRecords(Combined)
in Table

La sortie attendue : Power Query appelle l’API page après page jusqu’à recevoir une page vide, puis fusionne tous les résultats. Pour une base de 5 000 transactions, comptez environ une minute selon la latence de l’API.

Étape 8 : Charger les données dans Excel

Une fois la transformation finalisée, cliquez sur Accueil > Fermer et charger > Fermer et charger dans. Choisissez Tableau et sélectionnez la feuille de destination. Excel crée un tableau structuré avec un nom (par exemple Tableau_Transactions) directement utilisable dans des tableaux croisés dynamiques ou des graphiques.

Pour rafraîchir manuellement, faites un clic droit sur le tableau et choisissez Actualiser. Pour un rafraîchissement automatique à l’ouverture du classeur, allez dans Données > Propriétés de connexion et cochez Actualiser à l’ouverture du fichier.

Étape 9 : Programmer un rafraîchissement périodique

Pour rafraîchir toutes les 15 minutes, restez dans Données > Propriétés de connexion et cochez Actualiser toutes les X minutes. Cette option est précieuse pour un tableau de bord projeté en salle de réunion à Cotonou ou à Lomé : les chiffres se mettent à jour pendant la présentation, sans intervention manuelle.

Pour un rafraîchissement nocturne planifié à 4h du matin, publiez le fichier sur SharePoint Online ou OneDrive Business et utilisez Power Automate avec un déclencheur planifié. Une autre option robuste : un script PowerShell qui lance Excel en mode silencieux, ouvre le fichier, force le rafraîchissement, sauvegarde et ferme. La sortie attendue : le directeur financier ouvre son tableau de bord à 8h et voit les données fraîches du jour.

Étape 10 : Gérer les erreurs et la résilience

Une API peut être indisponible ou renvoyer une erreur 429 Too Many Requests. Encapsulez l’appel Web.Contents dans une expression try ... otherwise :

Source = try Web.Contents("https://api.exemple.com/v1/transactions")
         otherwise null,
Result = if Source = null then
             #table({"erreur"}, {{"API indisponible"}})
         else
             Json.Document(Source)

La sortie attendue : si l’API tombe, le tableau Excel affiche une ligne API indisponible au lieu de planter le rafraîchissement. C’est un signal explicite pour l’utilisateur, et le rapport reste consultable avec les données de la veille mises en cache.

Étape 11 : Sécuriser les credentials

Ne stockez jamais le token API en dur dans le code M : il serait visible par toute personne ouvrant le classeur. Allez dans Données > Obtenir des données > Paramètres de la source de données, sélectionnez l’URL de votre API, cliquez sur Modifier les autorisations et choisissez le type d’authentification (Web API, OAuth 2.0 ou Anonyme).

Power Query stocke alors les credentials dans le coffre Windows de l’utilisateur courant, séparément du fichier .xlsx. Lorsque le fichier est partagé avec un collègue à Saint-Louis ou à Bamako, il devra renseigner ses propres identifiants à la première ouverture, ce qui évite toute fuite de secrets.

Étape 12 : Documenter la requête pour la maintenance

Le langage M autorise les commentaires en ligne avec // et en bloc avec /* ... */. Annotez chaque étape critique : version de l’API utilisée, date de mise en place, contact technique du fournisseur. Cette discipline évite qu’un nouvel arrivant à Yopougon ou à Akpakpa passe une demi-journée à comprendre une requête M existante.

Pour creuser ce sujet et exploiter les données rapatriées, consultez nos guides Excel DAX pour calculer le CA cumulé par boutique et Suivi de trésorerie prévisionnelle 12 mois qui combinent parfaitement avec un flux Power Query temps réel sur l’agrégateur bancaire de votre choix.

Étape 13 : Tester la requête sur un volume réaliste avant production

Avant de mettre la requête en production sur le poste du directeur financier, testez-la sur un volume représentatif. Si l’API expose une route de test (par exemple /v1/transactions?limit=5000), forcez la pagination jusqu’à 5 000 lignes et chronométrez. Une requête qui dépasse trois minutes en chargement initial doit être optimisée : filtrer plus en amont via Query, désactiver les colonnes inutiles, ou activer le mode Importer plutôt que Connexion uniquement.

Vous saurez que tout fonctionne quand final : le tableau Excel se rafraîchit en moins d’une minute pour un volume mensuel typique d’une PME ouest-africaine (entre 500 et 5 000 transactions), et le directeur financier ne perçoit aucun ralentissement lors du clic sur Actualiser tout. Vous avez transformé Excel en client API REST de production, sans une seule ligne de VBA.

Étape 14 : Versionner la requête M sur Git

Le code M peut être copié depuis l’éditeur avancé et versionné dans un dépôt Git interne. À chaque modification, créez une branche, commit, et faites une revue par un collègue. Cette pratique élève la requête Power Query au statut de code de production, avec historique, blame et rollback. Pour une équipe analytics répartie entre Dakar, Abidjan et Lomé, c’est la garantie qu’aucune modification sauvage ne casse le tableau de bord du directeur général.

Partager
#cluster-excel #cluster-satellite #excel

Articles Similaires