Power query : Appeler une API à partir des résultats d'une autre API

Le 23/05/2020 à 20:39

Bonjour à tous,

Je cherche à accéder aux données d'une application Web à partir des API qu'elle met à disposition : API rest, qui retournent du JSON, authentication basique HTTP.

J'ai une première API qui retourne des compagnies (id et nom) :

https://monsite/rest/companies

J'ai une deuxième API qui retourne des contrats :

https://monsite/rest/contrats/{id compagnie}

et qui dépend donc d'une compagnie. Ex : https://monsite/rest/contrats/2

J'arrive très bien à importer le contenu de ces deux API avec les fonctionnalités de Power Query.

Seulement, j'aimerais importer dans la même table les contrats de toutes les compagnies et je n'ai rien trouvé dans la documentation pour cela...

En gros, ça reviendrait à l'algorithme suivant :

TANT QUE j'ai des compagnies dans ma table compagnie

Je récupère l'Id de la compagnie

J'appelle l'API contrats avec pour paramètre l'id de la compagnie

Je rajoute ça dans ma table contrats

Je passe à la compagnie suivante

Je ne souhaite pas faire cela en VBA mais plutôt avec les fonctionnalités de Power Query si c'est possible.

Merci pour votre aide.

François

Le 23/05/2020 à 21:11

RE

A priori pas besoin de boucle

J'avais testé l'API google MP : il suffisait d'ajouter une colonne qui utilise l'api.

https://www.excel-downloads.com/threads/calcul-de-distance-et-de-temps-de-trajet-entre-deux-adresses.20036034/#post-20258466

Le 23/05/2020 à 21:38

Bonsoir,

Merci mais je crois que mon cas est un peu différent car mon API contrats retourne plusieurs lignes, que je souhaite ajouter dans ma table contrats pour toutes les compagnies existantes.

Le 23/05/2020 à 21:59

RE

Cela renvoie dans une chaîne ? Dans PQ on peut découper la chaîne tous en générant les lignes

Tes 3 liens revoient une erreur...

Le 24/05/2020 à 12:08

Oui ce sont des liens fictifs car je ne dispose que des API en environnement de production et je ne peux donc pas les partager ici (avec les identifiants / mots de passe).

Mes API retournent du JSON.

Tu m'écris : "Dans PQ on peut découper la chaîne tous en générant les lignes" --> là est sûrement la solution à mon problème s'il y a un parser JSON. Quelles sont les fonctionnalités qui permettraient ces découpages / générations de lignes ?

Merci.

Le 24/05/2020 à 13:22

RE

Je ne connais pas JSON...

Si PowerQuery récupère du texte, il y a plein de fonctions de découpage, plus efficaces que celle d'Excel

Au cours de recherches sur tel ou tel problème PowerQuery, je me souviens avoir croisé des questions et solutions parlant de JSON

En cherchant ces 2 mots on trouve des choses comme

A priori on récupère bien un texte exploitable

As-tu un exemple de ce que récupère une de tes requêtes ?

Le 24/05/2020 à 14:43

Merci. Voici un exemple de JSON pour les compagnies :

[
  {
    "companyId": 1,
    "name": "SOCIETE_A"
  },
  {
    "companyId": 2,
    "name": "SOCIETE_B"
  }
]

