[Power Query] Filtre personnalisé

Bonjour,

J'ai vu une question sur un autre forum, qui demandait à repérer une donnée avec un format particulier. Je me suis donc pris au jeu, et j'ai cherché si PQ permettait de filtrer selon un type de données.

J'ai vu une solution imbuvable utilisant Regex dans PQ... J'ai finalement réussi à trouver une solution, et vous propose maintenant ce petit défi.

Dans le fichier joint, seules 2 cellules respectent ce format. A vos claviers

Bonne journée

Edit, je crois que j'ai oublié de préciser le format... C'est un format de type "Téléphone", type "00 00 00 00 00"

Bonjour,

Si j'ai bien compris, il faut valider les numéros de téléphone au format 01 02 03 04 05.

Auquel cas je propose la petite fonction customisée à appeler dans une colonne :

let
    IsValidPhoneNumber = (strPhone as text) => 
    let
        phoneSplit = Text.Split(strPhone, " "),
        nbGroups = List.Count(phoneSplit),
        validLenGroups = List.Transform(phoneSplit, each Text.Length(_) = 2),
        validNums = List.Transform(phoneSplit, each Text.Select(_, {"0".."9"}) = _),

        res = (nbGroups = 5) and List.AllTrue(validLenGroups) and List.AllTrue(validNums)
    in
        res
in
    IsValidPhoneNumber

EDIT : il me manquait la vérification la plus importante : qu'on n'aie que des nombres ... corrigé

Bonjour à tous !

Une proposition ?

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Test = Table.AddColumn(Source, "Test", each List.Accumulate({"0".."9"},[Titre],(state, d) => Text.Replace(state, d, "N")) ="NN NN NN NN NN")
in
    Test

Re-,

Merci pour vos propositions.

Perso, je m'étais un peu (trop?) trituré le cerveau... :)

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Filtre = Table.SelectRows(Source, each Text.Length([Titre]) = 14 and Text.Length(Text.Select([Titre],{"0".."9"}))-List.Count(List.Select(List.Alternate(Text.ToList([Titre]),2,1,0),each _=" "))=6)
in
    Filtre

Bravo, et @ +

Bonjour à tous de nouveau !

cousinhub a écrit :

Perso, je m'étais un peu (trop?) trituré le cerveau... :)

Ce n'est jamais trop en la matière...

Je vous remercie de ce retour.

J'aime observer les différents raisonnements et comment, au final, toutes les routes mènent à Rome...

Bonne journée !

Hello,

Une maigre contribution après l'élite

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Check = Table.AddColumn(Source, "Personnalisé", each List.Accumulate({8,6,4,2},Text.Select([Titre],{"0".."9"}),(s,c)=> Text.Insert(s,c," "))=[Titre])
in
    Check

C'est chouette de tomber sur des petits défis c'était la première fois que j'utilisais Text.Insert

@+

Bonjour à tous de nouveau !

Une autre ?

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Test = Table.AddColumn(Source,  "Test", each List.PositionOfAny(Text.ToList([Titre]), {"0".."9"}, 2)={0,1,3,4,6,7,9,10,12,13})
in
    Test

Hi,

Le moment que vous attendiez tous....

Testé sur une base de 21504 lignes, avec une restitution de 6144 numéros.

Les temps obtenus :

Hub 0,1601563
Saboh 0,2226563
Baroute 0,1640625
JFL 0,1796875
JFL_2 0,1757813

L'avantage, c'est que tout le monde donne le même résultat.

Cependant, les temps fluctuent quelque peu, et sur la vingtaine de tests effectués, globalement, la requête de Saboh prend environ 5 à 6 centièmes de seconde de plus que les 4 autres (qui sont dans les mêmes ordres de grandeur)

Bravo à tous

Hâte de voir le prochain défi et les comparatifs

@+

Oui, ce n'est pas trop surprenant car ma requête a plus de parcours de liste que les autres. Elle était plus "visuelle/pédagogique" (on cherche 5 groupes de deux chiffres). On pourrait l'optimiser en combinant les vérifications validNums et validLenGroups en un parcours unique notamment, et surement par d'autres moyens plus avancés...

Sur les performances il y a peut-être un overheap également dû à l'appel d'une fonction ?

Cependant, en toute "modestie", si je devais classer les requêtes en termes de lisibilité/adaptabilité j'opterais pour ma solution ou la 1e de JFL. Les autres ont beaucoup de "nombres magiques" ou autres astuces pas toujours faciles à relire/modifier-adapter.

En attente du nouveau défi mais j'ai bien peur que sorti des "analyses de texte" je sois largué

Bonsoir à tous !

