Réduire le nombre de lignes dans un fichier en se basant sur des dates
Bonjour le forum,
Je sollicite une fois de plus vos connaissances pour m'aider à gagner du temps dans le traitement de données brutes que je devrais mettre "en forme".
Dans le passé, j'avais déjà eu une demande un peu similaire et on avait trouvé une solution avec Power Query.
J'ai essayé de repartir de cette solution pour arriver au résultat mais je n'y arrive pas. Et j'ai abandonné après 2 jours. :-(
En fait, j'ai un fichier qui se compose de numéros de référence et pour chaque référence d'une ou plusieurs dates d'entrée et dates sortie ainsi qu'un champ service.
Mon souci est que quand j'ai pour une même référence plusieurs périodes, s'il n'y a pas d'interruption entre la date de sortie et la date d'entrée suivante, il faudrait que cela ne fasse qu'une ligne.
Il s'agit donc de réduire le nombres de lignes.
Par contre, s'il y a un jour entre les deux lignes pour une même référence, on doit garder deux lignes.
Je vous donne un exemple pour que cela soit plus clair.
Hypothèse 1 :
Ref 0001 : 15/01/2020 au 31/01/2020 Terrain 01
Ref 0001 : 01/02/2020 au 17/04/2022 Terrain 01
Ref 0001 : 22/04/2020 au 31/05/2022 Terrain 01
Cela doit devenir dans le fichier :
Ref 0001 : 15/01/2020 au 17/04/2022 Terrain 01
Ref 0001 : 22/04/2020 au 31/05/2022 Terrain 01
-------------------------------------------------
Hypothèse 2 :
Ref 0001 : 15/01/2020 au 30/01/2020 Terrain 001
Ref 0001 : 01/02/2020 au 17/04/2022 Terrain 001
Ref 0001 : 22/04/2020 au 31/05/2022 Terrain 001
Cela doit devenir ou rester dans le fichier :
Ref 0001 : 15/01/2020 au 30/01/2020 Terrain 001
Ref 0001 : 01/02/2020 au 17/04/2022 Terrain 001
Ref 0001 : 22/04/2020 au 31/05/2022 Terrain 001
--------------------------------------------------
Si d'une ligne à l'autre le service change ce n'est pas très grave on peut garder le premier. Ce qui importe c'est la compilation des dates.
J'ai 800.000 lignes avec pour des références parfois des interruptions, parfois pas. J'ai essayé de le faire manuellement, j'ai fais 2.000 lignes en 1 heure et je ne suis pas certain du résultat.
Pourriez-vous m'aider pour "compiler et réduire la taille du fichier en tenant compte des dates et des interruptions ?
Je joins un fichier assez large de 5.000 lignes
D'avance merci pour votre aide.
T.G.
Merci Chris.
De mon premier check, j'ai l'impression que cela me paraît bon.
Questions subsidiaires :
1) Je peux mettre mes 800.000 lignes dans la partie de gauche ?
2) Comment puis-je voir la manière dont tu as procédé ? Histoire, d'essayer la prochaine fois par moi même ....
Mais déjà un tout grand merci pour ton aide.
TG
Bonjour
si tu ajoutes des lignes au tableau, il suffit d'actualiser le tableau de résultats.
Pour voir la requête : Données, Obtenir des données, lancer PwerQuery
Tu as toutes les étapes listées à droite.
Le principe ici http://www.excel-formations.fr/Trucs_astuces/PQ05.php
Bonsoir Chris,
Je te remercie pour ton aide. Cela nettoie fameusement le fichier. J'ai vérifié et je n'ai pas repéré d'erreur. Cela m'a donc fait gagner beaucoup et beaucoup de fatigue.
Par contre, j'ai un autre souci dont je ne m'étais pas rendu compte.
En fait, je rois travailler par année civile. Et je me rends compte que j'ai des rubriques à cheval sur deux années ou même plus !
J'ai essayé d'encore élaguer le travail mais je ne sais pas si c'est possible de le faire automatiquement en power query ou excel vu le nombre de chevauchements sur 800.000 lignes !
J'ai fait manuellement le travail pour les chevauchement sur plus de deux ans. Mais j'ai tout ceux sur 1 année. Quand je dis une année, ce sont les références qui dépassent 1 fois le 31/12.
Je m'explique plus clairement avec un exemple brut.
Hypothèse 1 :
Ref 0001 : 15/01/2020 au 31/01/2021
Ref 0001 : 01/03/2021 au 17/04/2021
Ref 0001 : 22/04/2021 au 31/04/2023
Cela doit devenir dans le fichier :
Ref 0001 : 15/01/2020 au 31/12/2021
Ref 0001 : 01/01/2021 au 31/01/2021
Ref 0001 : 01/03/2021 au 17/04/2021
Ref 0001 : 22/04/2021 au 31/12/2021
Ref 0001 : 01/01/2022 au 31/12/2022
Ref 0001 : 01/01/2023 au 30/04/2023
Penses-tu que power query pourrait-être la solution à ce problème?
Si c'est possible, je peux faire les choses en deux étapes.
Lancer ce que tu m'as configuré. Et copier le résultat, pour le deuxième traitement, le tout dans un autre fichier.
Si pas, je continuerai à la main pas de souci. ;-) c'est déjà du temps de gagné.
Merci d'avance pour ta réponse.
TG
Bonsoir à tous !
Une approche légèrement différente. Basée sur la première contribution de 78chris (
Merci pour votre aide.
Je vais checker cela demain
@78chris est ce que je fais les opérations en deux fois ou est ce que y’a nouvelle version intègre les deux directement ?
RE
La mienne comme celle de JFL (
Teste les deux voir s'il y a écart de durée d'exécution
Bonsoir à tous !
J'ai déjà tenté sur des cas mais pas eu l'impression que cela changeait les choses. Peut-être mal utilisé..
La documentation Microsoft précise que .....
Aucune assurance donc.
A tenter au cas par cas. Il me semble avoir, sur le forum, par ce biais, amélioré substantiellement la vélocité d'une requête traitant plus de 900.000 lignes.
Bonjour,
J'ai d'abord testé la version de Chris et ensuite celle de JFL !
Je suis reparti depuis le début de mon calcul sur 5.000 lignes et j'arrive pour vous deux au même résultat.
J'avais une différence entre votre résultat et le mien. J'ai vérifié et l'erreur venait de chez moi. Dans le flot de modifications manuelles, j'avais mal adapté le chevauchement d'année pour quelques personnes. Donc, pour moi c'est concluant.
On peut mettre sujet résolu !
Merci encore
Je vais d'ailleurs ouvrir un autre sujet... .
T.G.
Bonjour à tous !
Compte tenu de votre volumétrie, le temps d'exécution est acceptable ?
Sinon, je vous remercie de ce retour.
Oui. C'est super.