XL 2019 Power Query - créer un fonction

Flx1er · 28 Mars 2022

Bonjour,
J'ai cherché, mais en vain à écrire une fonction me permettant d'extraire, depuis le nom d'un fichier, l'année et le mois.
J'arrive à mes fins en utilisant les fonctions texte de M.
Comme je traite de nombreux fichiers serait-il possible d'appeler une fonction permettant d'extraire ces 2 informations ?

La structure de nommage des fichiers débute de la même manière :
Je dispose de plusieurs fichiers dont les noms sont structurés de la manière suivante :

séparateur : . (point)
Les 9 premiers caractères : 123456789
année au format : AAAA
mois au format : M (1 à 9) et MM (10 à12)
puis le reste

J'ai commencé à écrire une fonction permettant d'extraire l'année et le mois d'une chaîne de caractère, mais je n'arrive pas à intégrer le Source.Name dans cette fonction, puis à mettre respectivement l'année et le mois dans une colonne.

Je vous remercie de votre aide
Bien cordialement

Hasco · 29 Mars 2022

Re,

Flx1er à dit:
Comment traiter lorsque d'une date est null

Il y a bien des façons.
Mais la meilleur c'est celle qui vous conviendra après avoir fait des essais, tests et autres ratages...
Voici une façon, sur le dernier modèle que je vous ai donné :

(ChaineDate as text) as nullable date =>
let
Resultat = try Date.FromText(Text.Combine({Text.Start(ChaineDate,2),Text.Middle(ChaineDate,2,2),Text.End(ChaineDate,4)},"/"),"fr-FR") otherwise null
in
Resultat

Ici, utilisation de try ..... otherwise, après avoir déclarer la fonction en nullable date.
Il y a d'autres façon, tester le paramètre chaine et sa compostion avant de le traiter, examiner le champ [HasError] en cas d'erreur et autres joyeusetés...

Hasco · 28 Mars 2022

Bonsoir,

Comprends pas vraiment ce que vous voulez faire.
Si ce n'est qu'extraire l'année et le mois, éclatez votre Source.Name pas délimiteur (point) puis en fin de requête, ne conserver que les colonnes qui vous intéresse.
supprimez les étapes qui suivent l'étape :
Transformer le fichier développé
puis créez une nouvelle étape par Transformer/Fractionner la colonne/Par délimiteur qui donnera