De la difficulté de procéder à des mesures "objectives"......

J'ai utilisé l'outil diagnostic de Power BI Desktop (Pertinence ?)

Trois mesures ont été effectuées (Test 1 pour l'ensemble des propositions. Puis Test 2.....) sur la base des données du tableau structuré "Tableau1".

Un tableau Excel récapitulatif donne ceci :

Les écarts pour une même requête sont étonnants.

Bonjour
Je suis nouveau venu et je souhaite vous partager une autre approche
en testant la longueur, la présence uniquement de nombres et d'espace ainsi que la position des espaces

Text.Length([Titre])=14 
and List.IsEmpty(List.RemoveMatchingItems(Text.ToList([Titre]),{"0".."9"," "})) 
and Text.PositionOf([Titre], " ", 2) = {2,5,8,11}

Sinon,
- "NN 12 34 56 78" passe le test de JFL1
on peut substituer N par 0 avec donc un remplacement de moins

List.Accumulate({"1".."9"},[Titre],(state, d) => Text.Replace(state, d, "0")) ="00 00 00 00 00"

- "01A23 45 67 89" passe le test de JLF2
on peut ajouter un test qui valide le nombre et la position des espaces (comme mon 3e argument)

Re-,

raccourcix a écrit :

Bonjour
Je suis nouveau venu

Hi, mais tellement bienvenu

Welcome Stéphane, un aficionado PQ (entre autre) de plus, Yesssss!

Bonsoir à tous de nouveau !

raccourcix a écrit :

Je suis nouveau venu.......

Je me réjouis de votre présence et de votre contribution à ce forum !

Remarque : Bien vu les trous dans la raquette....

Merci du retour !

Je vois que mon pseudo n'est pas inconnu de certain

Une autre possibilité

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Remplacement = List.Buffer( List.Zip({{"1".."9"}, List.Repeat({"0"}, 9)})),
    Test = Table.AddColumn(Source, "Test", each Text.Combine(List.ReplaceMatchingItems(Text.ToList([Titre]), Remplacement)) ="00 00 00 00 00")
in
    Test

Stéphane

Autre approche en réalisant un List.Accumulate non pas sur chaque ligne une par une mais pour remplacer chacun des chiffres de 1 à 9 par 0 dans la colonne en entier

je n'ai pas testé l'efficacité sur une grande base

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Copie = Table.DuplicateColumn(Source, "Titre", "Test"),
    Liste_remplacement = List.Buffer(List.Zip({{"1".."9"}, List.Repeat({"0"},9)})),
    Remplacement = List.Accumulate(Liste_remplacement, Copie, (s,c)=> Table.ReplaceValue(s, c{0}, c{1}, Replacer.ReplaceText, {"Test"})),
    Test = Table.TransformColumns(Remplacement,{{"Test", each _ ="00 00 00 00 00", type logical}})
in
    Test

EDIT
la gestion en liste de liste peut être intéressante quand on doit remplacer des chaînes par d'autres qui ne sont pas identiques.
ici, on remplace les caractères 1 à 9 par 0. on peut donc simplifier en

let
    Source =Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Copie = Table.DuplicateColumn(Source, "Titre", "Test"),
    Remplacement = List.Accumulate({"1".."9"}, Copie, (s,c)=> Table.ReplaceValue(s, c, "0", Replacer.ReplaceText, {"Test"})),
    Test = Table.TransformColumns(Remplacement,{{"Test", each _ ="00 00 00 00 00", type logical}})
in
    Test

Stéphane

Bonjour à tous !

Une autre ?

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Test = Table.AddColumn(Source, "Test", each  List.Transform(Text.ToList([Titre]), each try if Number.From(_) is number then "0" else _ otherwise _) = {"0","0"," ","0","0"," ","0","0"," ","0","0"," ","0","0"}, type logical)
in
    Test

Bonjour JFL,

j'avais aussi imaginé une solution avec Number.From mais il y a au moins une exception (très tordue il est vrai !) avec le caractère infini

∞ = Character.FromNumber(8734)

Number.From("∞") = Infinity

et Infinity is number renverra True et donc un 0

Enfin, d'ici à avoir ce caractère dans une liste de n° de téléphone !

Stéphane

Bonjour à tous de nouveau !

@raccourcix : Hello !

Un remède ?

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Test = Table.AddColumn(Source, "Test", each  List.Transform(Text.ToList([Titre]), each if List.Contains({49..57},Character.ToNumber(_)) then "0" else _ )= {"0","0"," ","0","0"," ","0","0"," ","0","0"," ","0","0"}, type logical )
in
    Test

Je n'ai décelé aucun trou...... Suis-je (trop) optimiste ?