apply function to a list of columns

[PhilippeMuniesa]

Hello,

I have a table from which I extracted the list of columns including the word Date in the header.

List.Sort(
List.Select(
Table.ColumnNames(DSN_Contrat_developpe),
each Text.Contains(_, "Date")
or Text.Contains(_, "Period")
),
Order.Ascending
)

I also have a function that transforms dates into ddmmyyyy format

//ddmmyyyy
(x as any) =>
let
FormattedDate = #date(Number.From(Text.End(x,4)),Number.From(Text.Middle(x,2,2)),Number.From(Text.Start(x,2)))
in
FormattedDate

But I can't seem to apply my function to the columns of my list.

What would be the correct syntax

Thank you, and Merry Christmas

Philippe Muniesa

[AlB · Accepted solution]

@PhilippeMuniesa 

Place the following M code in a blank query to see the steps. Note that I have done it in PBI instead of Excel and thus the first step (Source) is different because I am just pasting your data in. You'll have to change that first step to what you had initially to read from your table in Excel. Also, note that the query seems long because of the space the first step takes but the important steps only start after your Select_Col_Dates.  

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZXbjtowEIZfxeIaLJ8dXwZi2LQh2SaA2q72AolUakUXCZZKffuOBwLh1K3agnKYmLGd+b/MzNNTh/FOtwMHg18wJWdcMO7CEJxhiAtqTPMs4dLjzZOhUoe7opFpP2sm9y7NyalyOJ/y9l+HAzeSzAh4DzCrOM0npF+mfjqAx/f1+ls9fwFLCM6Dw2NWTEdTX+Z+5rOwGMc/4TIsdys6yVgkbXgvEXEBQUXBDyNRcNFCL+AWjzzsVPlylg58RbJilFaT9MPUV8ExLOhcSx/cnGveNfbEYEdHEW4Cw1CMOaWUNTBb2sMwOD53T7W3p9pzbQ11shFGWUedbTFRmmshooO6TjHq+C3LOEtFg/TWeTgExsMsd1GY/Vjkk3T2CSxLym1NFlsyXP2s16i3VGHqOPYZGcTlQ5x+bFho02JhZFjPaJQUgzxnYeaBRZr4XlWkozzOJ81CB/kPOt9Bfnkqv4qsplbsxRaRjSh+CjyiDRMgoITDzz1ygooQC+xjKK51zVJCCxo1FN/EgEkmAwYdNoGPfexzT6ppSapBUQ6HyJisAUm8/LKuF2RRk/F2s6lfEc0+kVpZdMnFgRfnOMQ4UxC9bJDsqoFSgcvYJ36Q5mTw4Ick8U22XBCSF4Ss7kYKhf9HQKJztTbtS8v1rGifgsmIqvOq9Jb4EsR3JzlQrravkAP1hiSr5ff6ZYNaa7urST6LpwjmTOpsTrL5y6Lu7MqSs7v6yFnApM5SoVWWEqhI10oTydPZhfrifvmhz8sTzKTSdZoGYRR1CIGaSxRCUIVuVjCK9fiqpTXMVn8KKAQrNFPcuTCe+YcixxfDGpXX2x91i0y/yEnpH4sK06efxTN/qC8XTcPyfQJg07C36dxqGm0q+p5V66xh90xEbcNEK4UwBG0wtZAo6AfBs8c5p7JtHPPkLzKGSy72GdPP0iTGGBHIO+jevfF8/bUm5Wq5hFzoHHt5WVSTEmpbfiJe8vmIRDnsHdyF9bEr/QbJHgfxk7hPoJvkAVA+Ism09Jc167829edf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Mois_Dsn = _t, nss = _t, Num_ctratPersonnalisé = _t, #"S21.G00.50.001-Date de versement-" = _t, #"S21.G00.50.002-Rémunération nette fiscale-" = _t, #"S21.G00.50.003-Numéro de versement-" = _t, #"S21.G00.50.004-Montant net versé-" = _t, #"S21.G00.50.006-Taux de prélèvement à la source-" = _t, #"S21.G00.50.007-Type du taux de prélèvement à la source-" = _t, #"S21.G00.50.008-Identifiant du taux de prélèvement à la source-" = _t, #"S21.G00.50.009-Montant de prélèvement à la source -" = _t, #"S21.G00.51.011-Type-001-Rémunération brute non plafonnée" = _t, #"S21.G00.51.011-Type-002-Salaire brut soumis à contributions d'Assurancc chômage" = _t, #"S21.G00.51.011-Type-003-Salaire rétabli - reconstitué" = _t, #"S21.G00.51.011-Type-010-Salaire de base" = _t, #"S21.G00.52.001-Type-027-Prime liée à l'activité avec période de rattachement spécifique 028 - Prime non liée à l'activité" = _t, #"S21.G00.52.001-Type-028-" = _t, #"S21.G00.52.001-Type-011-" = _t, #"S21.G00.52.001-Type-020-" = _t, #"S21.G00.50.005--" = _t, #"S21.G00.52.001-Type-043-" = _t, #"S21.G00.52.001-Type-006-Indemnité conventionnelle de départ à la retraite du salarié 007 - Indemnité légale de licenciement" = _t, #"S21.G00.30.002_Nom de famille" = _t, #"S21.G00.30.003_Nom d'usage" = _t, S21.G00.30.004_Prénoms = _t, S21.G00.30.005_Sexe = _t, #"S21.G00.30.006_Date de naissance" = _t, #"S21.G00.30.007_Lieu de naissance" = _t, #"S21.G00.30.008_Numéro, extension, nature et libellé de la voie" = _t, #"S21.G00.30.009_Code postal" = _t, S21.G00.30.010_Localité = _t, #"S21.G00.30.013_Codification UE" = _t, #"S21.G00.30.014_Code département de naissance" = _t, #"S21.G00.30.015_Code pays de naissance" = _t, #"S21.G00.30.016_Complément de la localisation de la construction" = _t, #"S21.G00.30.019_Matricule de l'individu dans l'entreprise" = _t, #"S21.G00.40.001-Date de début du contrat" = _t, #"S21.G00.40.002-Statut du salarié (conventionnel)" = _t, #"S21.G00.40.003-Code statut catégoriel Retraite Complémentaire obligatoire" = _t, #"S21.G00.40.004-Code profession et catégorie socioprofessionnelle (PCS-ESE)" = _t, #"S21.G00.40.006-Libellé de l'emploi" = _t, #"S21.G00.40.007-Nature du contrat" = _t, #"S21.G00.40.008-Dispositif de politique publique et conventionnel" = _t, #"S21.G00.40.009-Numéro du contrat" = _t, #"S21.G00.40.011-Unité de mesure de la quotité de travail" = _t, #"S21.G00.40.012-Quotité de travail de référence de l'entreprise pour la catégorie de salarié" = _t, #"S21.G00.40.013-Quotité de travail du contrat" = _t, #"S21.G00.40.014-Modalité d'exercice du temps de travail" = _t, #"S21.G00.40.016-Complément de base au régime obligatoire" = _t, #"S21.G00.40.017-Code convention collective applicable" = _t, #"S21.G00.40.018-Code régime de base risque maladie" = _t, #"S21.G00.40.019-Identifiant du lieu de travail" = _t, #"S21.G00.40.020-Code régime de base risque vieillesse" = _t, #"S21.G00.40.024-Travailleur à l'étranger au sens du code de la Sécurité Sociale" = _t]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Mois_Dsn", Int64.Type}, {"nss", Int64.Type}, {"Num_ctratPersonnalisé", Int64.Type}, {"S21.G00.50.001-Date de versement-", Int64.Type}, {"S21.G00.50.002-Rémunération nette fiscale-", type number}, {"S21.G00.50.003-Numéro de versement-", Int64.Type}, {"S21.G00.50.004-Montant net versé-", type number}, {"S21.G00.50.006-Taux de prélèvement à la source-", type number}, {"S21.G00.50.007-Type du taux de prélèvement à la source-", Int64.Type}, {"S21.G00.50.008-Identifiant du taux de prélèvement à la source-", Int64.Type}, {"S21.G00.50.009-Montant de prélèvement à la source -", type number}, {"S21.G00.51.011-Type-001-Rémunération brute non plafonnée", type number}, {"S21.G00.51.011-Type-002-Salaire brut soumis à contributions d'Assurancc chômage", type number}, {"S21.G00.51.011-Type-003-Salaire rétabli - reconstitué", type number}, {"S21.G00.51.011-Type-010-Salaire de base", type number}, {"S21.G00.52.001-Type-027-Prime liée à l'activité avec période de rattachement spécifique 028 - Prime non liée à l'activité", Int64.Type}, {"S21.G00.52.001-Type-028-", Int64.Type}, {"S21.G00.52.001-Type-011-", Int64.Type}, {"S21.G00.52.001-Type-020-", type number}, {"S21.G00.50.005--", type number}, {"S21.G00.52.001-Type-043-", Int64.Type}, {"S21.G00.52.001-Type-006-Indemnité conventionnelle de départ à la retraite du salarié 007 - Indemnité légale de licenciement", Int64.Type}, {"S21.G00.30.002_Nom de famille", type text}, {"S21.G00.30.003_Nom d'usage", type text}, {"S21.G00.30.004_Prénoms", type text}, {"S21.G00.30.005_Sexe", Int64.Type}, {"S21.G00.30.006_Date de naissance", Int64.Type}, {"S21.G00.30.007_Lieu de naissance", type text}, {"S21.G00.30.008_Numéro, extension, nature et libellé de la voie", type text}, {"S21.G00.30.009_Code postal", Int64.Type}, {"S21.G00.30.010_Localité", type text}, {"S21.G00.30.013_Codification UE", Int64.Type}, {"S21.G00.30.014_Code département de naissance", Int64.Type}, {"S21.G00.30.015_Code pays de naissance", type text}, {"S21.G00.30.016_Complément de la localisation de la construction", type text}, {"S21.G00.30.019_Matricule de l'individu dans l'entreprise", Int64.Type}, {"S21.G00.40.001-Date de début du contrat", Int64.Type}, {"S21.G00.40.002-Statut du salarié (conventionnel)", Int64.Type}, {"S21.G00.40.003-Code statut catégoriel Retraite Complémentaire obligatoire", Int64.Type}, {"S21.G00.40.004-Code profession et catégorie socioprofessionnelle (PCS-ESE)", type text}, {"S21.G00.40.006-Libellé de l'emploi", type text}, {"S21.G00.40.007-Nature du contrat", Int64.Type}, {"S21.G00.40.008-Dispositif de politique publique et conventionnel", Int64.Type}, {"S21.G00.40.009-Numéro du contrat", Int64.Type}, {"S21.G00.40.011-Unité de mesure de la quotité de travail", Int64.Type}, {"S21.G00.40.012-Quotité de travail de référence de l'entreprise pour la catégorie de salarié", type number}, {"S21.G00.40.013-Quotité de travail du contrat", type number}, {"S21.G00.40.014-Modalité d'exercice du temps de travail", Int64.Type}, {"S21.G00.40.016-Complément de base au régime obligatoire", Int64.Type}, {"S21.G00.40.017-Code convention collective applicable", Int64.Type}, {"S21.G00.40.018-Code régime de base risque maladie", Int64.Type}, {"S21.G00.40.019-Identifiant du lieu de travail", Int64.Type}, {"S21.G00.40.020-Code régime de base risque vieillesse", Int64.Type}, {"S21.G00.40.024-Travailleur à l'étranger au sens du code de la Sécurité Sociale", Int64.Type}}),


