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

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+

Bonjour à tous,

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

JHA

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)

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+

RE

wDog66 à dit:

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

Cliquez pour agrandir...

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...

wDog66 à dit:

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

Cliquez pour agrandir...

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...

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.

RE

Emeric_Pasteur à dit:

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

Cliquez pour agrandir...

J'ai mis ceinture et bretelles mais tu as raison

Emeric_Pasteur à dit:

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)

Cliquez pour agrandir...

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

Emeric_Pasteur à dit:

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

Cliquez pour agrandir...

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

chris à dit:

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é

Cliquez pour agrandir...

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

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

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")

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

et enlever la colonne Date valeur du tableau structuré Choix

Bonjour @chris

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

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

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

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