[Power Query - M] Text.Contains Part 2

Anonymous Hi,

Thanks for your help.

let
    Source = Folder.Files("C:\MonOneDrive\OneDrive\Power BI - BDD\Cosium\Patient"),
    AddCol_MonFichier = Table.AddColumn(Source, "MonFichier", each [Folder Path]&[Name]),
    AddCol_FuncRecup = Table.AddColumn(AddCol_MonFichier, "FuncRecup", each RecupPatient([MonFichier])),
    #"FuncRecup développé" = Table.ExpandTableColumn(AddCol_FuncRecup, "FuncRecup", {"Nom", "Prénom", "Denomination", "Date Naiss.", "Age", "Centre", "Num. client", "Caché", "Exclus", "Date création", "Numéro de voie", "Type de voie", "Adresse", "Nº app. ou BAL-Etage-Couloir-Escalier", "AdresseL3", "AdresseL4", "Adr. CP 1", "Adr. ville 1", "NPAI", "Origine", "N° de sécu", "CPAM", "Complémentaire", "Date dernière facture", "Prescripteur Aud", "Prix vendu", "Date vente", "Num. série", "Statut", "Code Produit", "Libellé", "Intra/Cont.", "Durée Garantie", "Côté", "Marque"}, {"FuncRecup.Nom", "FuncRecup.Prénom", "FuncRecup.Denomination", "FuncRecup.Date Naiss.", "FuncRecup.Age", "FuncRecup.Centre", "FuncRecup.Num. client", "FuncRecup.Caché", "FuncRecup.Exclus", "FuncRecup.Date création", "FuncRecup.Numéro de voie", "FuncRecup.Type de voie", "FuncRecup.Adresse", "FuncRecup.Nº app. ou BAL-Etage-Couloir-Escalier", "FuncRecup.AdresseL3", "FuncRecup.AdresseL4", "FuncRecup.Adr. CP 1", "FuncRecup.Adr. ville 1", "FuncRecup.NPAI", "FuncRecup.Origine", "FuncRecup.N° de sécu", "FuncRecup.CPAM", "FuncRecup.Complémentaire", "FuncRecup.Date dernière facture", "FuncRecup.Prescripteur Aud", "FuncRecup.Prix vendu", "FuncRecup.Date vente", "FuncRecup.Num. série", "FuncRecup.Statut", "FuncRecup.Code Produit", "FuncRecup.Libellé", "FuncRecup.Intra/Cont.", "FuncRecup.Durée Garantie", "FuncRecup.Côté", "FuncRecup.Marque"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"FuncRecup développé",{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "MonFichier"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"FuncRecup.Date Naiss.", type datetime}, {"FuncRecup.Date création", type datetime}, {"FuncRecup.Date dernière facture", type date}, {"FuncRecup.Date vente", type datetime}}),
    #"DenominationHomme" = Table.AddColumn(#"Type modifié", "Genre1", each if [FuncRecup.Denomination] = "Abbe" then "Homme" else if [FuncRecup.Denomination] = "Fr" then "Homme" else if [FuncRecup.Denomination] = "Frère" then "Homme" else if [FuncRecup.Denomination] = "M" then "Homme" else if [FuncRecup.Denomination] = "M." then "Homme" else if [FuncRecup.Denomination] = "Maitre" then "Homme" else if [FuncRecup.Denomination] = "Mon Père" then "Homme" else if [FuncRecup.Denomination] = "Monseigneur" then "Homme" else if [FuncRecup.Denomination] = "Monsieur" then "Homme" else if [FuncRecup.Denomination] = "Monsieur l' Abbé" then "Homme" else if [FuncRecup.Denomination] = "Mr" then "Homme" else if [FuncRecup.Denomination] = "Mr.Me." then "Homme" else if [FuncRecup.Denomination] = "Pere" then "Homme" else if [FuncRecup.Denomination] = "Père" then "Homme" else if [FuncRecup.Denomination] = "Pr." then "Homme" else ""),
    #"DenominationFemme" = Table.AddColumn(#"DenominationHomme", "Genre2", each if [FuncRecup.Denomination] = "Ma Soeur" then "Femme" else if [FuncRecup.Denomination] = "Madame" then "Femme" else if [FuncRecup.Denomination] = "Mademoiselle" then "Femme" else if [FuncRecup.Denomination] = "Mell" then "Femme" else if [FuncRecup.Denomination] = "Melle" then "Femme" else if [FuncRecup.Denomination] = "MLE" then "Femme" else if [FuncRecup.Denomination] = "Mlle" then "Femme" else if [FuncRecup.Denomination] = "Mlle." then "Femme" else if [FuncRecup.Denomination] = "Mm" then "Femme" else if [FuncRecup.Denomination] = "Mme" then "Femme" else if [FuncRecup.Denomination] = "Mme." then "Femme" else if [FuncRecup.Denomination] = "Mms" then "Femme" else if [FuncRecup.Denomination] = "Soeur" then "Femme" else if [FuncRecup.Denomination] = "Sr" then "Femme" else ""),
    #"DenominationEnfant" = Table.AddColumn(#"DenominationFemme", "Genre3", each if [FuncRecup.Denomination] = "Enf" then "Enfant" else if [FuncRecup.Denomination] = "Enf." then "Enfant" else if [FuncRecup.Denomination] = "Enfant" then "Enfant" else if [FuncRecup.Denomination] = "Enfant." then "Enfant" else ""),
    #"Personnalisée ajoutée" = Table.AddColumn(DenominationEnfant, "Genre", each if [Genre1] = "Homme" then "Homme" else if [Genre2] = "Femme" then "Femme" else if [Genre3] = "Enfant" then "Enfant" else "Autre"),
    #"Lignes vides supprimées" = Table.SelectRows(#"Personnalisée ajoutée", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Doublons supprimés" = Table.Distinct(#"Lignes vides supprimées", {"FuncRecup.Num. client"}),
    #"Valeur remplacée" = Table.ReplaceValue(#"Doublons supprimés"," ans","",Replacer.ReplaceText,{"FuncRecup.Age"}),
    #"Type modifié1" = Table.TransformColumnTypes(#"Valeur remplacée",{{"FuncRecup.Age", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Type modifié1",{"Genre1", "Genre2", "Genre3"})
in
    #"Removed Columns"

If you want something else, tell me.