//selectionne les colonnes date ou periode
Select_Col_Dates= List.Sort(
    List.Select(
      Table.ColumnNames(#"Type modifié"),
      each Text.Contains(_, "Date")
        or Text.Contains(_, "Période")
    ),
    Order.Ascending
  ),

inputList_ = List.Zip({Select_Col_Dates, List.Repeat({ddmmaaaa}, List.Count(Select_Col_Dates))}),
inputListTypes_ = List.Zip({Select_Col_Dates, List.Repeat({type date}, List.Count(Select_Col_Dates))}),
Transform_Col_date = Table.TransformColumns(#"Type modifié", inputList_),
#"Change Type of transformed columns" = Table.TransformColumnTypes(Transform_Col_date, inputListTypes_)
  
in
 #"Change Type of transformed columns"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

@PhilippeMuniesa 

It does work. Have you checked the pbix file I uploaded? You can see it all working correctly there.

You have not changed/copied  the function, name nor contents. Note its name is ddmmaaaa, with 4 "a" rather than ddmmaaaa with 5

Plus like I said  it would be best to change the code of the first step (Source) to what you had initially to read from your table in Excel.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB · Accepted solution]

@PhilippeMuniesa 

Note that I've also updated a bit your function, as it was failing to convert to text

See it all at work in the attached file.

(x as any) =>
let   
   x_text = Text.From(x),
   FormattedDate = #date(Number.From(Text.End(x_text,4)),Number.From(Text.Middle(x_text,2,2)),Number.From(Text.Start(x_text,2)))
in
  FormattedDate

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[PhilippeMuniesa]

Hello AIB,

Thanks a lot, it's perfect.

Sorry for aaaaa (5)  and aaaa (4)

By dint of going around in circles, my eyes were tired.

I take advantage of this message to wish a happy end of the year to all the Helper for the time spent getting us out of the tortuous path in which it is easy to get lost (well at the month for me)

And a particular big thank to AIB for the quality of his explanations and the patience he shows.

Philippe

[AlB · Accepted solution]

@PhilippeMuniesa 

Place the following M code in a blank query to see the steps. Note that I have done it in PBI instead of Excel and thus the first step (Source) is different because I am just pasting your data in. You'll have to change that first step to what you had initially to read from your table in Excel. Also, note that the query seems long because of the space the first step takes but the important steps only start after your Select_Col_Dates.  

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZXbjtowEIZfxeIaLJ8dXwZi2LQh2SaA2q72AolUakUXCZZKffuOBwLh1K3agnKYmLGd+b/MzNNTh/FOtwMHg18wJWdcMO7CEJxhiAtqTPMs4dLjzZOhUoe7opFpP2sm9y7NyalyOJ/y9l+HAzeSzAh4DzCrOM0npF+mfjqAx/f1+ls9fwFLCM6Dw2NWTEdTX+Z+5rOwGMc/4TIsdys6yVgkbXgvEXEBQUXBDyNRcNFCL+AWjzzsVPlylg58RbJilFaT9MPUV8ExLOhcSx/cnGveNfbEYEdHEW4Cw1CMOaWUNTBb2sMwOD53T7W3p9pzbQ11shFGWUedbTFRmmshooO6TjHq+C3LOEtFg/TWeTgExsMsd1GY/Vjkk3T2CSxLym1NFlsyXP2s16i3VGHqOPYZGcTlQ5x+bFho02JhZFjPaJQUgzxnYeaBRZr4XlWkozzOJ81CB/kPOt9Bfnkqv4qsplbsxRaRjSh+CjyiDRMgoITDzz1ygooQC+xjKK51zVJCCxo1FN/EgEkmAwYdNoGPfexzT6ppSapBUQ6HyJisAUm8/LKuF2RRk/F2s6lfEc0+kVpZdMnFgRfnOMQ4UxC9bJDsqoFSgcvYJ36Q5mTw4Ick8U22XBCSF4Ss7kYKhf9HQKJztTbtS8v1rGifgsmIqvOq9Jb4EsR3JzlQrravkAP1hiSr5ff6ZYNaa7urST6LpwjmTOpsTrL5y6Lu7MqSs7v6yFnApM5SoVWWEqhI10oTydPZhfrifvmhz8sTzKTSdZoGYRR1CIGaSxRCUIVuVjCK9fiqpTXMVn8KKAQrNFPcuTCe+YcixxfDGpXX2x91i0y/yEnpH4sK06efxTN/qC8XTcPyfQJg07C36dxqGm0q+p5V66xh90xEbcNEK4UwBG0wtZAo6AfBs8c5p7JtHPPkLzKGSy72GdPP0iTGGBHIO+jevfF8/bUm5Wq5hFzoHHt5WVSTEmpbfiJe8vmIRDnsHdyF9bEr/QbJHgfxk7hPoJvkAVA+Ism09Jc167829edf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Mois_Dsn = _t, nss = _t, Num_ctratPersonnalisé = _t, #"S21.G00.50.001-Date de versement-" = _t, #"S21.G00.50.002-Rémunération nette fiscale-" = _t, #"S21.G00.50.003-Numéro de versement-" = _t, #"S21.G00.50.004-Montant net versé-" = _t, #"S21.G00.50.006-Taux de prélèvement à la source-" = _t, #"S21.G00.50.007-Type du taux de prélèvement à la source-" = _t, #"S21.G00.50.008-Identifiant du taux de prélèvement à la source-" = _t, #"S21.G00.50.009-Montant de prélèvement à la source -" = _t, #"S21.G00.51.011-Type-001-Rémunération brute non plafonnée" = _t, #"S21.G00.51.011-Type-002-Salaire brut soumis à contributions d'Assurancc chômage" = _t, #"S21.G00.51.011-Type-003-Salaire rétabli - reconstitué" = _t, #"S21.G00.51.011-Type-010-Salaire de base" = _t, #"S21.G00.52.001-Type-027-Prime liée à l'activité avec période de rattachement spécifique 028 - Prime non liée à l'activité" = _t, #"S21.G00.52.001-Type-028-" = _t, #"S21.G00.52.001-Type-011-" = _t, #"S21.G00.52.001-Type-020-" = _t, #"S21.G00.50.005--" = _t, #"S21.G00.52.001-Type-043-" = _t, #"S21.G00.52.001-Type-006-Indemnité conventionnelle de départ à la retraite du salarié 007 - Indemnité légale de licenciement" = _t, #"S21.G00.30.002_Nom de famille" = _t, #"S21.G00.30.003_Nom d'usage" = _t, S21.G00.30.004_Prénoms = _t, S21.G00.30.005_Sexe = _t, #"S21.G00.30.006_Date de naissance" = _t, #"S21.G00.30.007_Lieu de naissance" = _t, #"S21.G00.30.008_Numéro, extension, nature et libellé de la voie" = _t, #"S21.G00.30.009_Code postal" = _t, S21.G00.30.010_Localité = _t, #"S21.G00.30.013_Codification UE" = _t, #"S21.G00.30.014_Code département de naissance" = _t, #"S21.G00.30.015_Code pays de naissance" = _t, #"S21.G00.30.016_Complément de la localisation de la construction" = _t, #"S21.G00.30.019_Matricule de l'individu dans l'entreprise" = _t, #"S21.G00.40.001-Date de début du contrat" = _t, #"S21.G00.40.002-Statut du salarié (conventionnel)" = _t, #"S21.G00.40.003-Code statut catégoriel Retraite Complémentaire obligatoire" = _t, #"S21.G00.40.004-Code profession et catégorie socioprofessionnelle (PCS-ESE)" = _t, #"S21.G00.40.006-Libellé de l'emploi" = _t, #"S21.G00.40.007-Nature du contrat" = _t, #"S21.G00.40.008-Dispositif de politique publique et conventionnel" = _t, #"S21.G00.40.009-Numéro du contrat" = _t, #"S21.G00.40.011-Unité de mesure de la quotité de travail" = _t, #"S21.G00.40.012-Quotité de travail de référence de l'entreprise pour la catégorie de salarié" = _t, #"S21.G00.40.013-Quotité de travail du contrat" = _t, #"S21.G00.40.014-Modalité d'exercice du temps de travail" = _t, #"S21.G00.40.016-Complément de base au régime obligatoire" = _t, #"S21.G00.40.017-Code convention collective applicable" = _t, #"S21.G00.40.018-Code régime de base risque maladie" = _t, #"S21.G00.40.019-Identifiant du lieu de travail" = _t, #"S21.G00.40.020-Code régime de base risque vieillesse" = _t, #"S21.G00.40.024-Travailleur à l'étranger au sens du code de la Sécurité Sociale" = _t]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Mois_Dsn", Int64.Type}, {"nss", Int64.Type}, {"Num_ctratPersonnalisé", Int64.Type}, {"S21.G00.50.001-Date de versement-", Int64.Type}, {"S21.G00.50.002-Rémunération nette fiscale-", type number}, {"S21.G00.50.003-Numéro de versement-", Int64.Type}, {"S21.G00.50.004-Montant net versé-", type number}, {"S21.G00.50.006-Taux de prélèvement à la source-", type number}, {"S21.G00.50.007-Type du taux de prélèvement à la source-", Int64.Type}, {"S21.G00.50.008-Identifiant du taux de prélèvement à la source-", Int64.Type}, {"S21.G00.50.009-Montant de prélèvement à la source -", type number}, {"S21.G00.51.011-Type-001-Rémunération brute non plafonnée", type number}, {"S21.G00.51.011-Type-002-Salaire brut soumis à contributions d'Assurancc chômage", type number}, {"S21.G00.51.011-Type-003-Salaire rétabli - reconstitué", type number}, {"S21.G00.51.011-Type-010-Salaire de base", type number}, {"S21.G00.52.001-Type-027-Prime liée à l'activité avec période de rattachement spécifique 028 - Prime non liée à l'activité", Int64.Type}, {"S21.G00.52.001-Type-028-", Int64.Type}, {"S21.G00.52.001-Type-011-", Int64.Type}, {"S21.G00.52.001-Type-020-", type number}, {"S21.G00.50.005--", type number}, {"S21.G00.52.001-Type-043-", Int64.Type}, {"S21.G00.52.001-Type-006-Indemnité conventionnelle de départ à la retraite du salarié 007 - Indemnité légale de licenciement", Int64.Type}, {"S21.G00.30.002_Nom de famille", type text}, {"S21.G00.30.003_Nom d'usage", type text}, {"S21.G00.30.004_Prénoms", type text}, {"S21.G00.30.005_Sexe", Int64.Type}, {"S21.G00.30.006_Date de naissance", Int64.Type}, {"S21.G00.30.007_Lieu de naissance", type text}, {"S21.G00.30.008_Numéro, extension, nature et libellé de la voie", type text}, {"S21.G00.30.009_Code postal", Int64.Type}, {"S21.G00.30.010_Localité", type text}, {"S21.G00.30.013_Codification UE", Int64.Type}, {"S21.G00.30.014_Code département de naissance", Int64.Type}, {"S21.G00.30.015_Code pays de naissance", type text}, {"S21.G00.30.016_Complément de la localisation de la construction", type text}, {"S21.G00.30.019_Matricule de l'individu dans l'entreprise", Int64.Type}, {"S21.G00.40.001-Date de début du contrat", Int64.Type}, {"S21.G00.40.002-Statut du salarié (conventionnel)", Int64.Type}, {"S21.G00.40.003-Code statut catégoriel Retraite Complémentaire obligatoire", Int64.Type}, {"S21.G00.40.004-Code profession et catégorie socioprofessionnelle (PCS-ESE)", type text}, {"S21.G00.40.006-Libellé de l'emploi", type text}, {"S21.G00.40.007-Nature du contrat", Int64.Type}, {"S21.G00.40.008-Dispositif de politique publique et conventionnel", Int64.Type}, {"S21.G00.40.009-Numéro du contrat", Int64.Type}, {"S21.G00.40.011-Unité de mesure de la quotité de travail", Int64.Type}, {"S21.G00.40.012-Quotité de travail de référence de l'entreprise pour la catégorie de salarié", type number}, {"S21.G00.40.013-Quotité de travail du contrat", type number}, {"S21.G00.40.014-Modalité d'exercice du temps de travail", Int64.Type}, {"S21.G00.40.016-Complément de base au régime obligatoire", Int64.Type}, {"S21.G00.40.017-Code convention collective applicable", Int64.Type}, {"S21.G00.40.018-Code régime de base risque maladie", Int64.Type}, {"S21.G00.40.019-Identifiant du lieu de travail", Int64.Type}, {"S21.G00.40.020-Code régime de base risque vieillesse", Int64.Type}, {"S21.G00.40.024-Travailleur à l'étranger au sens du code de la Sécurité Sociale", Int64.Type}}),


//selectionne les colonnes date ou periode
Select_Col_Dates= List.Sort(
    List.Select(
      Table.ColumnNames(#"Type modifié"),
      each Text.Contains(_, "Date")
        or Text.Contains(_, "Période")
    ),
    Order.Ascending
  ),

inputList_ = List.Zip({Select_Col_Dates, List.Repeat({ddmmaaaa}, List.Count(Select_Col_Dates))}),
inputListTypes_ = List.Zip({Select_Col_Dates, List.Repeat({type date}, List.Count(Select_Col_Dates))}),
Transform_Col_date = Table.TransformColumns(#"Type modifié", inputList_),
#"Change Type of transformed columns" = Table.TransformColumnTypes(Transform_Col_date, inputListTypes_)
  
in
 #"Change Type of transformed columns"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[PhilippeMuniesa]

Sorry, but that don' work

there is an error with ddmmaaaa funtion

ddmmaaaa does not correspond to any import

link 

https://www.dropbox.com/t/prosV7zBj5TRrhMX

Attached is the link of the corresponding excel file

I don't think I made a mistake in copying the request

It seems that the function ddmmaaaaa is not recognized

Do you have a solution or another idea?

Thanks a lot.

Philippe

[AlB]

If you have the list of column names you can build the  list of lists that you need as second argument for  Table.TransformColumns as shown above with for instance List.Zip

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

@PhilippeMuniesa 

Have you tried  Table.TransformColumns(table as table, transformOperations as list)?

Something like:

Table.TransformColumns(table as table, {{ColumnName1, yourFunction}, {ColumnName2, yourFunction}, {ColumnName3, yourFunction})

 Share a sample of your tables if this does not work, or ideally the pbix with the queries

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[PhilippeMuniesa]

Hello AIB,

in addition to my previous message, attached dropbox download link of an excel file containing an extract of the table, the function and the query (which does not work)

https://www.dropbox.com/t/QQtfGzbJAZ4Is0ar

Thanks a lot for your help,

Philippe

[PhilippeMuniesa]

thank you, but as I do not know in advance the number and the name of the columns to be transformed, I simply know that the column headers will contain the word date or the word period.

thats why i create a list of column names containing date or period, then i would like to apply the function to this list of column.

ps i work with powerquery in excel.I think it is possible to apply a function to a list of column names, but I stumble on the syntax

thank for your help

philippe