= Table.SplitColumn(#"Transformer le fichier développé", "Source.Name", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Source.Name.1", "Source.Name.2", "Source.Name.3", "Source.Name.4", "Source.Name.5"})

Si vous voulez renommer les colonnes et ne conserver que certaines, modifiez dans la barre de formule de Pq l'étape ci-dessus.

= Table.SplitColumn(#"Transformer le fichier développé", "Source.Name", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Nom", "Année", "Mois"})

Remplacez Source.Name.1 par Nom, Source.Name.2 par Année, Source.Name.3 par Mois
Supprimez ~~Source.Name.4~~ et ~~Source.Name.5~~

Ou passez par des étapes de renommage et suppression de colonnes

Si ce n'est pas ça explicitez la demande.

Mettez un exemple (fait main) de ce que vous souhaitez comme résultat.

Cordialement

Flx1er · 29 Mars 2022

Hasco à dit:
Bonsoir,

Comprends pas vraiment ce que vous voulez faire.
Si ce n'est qu'extraire l'année et le mois, éclatez votre Source.Name pas délimiteur (point) puis en fin de requête, ne conserver que les colonnes qui vous intéresse.
supprimez les étapes qui suivent l'étape :
Transformer le fichier développé
puis créez une nouvelle étape par Transformer/Fractionner la colonne/Par délimiteur qui donnera

Si vous voulez renommer les colonnes et ne conserver que certaines, modifiez dans la barre de formule de Pq l'étape ci-dessus.

Remplacez Source.Name.1 par Nom, Source.Name.2 par Année, Source.Name.3 par Mois
Supprimez ~~Source.Name.4~~ et ~~Source.Name.5~~

Ou passez par des étapes de renommage et suppression de colonnes

Si ce n'est pas ça explicitez la demande.

Mettez un exemple (fait main) de ce que vous souhaitez comme résultat.

Cordialement

Bonjour Hasco,

En fait, l'objectif de ce post était de connaître la façon de créer une fonction personnalisée. Mon exemple, de récupérer l'année et le mois par une fonction personnalisé, n'étais pas judicieux.

Je suis donc parti sur la conception d'une nouvelle fonction me permet de formater une chaîne '01012020' en '01/01/2020'. Cette fonction se nomme fnFormatDate.

Par contre, je bloque sur la convertion de cette chaine texte en date : c.f. fonction fnFormatDate (2).

Sur le fichier Fonction2.xlsx, se trouvent les fonctions.

Pouvez-vous me montrer comment convertir une date au format texte en un format date ?

En vous remerciant.

Cordioalement,

Hasco · 29 Mars 2022

Re,

Flx1er à dit:
e bloque sur la convertion de cette chaine texte en date : c.f. fonction fnFormatDate (2).

Utilisez la Fonction Date.From():

Date.From - PowerQuery M

En savoir plus sur les alertes suivantes : Date.From

docs.microsoft.com

vous pourrez même y mettre optionnellement la culture : "fr-FR" pour la france.

VB:

(ChaineDate as text) as text=>
let
    Resultat =Date.From( Text.Start(ChaineDate,2)&"/"&Text.Middle(ChaineDate,2,2)&"/"&Text.End(ChaineDate,4), "fr-FR")
in
    Resultat

Cordialement

Ou

Code:

(ChaineDate as text) as text=>
let
    Resultat = Text.Start(ChaineDate,2)&"/"&Text.Middle(ChaineDate,2,2)&"/"&Text.End(ChaineDate,4)
in
    Date.From(Resultat)

OU

Code:

(ChaineDate as text) as text=>
let
    Resultat = Text.Start(ChaineDate,2)&"/"&Text.Middle(ChaineDate,2,2)&"/"&Text.End(ChaineDate,4)
in
    Date.From(Resultat, "fr-FR")

Avec Date.FromText c'est :

VB:

(ChaineDate as text) as date=>
let
    Resultat = Date.FromText(Text.Start(ChaineDate,2)&"/"&Text.Middle(ChaineDate,2,2)&"/"&Text.End(ChaineDate,4),"fr-FR")
in
    Resultat

Et une dernière, avec Text.Combine et Date.FromText :

Code:

(ChaineDate as text) as date=>
let
    Resultat = Text.Combine({Text.Start(ChaineDate,2),Text.Middle(ChaineDate,2,2),Text.End(ChaineDate,4)},"/")
in
    Date.FromText(Resultat,"fr-FR")

cordialement

P.S: je n'ai pas téléchargé vos fichiers textes.

Flx1er · 29 Mars 2022

Hasco à dit:

Re,

Utilisez la Fonction Date.From():

vous pourrez même y mettre optionnellement la culture : "fr-FR" pour la france.

VB:

(ChaineDate as text) as text=>
let
    Resultat =Date.From( Text.Start(ChaineDate,2)&"/"&Text.Middle(ChaineDate,2,2)&"/"&Text.End(ChaineDate,4), "fr-FR")
in
    Resultat

Cordialement

Ou

Code:

(ChaineDate as text) as text=>
let
    Resultat = Text.Start(ChaineDate,2)&"/"&Text.Middle(ChaineDate,2,2)&"/"&Text.End(ChaineDate,4)
in
    Date.From(Resultat)

OU

Code:

(ChaineDate as text) as text=>
let
    Resultat = Text.Start(ChaineDate,2)&"/"&Text.Middle(ChaineDate,2,2)&"/"&Text.End(ChaineDate,4)
in
    Date.From(Resultat, "fr-FR")

Avec Date.FromText c'est :

VB:

(ChaineDate as text) as date=>
let
    Resultat = Date.FromText(Text.Start(ChaineDate,2)&"/"&Text.Middle(ChaineDate,2,2)&"/"&Text.End(ChaineDate,4),"fr-FR")
in
    Resultat

Et une dernière, avec Text.Combine et Date.FromText :

Code:

(ChaineDate as text) as date=>
let
    Resultat = Text.Combine({Text.Start(ChaineDate,2),Text.Middle(ChaineDate,2,2),Text.End(ChaineDate,4)},"/")
in
    Date.FromText(Resultat,"fr-FR")

cordialement

P.S: je n'ai pas téléchargé vos fichiers textes.

Hasco,
Merci pour ces solutions.
Un dernier point, lorsque la chaine de caractères représentant une date est null, le résultat de la fonction retourne 'error'.

VB:

(ChaineDate as nullable text) as date=>
let
    Resultat = if Value.Is(ChaineDate, type null) then Date.FromText("31/12/2099","fr-FR") else Date.FromText(Text.Start(ChaineDate,2)&"/"&Text.Middle(ChaineDate,2,2)&"/"&Text.End(ChaineDate,4),"fr-FR")
in
    Resultat

Lors du test de la fonction inscrit bien '31/12/2099' lorsque le paramètre est null. Par contre, lorsque j'intégre cette fonction dans requête, une 'error' apparait.
L'erreur suivant apparait : "

Une erreur s'est produite dans la requête « ». DataFormat.Error : Désolé... Nous n'avons pas pu analyser l'entrée fournie à une valeur Date.
Détails :
//"

Comment traiter lorsque d'une date est null
En vous remrciant.
Cordialement,

Hasco · 29 Mars 2022

Re,

Flx1er à dit:
Comment traiter lorsque d'une date est null

Il y a bien des façons.
Mais la meilleur c'est celle qui vous conviendra après avoir fait des essais, tests et autres ratages...
Voici une façon, sur le dernier modèle que je vous ai donné :

(ChaineDate as text) as nullable date =>
let
Resultat = try Date.FromText(Text.Combine({Text.Start(ChaineDate,2),Text.Middle(ChaineDate,2,2),Text.End(ChaineDate,4)},"/"),"fr-FR") otherwise null
in
Resultat

Ici, utilisation de try ..... otherwise, après avoir déclarer la fonction en nullable date.
Il y a d'autres façon, tester le paramètre chaine et sa compostion avant de le traiter, examiner le champ [HasError] en cas d'erreur et autres joyeusetés.

Dans l'exemple que je vous ai donné, tout ce qui n'est pas reconnu comme date ("toto" par exemple) renverra null.

Cordialement

Flx1er · 29 Mars 2022

Hasco,
Un grand merci pour vos explication claires et précises
Bien cordialement

XL 2019 Power Query - créer un fonction

Flx1er

XLDnaute Occasionnel

Pièces jointes

Hasco

Hasco

XLDnaute Barbatruc

Pièces jointes

Flx1er

XLDnaute Occasionnel

Pièces jointes

Hasco

XLDnaute Barbatruc

Date.From - PowerQuery M

Flx1er

XLDnaute Occasionnel

Hasco

XLDnaute Barbatruc

Flx1er

XLDnaute Occasionnel

Discussions similaires