Power Query Optimiser Expression régulière ou autres

Amilo · 17 Octobre 2022

Bonjour le forum,

J'ai un fichier de plus de 4000 lignes avec une colonne de libellés.
Je souhaiterais extraire uniquement l'année dans une nouvelle colonne.
Dans plus de 99% de libellés, il y a une année mais parfois aussi de type date au milieu du texte.

J'ai testé les 3 solutions ci-dessous :
- une expression régulière dans l'éditeur Power query mais qui ne fonctionne pas très bien (voir pièce jointe).
- toujours dans Power query avec l'option "Colonne à partir d'exemple" qui donne de meilleurs résultats.
- et le remplissage instantané directement dans la feuille Excel mais pas très satisfaisant.

Peut-on optimiser l'expression régulière dans Power query ?
Je suis ouvert à tout autre proposition.

En vous remerciant pas avance

Cordialement

Hasco · 20 Octobre 2022

Bonjour,

@Amilo vous dites que vous n'avez que des années de 2020 à 2022 mais votre exemple montre une ligne avec 2018

Dans le fichier joint les cas sont traités de x>2015 à x<2023, à vous de corriger dans la requêtes 'Remplacements' qui liste les items à remplacer.

Pour la ligne :

Ext provision 06/22 de 2018 sur facture n° 2072

Deux lignes sont retournées, une pour 2022 (06/22) et une pour 2018, à vous d'adapter et choisir laquelle vous voulez. List.First pour obtenir la première ou List.Last pour la dernière.

cordialement

merinos-BernardEtang · 17 Octobre 2022

Salut @Amilo ,

C'est pourtant simple: il suffit de separer les mots ...

Merinos...

Amilo · 18 Octobre 2022

Bonjour merinos, le forum,

@merinos ,merci pour votre réponse qui m'a orienté vers une autre solution Power query,
Je vais tester en fin de journée sur mon fichier de plus de 4000 lignes pour voir ce que cela donne.

Cordialement

merinos-BernardEtang · 18 Octobre 2022

j'aime pas trop de filtrer sur des valeurs fixes ...

Le choix de garder les intitulés est valable, mais il charge la memoire...
pour 4.000 lignes ce ne devrait pas être un problème. j'en ai chargé 18.000.000 ... je dois plus optimiser.
C'est pour cela que je viens d'upgrader ma mémoire... 64 BG.

Amilo · 18 Octobre 2022

Re @merinos ,

Généralement je préfère et me créer aussi un filtre dynamique,
Cela dépend du contexte,

Bonne journée
Cordialement

merinos-BernardEtang · 19 Octobre 2022

Salut @Amilo ,

Alors?

Hasco · 19 Octobre 2022

Bonjour,

Dans le fichier joint une tentative avec le test suivant pour retourner l'année :

