Re,
Comme je n'ai pas envie que ça dure des jours, je reprends.
Dans la dernière étape nous avons sélectionné les feuilles contrats sur leur nom.
Maintenant Power Query nous présente un tableau dont une colonne se nomme 'Data'.
Cette colonne présente à la droite de son nom, une icône avec deux flèches (gauche et droite ou ouest et est)
Cliquer sur cette icône, la liste des colonnes reconnues par PQ dans les items de la colonnes, se développe.
Comme nous savons que la date de fin de contrat (celle qui nous intéresse) est dans la deuxième colonne occupée reconnue par PQ, la sélectionner :
Décochez la case 'Utiliser le nom de la colonne d'origine comme préfixe' pour ne pas avoir de nom de colonne comme Data.Column2
Nous obtenons dans la barre de formule :
= Table.ExpandTableColumn(#"Lignes filtrées", "Data", {"Column2"}, {"Column2"})
Remplacer le deuxième "Column2" par "Statut". Cela évitera d'avoir à créer une étape ultérieure pour renommer la colonne. La Formule devient :
= Table.ExpandTableColumn(#"Lignes filtrées", "Data", {"Column2"}, {"Statut"})
Dans notre colonne nous avons des lignes avec "Date fin", filtrer ces lignes par l'interface (flèche à droite de la colonne 'Statut'), ce qui produira comme formule :
= Table.SelectRows(#"Data développé", each ([Statut] <> "Date Fin"))
Maintenant nous allons transformer les dates et null de la colonne "Statut" en "Clôturé" et "En cours"
Pour cela cliquer sur le Fx à gauche de la barre de formule :
Ce qui crée une étape nommée 'Personnalisée1' dont la formule dans la barre de formule est :
remplacer cette formule par :
= Table.TransformColumns(#"Lignes filtrées1", {{"Statut", each if _ = null then "En cours" else "Clôturé"}})
Table.TransformColumns est une fonction PQ qui permet d'appliquer une transformation à une ou plusieurs colonnes.
La première paires de crochets contient la liste des paires {nom colonne,fonction de transformation} entre crochets également.
{"Statut", each if _ = null then "En cours" else "Clôturé"}
each* est un élément permettant d'appliquer en boucle ce qui le suit sur les items de la colonne "Statut"
Chacun des items lorsqu'il est parcouru se retrouve grâce à each dans une variable implicite dont le nom est '_' (trait de soulignement ou underscore).
donc pour chaque item de la colonne Statut si sa valeur est nulle alors on a affaire à un contrat en cours sinon on a affaire à un contrat Clôturé.
*dans d'autres fonctions que "Table.TransformColumns" l'underscore contient l'enregistrement complet de la ligne parcourue
On a presque fini.
En double-cliquant sur l'étiquette de la colonne 'Name' on peut renommer cette colonne en 'Contrat'
Et pour finir on peut sélectionner les colonnes à retourner (Onglet 'Accueil' bouton 'Choisir les colonnes')
Cela peut se faire également en cliquant sur l'entête de la colonne 'Contrat' puis avec la touche maj enfoncée, sur l'entête de la colonne 'Etat' et enfin sur 'Supprimer les colonnes/Supprimer les autres colonnes').
Voilà c'est fini.
La difficulté pour vous dans la réalité sera l'étape de sélection de la bonne colonne de date de fin. Car je suppose que vos feuilles ne contiennent pas uniquement 4 cellules occupées.
Faites déjà ce qui précède sur le dernier fichier exemple donné, et pour le reste nous verrons plus tard.
La formule suivante fait la même chose mais n'est valide que pour les version 365 et + de excel :
=LET(
ToutesFeuilles;STXT(NomsFeuilles;TROUVE("]";NomsFeuilles)+1;9^9);
FeuillesContrats;FILTRE(ToutesFeuilles;EXACT(ToutesFeuilles;MAJUSCULE(ToutesFeuilles)));
TestsClôtures;N(N(INDIRECT(CONCATENER("'";FeuillesContrats;"'!i2")))>0);
Résultat;SI(ESTVIDE('Contrats'!$E$10);FeuillesContrats;FILTRE(FeuillesContrats;TestsClôtures= N('Contrats'!$E$10="Clôturé")));
Résultat)
Où 'NomsFeuilles' est un nom contenant la fonction xlmacro4 LIRE.CLASSEUR(1) (=TRANSPOSE(LIRE.CLASSEUR(1))) Etant une fonction xlmacro4 elle oblige à enregistrer le classeur avec l'extension .xlsm même s'il ne contient aucune macro vba.
Si vous voulez en voir les variables intermédiaires, remplacez le dernier 'Résultat' (juste avant la parenthèse finale) par le nom de la variable voulue (ToutesFeuilles, FeuillesContrats,TestsClôtures)
Le nom 'NomFeuilles' quant à lui renvoie :
[ListeContrats2022.06.23.xlsm]Contrats
[ListeContrats2022.06.23.xlsm]QERHY
[ListeContrats2022.06.23.xlsm]AEBYT
[ListeContrats2022.06.23.xlsm]NHSDTHN
[ListeContrats2022.06.23.xlsm]KLFJ-HGTH
[ListeContrats2022.06.23.xlsm]BGRECZ
[ListeContrats2022.06.23.xlsm]CGYJT
[ListeContrats2022.06.23.xlsm]KLJFS
[ListeContrats2022.06.23.xlsm]QAEFV
[ListeContrats2022.06.23.xlsm]DFRYZE
[ListeContrats2022.06.23.xlsm]Listes
[ListeContrats2022.06.23.xlsm]AddNew
Toutes les feuilles du classeurs
Bon week-end
