Power Query Index TP de l'INSEE - Ajuster une formule à chaque mise à jour d'un tableau généré avec PQ

[Emeric_Pasteur]

Bonjour, je m'appelle Emeric et je suis nouveau sur le forum.
Je débute avec l'utilisation de Power Query.

Voici un fichier joint et une description de ce que j'essaie de faire.
Objectif : télécharger sur le site de l'INSEE tous les index TP puis en extraire une valeur

Structure du classeur :
- une feuille 1 "Recherche INDEX TP", pour que l'utilisateur précise les critères (critères : un index, par exemple TP03, et une date, par exemple 08-2024)
- une feuille 2 "PQ valeurs index TP tous", Power Query avec la requête vers le site de l'INSEE qui regroupe tous les index TP
- d'autres feuilles sans intérêt pour ma question
Fonctionnement :
Dans la feuille 2 avec la PQ, j'ai ajouté un second tableau qui vérifie si la ligne est la bonne (avec le bon index TP et la bonne date) afin d'afficher la valeur "1" ; la feuille 1 recherche la ligne avec la valeur "1" et en extrait la valeur de l'index TP.

Question :
Je ne suis pas sûr que mon classeur fonctionnera lors de la prochaine mise à jour de la base des index de l'INSEE (le mois prochain) : le nombre de lignes de la PQ va changer me semble-t-il, mais quid du second tableau à côté ?
Cela va-t-il fonctionner ?
Sinon, comment puis-je corriger les feuilles ?
Voire, y a-t-il une autre approche plus simple (pas de VBA...) ?

Merci à vous par avance.

 

[wDog66]

Bonjour Emeric,

Très bonne idée de récupérer les index avec PQ

En revanche, le tableau complémentaire n'en est effectivement pas une.
Voici le fichier avec les 2 formules modifiées (à vérifier)

Ceci dit, je pense que tu pourrais améliorer ta requête "Séries" en appelant le tableau des identifiants
@chris tu pourrais l'aider sur le sujet STP, je sais que tu es une pro de PQ

A+

 

[JHA]

Bonjour à tous,

Cette formule en "B8" doit te donner le résultat du tableau "Series".

=INDEX(Series[Valeur];EQUIV('Recherche INDEX TP'!$B$5&'Recherche INDEX TP'!$F$6;Series[INDEX]&Series[Date valeur];0))

JHA

 

[chris]

Bonjour à tous

Requêtes optimisées et formules modifiées (v2)

Mais on peut directement filtrer la requête avec les éléments choisis ce qui allège le classeur (v3)

 

[wDog66]

Re,

Perso, je préfère la version "final2" pas besoin d'actualiser lors d'un changement de choix
mais dans les 2 versions j'y vois un GROS inconvénient

Si je veux ajouter un index, le BT47 par exemple dans la liste des index
Index du bâtiment - BT47 - Électricité - Base 2010 Identifiant 001710979

J'actualise "PQ Index", puis "PQ valeurs index TP tous"
Et bien vu que la requête se base sur des valeurs inscrites en dur, je n'ai pas le BT47

A+

 

[chris]

RE

Si je veux ajouter un index, le BT47 par exemple dans la liste des index
Index du bâtiment - BT47 - Électricité - Base 2010 Identifiant 001710979

La requête initiale cible en dur certains index uniquement

Si on veut pouvoir ajouter des index il faut lister les index à récupérer afin de paramétrer la requête...
Facile à faire mais ce n'est pas le demande initiale...

Perso, je préfère la version "final2" pas besoin d'actualiser lors d'un changement de choix

L'actualisation peut être automatisée sans problème lors du changement de l'index ou année ou mois (à définir...)
Le fichier est 5 fois plus léger...

 

[Emeric_Pasteur]

J'espère ne froisser personne avec le tutoiement. Sacrée mobilisation autours de ce post ! Je vais essayer de n'oublier personne.

@wDog66
Merci pour les modifications : cela fonctionne très bien sans le tableau que j'avais ajouté.
En B8 :
Je viens de découvrir que SOMME.SI.ENS() permet de faire une recherche multi-critère (sans faire forcément une somme) ! C'est très utile.
Et la fonction TEXTE() avec l'argument "00" pour mettre un zéro si nécessaire au nombre à 2 chiffres est bien plus appropriée.
En F8 :
Si j'ai bien compris, c'est une formule matricielle, mais je n'ai pas saisi comment cela fonctionne. Je ne sais pas en faire.

@JHA
Merci à toi aussi.
Tu as utilisé le même format de formule (matricielle ?) que wDog66, que je ne maîtrise pas.
Il semblerait que l'on puisse mettre ainsi 2 critères et plage de recherche dans un simple INDEX() EQUIV(). Les 2 formules en B8 et F8 ont ainsi la même structure.
Je vais essayer de comprendre cela.