et un autre pour les contrats (de la compagnie SOCIETE_B :

[
  {
    "contractId": 194,
    "customerName": "TOTO",
    "customerSector": "Autres",
    "customerSegment": "NA",
    "contractCategoryId": -1,
    "contractCategoryDesignation": "Disabled Setting",
    "tenantID": null,
    "contractAmount": 100000,
    "remoteId": null,
    "title": "Phase 1 - Cadrage",
    "deliveryAmount": 100000,
    "upperLimit": "0",
    "beginDate": "20/12/2015",
    "endDate": "30/04/2016",
    "billingDate": "30/04/2016",
    "companyId": 2,
    "companyName": "SOCIETE_B",
    "businessUnit": null,
    "businessUnitName": null,
    "customerId": 32,
    "contactCustomerId": null,
    "billingMode": 2,
    "billingModeName": "Flat-price contract",
    "billingPlanning": 0,
    "billingPlanningName": "Contract and Down payment",
    "projectId": 8,
    "projectName": "Restatement project",
    "description": null,
    "status": 1,
    "statusName": "In-progress",
    "profitCenter": "Disabled Setting",
    "remark": null,
    "orderNumber": "6001",
    "contractNumber": null,
    "contractTypeId": 6,
    "contractTypeName": "Conseil",
    "expensesFlatRate": -1,
    "billableExpenses": false,
    "contractCurrency": "€",
    "contractCreator": "Kris LABRUNE",
    "affectedCommercialsList": [],
    "affectedProjectManagerList": [],
    "proprieteOnDemand": [],
    "customerCurrency": "€",
    "commercialStatusID": 0,
    "commercialStatusLevel": null,
    "quoteAmount": 0,
    "planningAmount": 71250,
    "deliverablesAmount": 0,
    "payedAmount": 0,
    "billedAmount": 2250,
    "unplannedBalance": 0
  },
  {
    "contractId": 186,
    "customerName": "TATA",
    "customerSector": "Industries",
    "customerSegment": "Sciences de la vie (pharmacie et biotech)",
    "contractCategoryId": -1,
    "contractCategoryDesignation": "Disabled Setting",
    "tenantID": null,
    "contractAmount": 92500,
    "remoteId": null,
    "title": "Conseil Delphi",
    "deliveryAmount": 92500,
    "upperLimit": "0",
    "beginDate": "01/03/2016",
    "endDate": "31/05/2016",
    "billingDate": "31/05/2016",
    "companyId": 2,
    "companyName": "SOCIETE_B",
    "businessUnit": null,
    "businessUnitName": null,
    "customerId": 9,
    "contactCustomerId": null,
    "billingMode": 2,
    "billingModeName": "Flat-price contract",
    "billingPlanning": 0,
    "billingPlanningName": "Contract and Down payment",
    "projectId": null,
    "projectName": null,
    "description": null,
    "status": 1,
    "statusName": "In-progress",
    "profitCenter": "Disabled Setting",
    "remark": null,
    "orderNumber": null,
    "contractNumber": null,
    "contractTypeId": 6,
    "contractTypeName": "Conseil",
    "expensesFlatRate": -1,
    "billableExpenses": false,
    "contractCurrency": "€",
    "contractCreator": "Admin Fitnet",
    "affectedCommercialsList": [],
    "affectedProjectManagerList": [],
    "proprieteOnDemand": [],
    "customerCurrency": "€",
    "commercialStatusID": 0,
    "commercialStatusLevel": null,
    "quoteAmount": 92500,
    "planningAmount": 70400,
    "deliverablesAmount": 0,
    "payedAmount": 18500,
    "billedAmount": 18500,
    "unplannedBalance": 0
  },
  {
    "contractId": 188,
    "customerName": "TATA",
    "customerSector": "Industries",
    "customerSegment": "Sciences de la vie (pharmacie et biotech)",
    "contractCategoryId": -1,
    "contractCategoryDesignation": "Disabled Setting",
    "tenantID": null,
    "contractAmount": 0,
    "remoteId": null,
    "title": "Conseil Delphi II",
    "deliveryAmount": 0,
    "upperLimit": "0",
    "beginDate": "02/05/2016",
    "endDate": "30/09/2016",
    "billingDate": null,
    "companyId": 2,
    "companyName": "SOCIETE_B",
    "businessUnit": null,
    "businessUnitName": null,
    "customerId": 9,
    "contactCustomerId": null,
    "billingMode": 0,
    "billingModeName": "Daily price contract",
    "billingPlanning": 0,
    "billingPlanningName": "Contract and Down payment",
    "projectId": null,
    "projectName": null,
    "description": null,
    "status": 0,
    "statusName": "Opportunity",
    "profitCenter": "Disabled Setting",
    "remark": null,
    "orderNumber": null,
    "contractNumber": null,
    "contractTypeId": 4,
    "contractTypeName": "Prestations de service",
    "expensesFlatRate": -1,
    "billableExpenses": false,
    "contractCurrency": "€",
    "contractCreator": "Admin Fitnet",
    "affectedCommercialsList": [],
    "affectedProjectManagerList": null,
    "proprieteOnDemand": [],
    "customerCurrency": "€",
    "commercialStatusID": 8,
    "commercialStatusLevel": 30,
    "quoteAmount": 86000,
    "planningAmount": 0,
    "deliverablesAmount": 0,
    "payedAmount": 0,
    "billedAmount": 0,
    "unplannedBalance": 0
  },
  {
    "contractId": 185,
    "customerName": "TITI",
    "customerSector": "Distribution",
    "customerSegment": "Grande distribution",
    "contractCategoryId": -1,
    "contractCategoryDesignation": "Disabled Setting",
    "tenantID": null,
    "contractAmount": 36000,
    "remoteId": null,
    "title": "Audit interne",
    "deliveryAmount": 36000,
    "upperLimit": "0",
    "beginDate": "01/03/2016",
    "endDate": "30/12/2016",
    "billingDate": "01/03/2016",
    "companyId": 2,
    "companyName": "SOCIETE_B",
    "businessUnit": null,
    "businessUnitName": null,
    "customerId": 7,
    "contactCustomerId": null,
    "billingMode": 0,
    "billingModeName": "Daily price contract",
    "billingPlanning": 0,
    "billingPlanningName": "Contract and Down payment",
    "projectId": null,
    "projectName": null,
    "description": null,
    "status": 1,
    "statusName": "In-progress",
    "profitCenter": "Disabled Setting",
    "remark": null,
    "orderNumber": null,
    "contractNumber": null,
    "contractTypeId": 4,
    "contractTypeName": "Prestations de service",
    "expensesFlatRate": -1,
    "billableExpenses": false,
    "contractCurrency": "€",
    "contractCreator": "Admin Fitnet",
    "affectedCommercialsList": [],
    "affectedProjectManagerList": [],
    "proprieteOnDemand": [],
    "customerCurrency": "€",
    "commercialStatusID": 0,
    "commercialStatusLevel": null,
    "quoteAmount": 91000,
    "planningAmount": 36000,
    "deliverablesAmount": 0,
    "payedAmount": 0,
    "billedAmount": 9600,
    "unplannedBalance": 26400
  }
]
Le 24/05/2020 à 15:45

RE

Pour la première ajouter une colonne personnalisée avec la formule

Text.BetweenDelimiters([Colonne],"name#(0022): #(0022)", "#(0022)")

ou Colonne est a remplacer par le nom de la colonne contenant la chaîne

Pour la seconde tu veux récupérer quoi exactement concernant chaque contrat ?

Le 24/05/2020 à 16:34

Merci. Je souhaite récupérer toutes les colonnes de contrat.

Les éléments que tu me donnes vont me permettre de parser les résultats, c'est très bien.

Ce que je ne vois pas bien pour le moment, c'est comment à partir des résultats (dynamiques) de Compagnie, je vais appeler l'API en lui passant en paramètre l'id de chaque compagnie.

Merci pour ton aide.

Le 24/05/2020 à 16:47

RE

A partir du moment ou tu as récupéré l'id c'est une chaîne à assembler avec la colonne ID

Regarde l'exemple Google Map : c'est le même principe

Le 25/05/2020 à 12:21

RE

Je récupère bien tous les contrats avec tous les champs.

As-tu trouv' comment paramétrer ton API une fois l'ID société récupéré ?

Le 25/05/2020 à 12:28

Hélas non, pas encore. Je suis preneur de tes conseils.

Récupérer les compagnies : pas de problème

Récupérer les contrats par rapport à un id de compagnie donné : pas de problème

Récupérer dynamiquement les contrats par rapport à la liste (qui peut bouger) des compagnies : je n'y suis pas encore parvenu

Le 25/05/2020 à 12:49

RE

dans l'exemple du Google map on crée une fonction CaculDistance qui utilise l'info Cle, définit l'usage des variables départ et arrivee en fonction des attentes de l'API

let
CalculDistance = (depart, arrivee) =>
let
    Source = Xml.Document(Web.Contents(
    "https://maps.googleapis.com/maps/api/distancematrix/xml",
    [Query=[origins=depart, destinations=arrivee, mode="driving", sensor="false", key=Record.Field(Table.First(Excel.CurrentWorkbook(){[Name="VOTRE_CLE_API"]}[Content]),"Column1")]])),
    Value = Source{0}[Value],
    Value1 = Value{3}[Value],
    Value2 = Value1{0}[Value],
    Value3 = Value2{2}[Value],
    Value4 = Value3{0}[Value]
in
    Value4
in
CalculDistance

puis on appelle la fonction en colonne ajoutée se basant sur des colonnes de la requête

= Table.AddColumn(#"Valeur remplacée", "Distance", each Number.From(CalculDistance( [Rue départ] & ","&[Ville départ],arrivee))/1000)

Donc principes forcément adaptable à ton API en utilisant comme variable l'ID société récupérée dans une colonne

Rechercher des sujets similaires à "power query appeler api partir resultats"