POWERQUERY :: Typer les colonnes via une Table Excel de manière dynamique :: fnChangeColumnsTypes

[oguruma]

Ce post permet de définir les types de colonnes d'une requête PowerQuery à partir d'une table Excel.

Exemple table source à importer dans Pwq

Table de transformation des types de colonnes

Version simplifiée du code

let
    Source= Excel.CurrentWorkbook(){[Name="TblType"]}[Content],
    LstCol=Table.ColumnNames(Source),  
    ChangeColTypes = Table.TransformColumnTypes(Source,{{LstCol{0}, type text}, {LstCol{1}, type text}}),  
    //ToType = Table.TransformColumns(ChangeColTypes, {{LstCol{1}, each Expression.Evaluate(_, [Currency.Type=Currency.Type, Int64.Type=Int64.Type, Percentage.Type=Percentage.Type]) }}),  
    ToType = Table.TransformColumns(ChangeColTypes,{{LstCol{1}, each Expression.Evaluate(_, [Currency.Type=Currency.Type, Int64.Type=Int64.Type, Percentage.Type=Percentage.Type])}}),
    ToField = Table.AddColumn(ToType, "Custom", each Record.FieldValues(_)),
    RemoveOtherCols = Table.SelectColumns(ToField,{"Custom"}),
    ToList = RemoveOtherCols[Custom]
in
    ToList

Résultat sous forme de liste

Table type

let
    Source = Excel.CurrentWorkbook(){[Name="TblType"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"COLONNE", type text}, {"TYPE", type text}})
in
    #"Type modifié"

La version simplifiée sous forme d'une fonction - tout est expliqué dans les commentaires

let
    //----------------------------------------------------------------------------------------------
    // La fonction attend la table des types pour chaque colonne
    // La table passée en paramètre peut être soit de type text soit de type table
    //----------------------------------------------------------------------------------------------

    fnTransformTypes=(pTable as any) as list =>
    let
        //------------------------------------------------------------------------------------------
        // On évalue le type de paramètre table
        //------------------------------------------------------------------------------------------
        Source= if pTable is text
                then Excel.CurrentWorkbook(){[Name=pTable]}[Content]
                else if pTable is table then pTable else null,
               
        //-----------------------------------------------------------------------------------------------
        // La table des types doit comporter deux colonnes
        // colonne 1 : nom de la colonne à transformer
        // colonne 2 : type de la colonne
        // les noms des colonnes ne sont pas imposés puisque la fonction les détecte à la ligne suivante
        //------------------------------------------------------------------------------------------------
        LstCol=Table.ColumnNames(Source),  

        //------------------------------------------------------------------------------------------
        // Les noms des colonnes sont récupérés dans la variable de liste LstCol
        // LstCol{0} = colonne 1 - nom des colonnes
        // LstCol{1} = colonne 2 - types de colonnes
        //------------------------------------------------------------------------------------------
        ChangeColTypes = Table.TransformColumnTypes(Source,{{LstCol{0}, type text}, {LstCol{1}, type text}}),          

        //------------------------------------------------------------------------------------------
        // On prend aussi en compte les types non primitifs
        // [Currency.Type=Currency.Type, Int64.Type=Int64.Type, Percentage.Type=Percentage.Type]
        // Astuce importante :
        //     Il est nécessaire de les déclarer afin qu'ils soient reconnus dans le contexte PQ
        //------------------------------------------------------------------------------------------
        ToType = Table.TransformColumns(ChangeColTypes,{{LstCol{1}, each Expression.Evaluate(_, [Currency.Type=Currency.Type, Int64.Type=Int64.Type, Percentage.Type=Percentage.Type])}}),
        ToField = Table.AddColumn(ToType, "CustomTypes", each Record.FieldValues(_)),

        //------------------------------------------------------------------------------------------
        // Création de la liste des types à appliquer à chaque colonne
        //------------------------------------------------------------------------------------------
        RemoveOtherCols = Table.SelectColumns(ToField,{"CustomTypes"}),
        ToList = RemoveOtherCols[CustomTypes]
    in
        try ToList otherwise null
in
    try fnTransformTypes otherwise null

Appel de la fonction on passe la table de transformation sous forme de nom de table au format Table Powerquery

let
    Source = Excel.CurrentWorkbook(){[Name="tblSourceData"]}[Content],
    ChangeTypes = Table.TransformColumnTypes(Source,fnTransformTypes(TblType))  
in
    ChangeTypes

ou

Appel de la fonction on passe la table de transformation sous forme de nom de table au format texte

let
    Source = Excel.CurrentWorkbook(){[Name="tblSourceData"]}[Content],
    ChangeTypes = Table.TransformColumnTypes(Source,fnTransformTypes("TblType"))  
in
    ChangeTypes