@chris
Merci aussi.
Tableau V2
J'ai bien vu le travail de nettoyage et de nommage, avec le tableau "Choix", c'est bien plus propre.
Idem pour les formules matricielles.
Remarque : comme B6 dépend uniquement de A6, il n'est pas nécessaire de mettre cette valeur en 3e critère. Si ?

Tableau V3
Cette solution avec PQ m'amène plein de questions :
1. Tu "empêches" l'affichage de Series dans une Feuille à part et l'affichage se fait dans la même feuille "Recherche INDEX TP" que celle qui contient le tableau Choix. Comment fais-tu cela ? (quand j'utilise PQ, j'ai toujours une nouvelle feuille qui se crée)

2. Tu utilises le tri et la fusion de requête ainsi :
#"Lignes triées" = Table.Sort(#"Colonnes supprimées1",{{"INDEX", Order.Ascending}, {"Date valeur", Order.Descending}}),
#"Requêtes fusionnées" = Table.NestedJoin(#"Lignes triées", {"INDEX", "Intitulé", "Date valeur"}, Choix, {"Index", "Intitulé", "Date valeur"}, "Choix", JoinKind.Inner),

Mais comment a-t-on accès à cette fusion multiple (sans passer par l'éditeur avancé), je n'ai qu'un critère quand je clique sur le bouton "Fusionner des requêtes" ? image en pièce attachée
#"Requêtes fusionnées" = Table.NestedJoin(#"Lignes triées", {"INDEX"}, Choix, {"Index"}, "Choix", JoinKind.Inner)

3. Pour info, je m'étais approché de la solution avec PQ, en utilisant la réponse à un autre post du forum (mon fichier -un peu bancal car je ne sais pas faire apparaitre le résultat dans la même feuille !- en pièce attachée) : j'ai nommé les 2 cellules avec les critères critere_index et critere_date
let
//lecture des parametres
index = Excel.CurrentWorkbook(){[Name="critere_index"]}[Content]{0}[Column1],
date = Excel.CurrentWorkbook(){[Name="critere_date"]}[Content]{0}[Column1],
//debut du code
Source = Excel.CurrentWorkbook(){[Name="Series"]}[Content],
#"Type modifié" = Table.TransformColumnTypes(Source,{{"INDEX", type text}, {"Intitule", type text}, {"Base", type text}, {"Serie arretee", type text}, {"Date valeur", type text}, {"Valeur", type number}, {"Date publication", type text}, {"Identifiant", type text}}),
// Filtre selon les parametres
FILTRE = Table.SelectRows(#"Type modifié", each [INDEX] = index and [Date valeur] = date)
in
FILTRE
Quelqu'un peut-il me dire si l'une ou l'autre méthode Table.NestedJoin et Table.SelectRows présente des avantages ou inconvénients appliquées à mon cas ?

4. QUESTION subsidiaire sur les formules matricielles:
Tentative de modification : J'essaie de supprimer l'étape en E6 (concaténer dans une cellule du tableau Choix pour avoir le critère "Date valeur"), en tentant une modif semblable à celle de wDog66 avec la fonction TEXTE (qui utilisait SOMME.SI.ENS sans formule matricielle en B8).
Comme le symbole & peut-il être utilisé pour concaténer dans les formules simples et pour assembler plusieurs arguments dans les formules matricielles, comment peut-on utiliser les 2 ensembles ?
{=INDEX(Series[Valeur];EQUIV(Choix[Index]&Choix[Année]&"-"&TEXTE(Choix[Mois];"00");Series[INDEX]&Series[Date valeur];0))}
spoiler : ça ne marche pas... (j'utilise CTRL+MAJ+Entrée)
celle-là non plus...
{=INDEX(Series[Valeur];EQUIV(Choix[Index]&CONCATENER(Choix[Année];"-";TEXTE(Choix[Mois];"00"));Series[INDEX]&Series[Date valeur];0))}

Bref, merci !
SOMME.SI.ENS() fonctionne très bien dans les formules classiques, c'est ce que j'ai le mieux compris.
La formule matricielle parait très adaptée, il faut donc que j'apprenne à les faire fonctionner.
Et le tri + la fusion ou la recherche de ligne directement dans PQ permet de faire ça aussi très bien.

 

[chris]

RE

Remarque : comme B6 dépend uniquement de A6, il n'est pas nécessaire de mettre cette valeur en 3e critère. Si ?

J'ai mis ceinture et bretelles mais tu as raison

1. Tu "empêches" l'affichage de Series dans une Feuille à part et l'affichage se fait dans la même feuille "Recherche INDEX TP" que celle qui contient le tableau Choix. Comment fais-tu cela ? (quand j'utilise PQ, j'ai toujours une nouvelle feuille qui se crée)

Il faut sortir par Fermer et charger dans et prendre Connexion seulement

Mais comment a-t-on accès à cette fusion multiple

Il faut sélectionner dans chaque table les colonnes à comparer pour la fusion : le nombre n'est pas limité

 

[Emeric_Pasteur]

RE

J'ai mis ceinture et bretelles mais tu as raison

Il faut sortir par Fermer et charger dans et prendre Connexion seulement

Il faut sélectionner dans chaque table les colonnes à comparer pour la fusion : le nombre n'est pas limité

Sélection multiple avec MAJ : OK

Fermer et charger dans + Connexion seulement : ok partiellement
*ça fonctionne si je sélectionne "Tableau" + "Insérer dans feuille de calcul existante" en précisant la réf de la cellule
*par contre rien ne s'affiche si je sélectionne "Ne créer que la connexion"
Est-ce bien cela que tu voulais dire par "prendre Connexion seulement" ?
Fais-tu quelque chose de particulier par exemple avant ou au moment de lancer l'éditeur PQ ?

voir images en fichier joint

Merci beaucoup chris

 

[chris]

RE

Oui Tableau permet de choisir un emplacement et donc pas forcément une nouvelle feuille,
et si on ne veut rien stocker dans le classeur (pour certaines requêtes), on prend Ne créer que la connexion
Je voulais juste indiquer qu'on évite de sortir en fermant juste la fenêtre PQ.

J'ai désactivé pour ma part la création automatique d'un onglet, et je charge ainsi a postériori seulement ce que je veux où je veux

 

[Emeric_Pasteur]

Merci chris,
C'est très clair.

J'en profite pour reposer une des questions sur les formules matricielles, restée sans réponse, au cas où.

4. QUESTION subsidiaire sur les formules matricielles:
Tentative de modification : J'essaie de supprimer l'étape en E6 (concaténer dans une cellule du tableau Choix pour avoir le critère "Date valeur"), en tentant une modif semblable à celle de wDog66 avec la fonction TEXTE (qui utilisait SOMME.SI.ENS sans formule matricielle en B8).
Comme le symbole & peut-il être utilisé pour concaténer dans les formules simples et pour assembler plusieurs arguments dans les formules matricielles, comment peut-on utiliser les 2 ensembles ?
{=INDEX(Series[Valeur];EQUIV(Choix[Index]&Choix[Année]&"-"&TEXTE(Choix[Mois];"00");Series[INDEX]&Series[Date valeur];0))}
spoiler : ça ne marche pas... (j'utilise CTRL+MAJ+Entrée)
celle-là non plus...
{=INDEX(Series[Valeur];EQUIV(Choix[Index]&CONCATENER(Choix[Année];"-";TEXTE(Choix[Mois];"00"));Series[INDEX]&Series[Date valeur];0))}

Précision : la partie concaténée est Choix[Année]&"-"&TEXTE(Choix[Mois];"00")

 

[chris]

Bonjour

INDEX n'a pas besoin d'être validé en matriciel car c'est une fonction naturellement matricielle.

Sur mon fichier Import index TP final2 on peut utiliser

=INDEX(Series[Valeur];EQUIV(Choix[Index]&Choix[Année]&"-"&TEXTE(Choix[Mois];"00");Series[INDEX]&Series[Date valeur];0))

et enlever la colonne Date valeur du tableau structuré Choix

 

[laurent950]

Bonjour @chris

j'ai pas tous compris, avec PQ ont peux directement importer des valeurs depuis internet via Excel ?

 

[Staple1600]

Bonsoir le fil

@laurent950
En attendant que @chris se reconnecte sur XLD
je peux écrire que la réponse est oui
D'où l'intérêt de la chose
Le souci c'est que le langage M est fort différent du VBA
Mais pour débuter avec PQ, on peut débuter en piloter tout à la souris

Pour voir à quoi ressemble le langage M
Faire Données
- Obtenir des données
- Lancer l'éditeur PowerQuery
Puis sélectionner une requête dans l'onglet à droite
Enfin cliquer dans une étape dans ETAPES APPLIQUEES (volet à droite)
et pour finir choisir Editeur avancé
Ce qui donnera par exemple pour la requête Series

Spoiler: Cliquer pour voir le code M

let
    Source = Xml.Tables(Web.Contents("https://bdm.insee.fr/series/sdmx/data/SERIES_BDM/001711007+001710987+001710988+001710989+001710990+001710991+001710992+001710993+001710994+001710995+001710996+001710997+001710998+001710999+001711000+010605983+010777525+010777582+001711001+001711002+001711003+001711004+001796841+001711005+010777583+010777526+001711006")),
    Table1 = Source{1}[Table],
    Table0 = Table1{0}[Table],
    Table2 = Table0{0}[Table],
    Personnalisé1 = Table.ExpandTableColumn(Table2, "Obs", {"Attribute:TIME_PERIOD", "Attribute:OBS_VALUE", "Attribute:DATE_JO"}, {"Date valeur", "Valeur", "Date publication"}),
    #"Colonnes supprimées" = Table.RemoveColumns(Personnalisé1,{"Attribute:FREQ", "Attribute:TITLE_EN", "Attribute:LAST_UPDATE", "Attribute:UNIT_MEASURE", "Attribute:UNIT_MULT", "Attribute:REF_AREA", "Attribute:DECIMALS"}),
    #"Type modifié avec paramètres régionaux" = Table.TransformColumnTypes(#"Colonnes supprimées", {{"Valeur", type number}}, "en-US"),
    #"Type modifié" = Table.TransformColumnTypes(#"Type modifié avec paramètres régionaux",{{"Date publication", type date}, {"Date valeur", type text}}),
    #"Colonnes renommées" = Table.RenameColumns(#"Type modifié",{{"Attribute:IDBANK", "Identifiant"}}),
    #"Colonnes permutées" = Table.ReorderColumns(#"Colonnes renommées",{"Attribute:TITLE_FR", "Date valeur", "Valeur", "Date publication", "Identifiant"}),
    #"Valeur remplacée1" = Table.ReplaceValue(#"Colonnes permutées","–","-",Replacer.ReplaceText,{"Attribute:TITLE_FR"}),
    #"Valeur remplacée2" = Table.ReplaceValue(#"Valeur remplacée1","multi-matériaux","multimatériaux",Replacer.ReplaceText,{"Attribute:TITLE_FR"}),
    #"Valeur remplacée3" = Table.ReplaceValue(#"Valeur remplacée2","- Travaux de maintenance","Travaux de maintenance",Replacer.ReplaceText,{"Attribute:TITLE_FR"}),
    #"Valeur remplacée4" = Table.ReplaceValue(#"Valeur remplacée3","- Travaux d'installation","Travaux d'installation",Replacer.ReplaceText,{"Attribute:TITLE_FR"}),
    #"Fractionner la colonne par délimiteur" = Table.SplitColumn(#"Valeur remplacée4", "Attribute:TITLE_FR", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Attribute:TITLE_FR.1", "INDEX", "Intitulé", "Base", "Série arretée"}),
    #"Colonnes supprimées1" = Table.RemoveColumns(#"Fractionner la colonne par délimiteur",{"Attribute:TITLE_FR.1"}),
    #"Lignes triées" = Table.Sort(#"Colonnes supprimées1",{{"INDEX", Order.Ascending}, {"Date valeur", Order.Descending}}),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Lignes triées", {"INDEX", "Intitulé", "Date valeur"}, Choix, {"Index", "Intitulé", "Date valeur"}, "Choix", JoinKind.Inner),
    #"Autres colonnes supprimées" = Table.SelectColumns(#"Requêtes fusionnées",{"Valeur", "Date publication"})
in
    #"Autres colonnes supprimées"

Pour plus d'infos, voir les moults tutos sur le net

 

[laurent950]

Bonjour @Staple1600,

Je te remercie pour ta réponse et tes éclaircissements. En effet, je ne savais pas qu'il était possible d'importer directement des données depuis le Web avec Power Query et de les pré-traiter avant de les structurer dans Excel. Il semble que Power Query soit particulièrement utile lorsque l'on travaille avec des sources de données statiques, car il permet d'extraire des informations, de les transformer (en quelque sorte, de les mettre en "mémoire tampon"), et de les réinjecter dans un tableau Excel déjà préparé.

Cela dit, je me demande si cette approche reste fiable à long terme. Si la structure de la page web change (par exemple, l'organisation des tableaux ou des colonnes), est-ce que Power Query peut toujours faire correspondre les données correctement ? J'imagine qu'il est nécessaire que la structure soit toujours la même pour que la requête Power Query continue de fonctionner sans souci.

Concernant les index de révision des prix fournis par l'INSEE, comme les indices de bâtiment, travaux publics et autres coûts de production (BT, TP, IM), ils sont directement accessibles via des fichiers CSV et XLSX. Du coup, je me demande s'il est vraiment nécessaire de passer par Power Query pour les importer, puisque les formats proposés sont déjà optimisés pour une intégration rapide dans Excel. Peut-être que je ne vois pas encore tous les avantages à utiliser Power Query dans ce cas précis, mais j'ai du mal à percevoir l'intérêt comparé à une simple importation manuelle des fichiers ?

Merci d'avance pour ton retour et tes conseils !

Merci @Staple1600 pour ta réponse.

laurent