let
Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
#"Valeur remplacée" = Table.ReplaceValue(Source,"/"," ",Replacer.ReplaceText,{"Libellés"}),
Résultat = Table.AddColumn(#"Valeur remplacée", "Personnalisé", each List.First(List.Select(Text.Split([Libellés]," "), each let x = try Int32.From(_) in if x[HasError] then false else x[Value]>2010 and x[Value] < 2031)))
in
Résultat

Après remplacement des / par des " "
Pour chaque ligne le test sélectionnera la première valeur numérique comprise entre 2010 et 2031 contenu dans la liste issue de l'éclatement de la chaîne de caractères.

dans x = try Int32.From(_) , try retourne un objet record qui contient un champ indiquant s'il y a une erreur ou non dans la tentative de conversion et un champ avec soit la valeur (HasError=false) ou l'erreur dite (HasError = true)
List.Select admet une fonction renvoyant true ou false pour déterminer si l'élément doit être sélectionné ou non.

Cordialement

Amilo · 19 Octobre 2022

Bonjour merinos, Hasco, le forum,

@merinos , je viens de tester à l'instant sur mon fichier réel de presque 4 200 lignes avec les codes dans les messages #3 et #4 :
- la requête ne pose pas problème de performance ou de lenteur,
- par contre le résultat renvoie seulement 2 600 lignes env. sur les 4 200
- ceci en raison essentiellement d'un cas que je n'avais pas mentionné dans mon 1er message à savoir du texte de type 07/22 pour 07/2022...etc. Du coup, toutes ces lignes sont filtrées/masquées

@Hasco , merci pour votre proposition :
- aucun souci au niveau performance,
- l'autre point positif est que la requête génère l'ensemble des lignes à savoir les 4 200 lignes
- sinon comme pour le souci évoqué ci-dessus, j'ai en réalité 3 autres formats que je n'avais pas indiqués dans mon 1er fichier exemple, dont le 07/22. Il y a presque 1 500 lignes de ce type (02/22, 04/22, 06/22, 09/21...etc)

Sinon, pour information je n'aurai que des années entre 2020 et 2022

J'ai repris le fichier de Hasco en ajoutant 3 cellules avec le texte en rouges dans le fichier source.
Le fichier réel est fixe, il n'est pas amené à évoluer.
Aussi, le fait d'ajouter les dates à la main n'est pas trop problématique, cela sera fait qu'une seule fois.
Je creuserai aussi de mon côté pour trouver éventuellement une solution pour tous ces cas.
Cela servira peut-être pour d'autres situations.

Merci encore et désolé pour mon premier fichier avec les cas manquants

Cordialement

merinos-BernardEtang · 19 Octobre 2022

il y a des moement ou l'on ne peut repondre:

Ext provision 06 2022 de 2018 sur facture n° 2072

Oui mais comment faire quand on a " Ext provision 06 2022 sur facture n° 2021 de 04 2020 "
Surtout qu'on peut l'écrire dans le désordre...

C'est infini.

Amilo · 20 Octobre 2022

merinos à dit:
il y a des moement ou l'on ne peut repondre:

Ext provision 06 2022 de 2018 sur facture n° 2072

Oui mais comment faire quand on a " Ext provision 06 2022 sur facture n° 2021 de 04 2020 "
Surtout qu'on peut l'écrire dans le désordre...

C'est infini.

Re,

Effectivement, je suis conscient qu'il est très difficile voire impossible d'avoir l'ensemble des cas,

Cordialement

merinos-BernardEtang · 20 Octobre 2022

re:
Si on pouvait avoir une regle du style "la première simili date donnée dans l'expression"...

Alors on coupe , puis on applique un dictionnaire puis on garde la premiere ligne qui reste... (remove duplicate)

J'ai appliqué le dictionanire via une fonction lambda. Mais on peut surement y arriver dans PQ

Dans le dictionaire j'ai décidé que ° 2020 est un numero et pas une date

a+
Merinos

Hasco · 20 Octobre 2022

Bonjour,

@Amilo vous dites que vous n'avez que des années de 2020 à 2022 mais votre exemple montre une ligne avec 2018

Dans le fichier joint les cas sont traités de x>2015 à x<2023, à vous de corriger dans la requêtes 'Remplacements' qui liste les items à remplacer.

Pour la ligne :

Ext provision 06/22 de 2018 sur facture n° 2072

Deux lignes sont retournées, une pour 2022 (06/22) et une pour 2018, à vous d'adapter et choisir laquelle vous voulez. List.First pour obtenir la première ou List.Last pour la dernière.

cordialement

Amilo · 20 Octobre 2022

Bonsoir à tous,

@merinos , merci pour votre autre solution, je l'ai testée mais malheureusement je n'ai pas la fonction Lamba avec ma version Excel,

Hasco à dit:
Bonjour,

@Amilo vous dites que vous n'avez que des années de 2020 à 2022 mais votre exemple montre une ligne avec 2018

@Hasco , effectivement dans mon exemple j'ai mis 2018, en fait j'avais fait ce fichier rapidement sans avoir en tête tous les cas de mon fichier réel.

Sinon, très bien votre proposition notamment avec la requête de "Remplacement",
J'aime bien les codes à la fois courts sur une ligne et bien pensés pour ne pas dire "avancés"

En effet, la requête duplique certaines lignes notamment celle que vous évoquez.
Je suis passé par une suppression des doublons et cela fonctionne très bien.

Par contre, j'ai rencontré un autre problème avec le libellé contenant un "underscore" derrière une année de type : 2022_
J'ai fait un simple "Rechercher/Remplacer" dans le fichier source pour traiter ce cas.

Merci en tout cas pour votre aide.

Cordialement

Hasco · 21 Octobre 2022

Amilo à dit:
Bonsoir à tous,

@merinos

Par contre, j'ai rencontré un autre problème avec le libellé contenant un "underscore" derrière une année de type : 2022_
J'ai fait un simple "Rechercher/Remplacer" dans le fichier source pour traiter ce cas.

Bonjour,

A l'étape "Personnalisée ajoutée" (deuxième étape) rajoutez l'underscore dans la liste des délimiteurs.

Splitter.SplitTextByAnyDelimiter({" ",".","_"})

Amilo · 21 Octobre 2022

Hasco à dit:
Bonjour,

A l'étape "Personnalisée ajoutée" (deuxième étape) rajoutez l'underscore dans la liste des délimiteurs.

Merci Hasco,
Effectivement cette fonction admet plusieurs délimiteurs,
Je vais l'ajouter, comme ça c'est fait d'emblée pour une prochaine fois.

Cordialement

Power Query Optimiser Expression régulière ou autres

XLDnaute Accro

Pièces jointes

XLDnaute Accro

Pièces jointes

XLDnaute Accro

Pièces jointes

XLDnaute Accro

Pièces jointes

XLDnaute Accro

XLDnaute Accro

XLDnaute Barbatruc

Pièces jointes

XLDnaute Accro

Pièces jointes

XLDnaute Accro

XLDnaute Accro

XLDnaute Accro

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Accro

XLDnaute Barbatruc

XLDnaute Accro

Discussions similaires