POWERQUERY :: Récupérer le contenu d'un champ nommé (une cellule) ou le contenu d'une plage (range de plusieurs cellules)

[oguruma]

Bonjour, j'avais déjà posté un billet concernant la récupération de paramètres via une table de paramètres dédiée. Il est aussi parfois intéressant de récupérer le contenu d'un champ nommé ne comportant qu'une seule cellule ou de récupérer une valeur d'une plage nommée en spécifiant la ligne et la colonne de la plage.

Pour cela je vous propose 3 fonctions.

Les exemple de champs et plages nommés :

La 1ère fonction : fnGetFieldNameValue

let fnGetFieldNameValue = (pRangeName as text) =>
        let
            FilePath = Excel.CurrentWorkbook(){[Name=pRangeName]}[Content]{0}[Column1]          
        in
            FilePath
in
    fnGetFieldNameValue

Utilisation

let
    //Excel.CurrentWorkbook(){[Name="FILE_NAME"]}[Content]{0}[Column1]
    FilePath = fnGetFieldNameValue("FILE_NAME")  
in
    FilePath

La 2ème fonction : fnGetFieldNameValue

let fnGetRangeValue = (
            pRangeName as text,
            optional pRow as number,
            optional pColumn as number
        ) =>
       
        let
            //***********************************************************************************************************
            // Gestion des paramètres
            //***********************************************************************************************************
            RangeName=pRangeName,
            Row=if pRow is null or pRow=0 then 0 else pRow - 1,  
            Column=if pColumn is null or pColumn = 0 then 1 else pColumn,
            WbExcel=Excel.CurrentWorkbook(),

            //***********************************************************************************************************
            // Recherche de la valeur selon la ligne et la colonne
            // Construction dynamique des argumenents de recherche
            //***********************************************************************************************************
            StrRange="WbExcel{[Name=RangeName]}[Content]{Row}[Column" & Text.From(Column) & "]",
            RecEval=[WbExcel=WbExcel, Row=Row, Column=Column, RangeName=RangeName],
            EvalStrRange=Expression.Evaluate(StrRange,RecEval)  
        in
            EvalStrRange
in
    fnGetRangeValue

Exemple :

let
    V=fnGetRangeValue("RANGE_PARAMS")
in
    V
   
let
    V=fnGetRangeValue("FILE_NAME")
in
    V
   
let
    V=fnGetRangeValue("RANGE_PARAMS",2,1)
in
    V
   
let
    V=fnGetRangeValue("RANGE_PARAMS",2,2)
in
    V
   
let

    V=fnGetRangeValue("RANGE_PARAMS",3,2)
in
    V

La 3ème fonction est une variante de la 2ème, on contrôle la présence du champ nommé

let fnGetRangeValue = (
            pRangeName as text,
            optional pRow as number,
            optional pColumn as number
        ) =>
       
        let
            //***********************************************************************************************************
            // Gestion des paramètres
            //***********************************************************************************************************
            RangeName=pRangeName,
            Row=if pRow is null or pRow = 0 then 0 else pRow - 1,  
            Column=if pColumn is null or pColumn = 0 then 1 else pColumn,
            WbExcel=Excel.CurrentWorkbook(),

            //***********************************************************************************************************
            // Construction de la liste des objets connus dans Excel
            //***********************************************************************************************************
            TbList=WbExcel[Name],
            bFound=List.Contains(TbList,RangeName),

            //***********************************************************************************************************
            // La recherche est uniquement effectuée si le champ est trouvé dans la liste des objets connus dans Excel
            //***********************************************************************************************************
            Value=if bFound
                      then
                            let
                              //--------------------------------------------------------
                              // Traitements de recherches si le nom de champ est connu
                              //--------------------------------------------------------
                              StrRange="WbExcel{[Name=RangeName]}[Content]{Row}[Column" & Text.From(Column) & "]",
                              RecEval=[WbExcel=WbExcel, Row=Row, Column=Column, RangeName=RangeName],
                              Eval=Expression.Evaluate(StrRange,RecEval)  
                            in
                              Eval
                      else
                            "#N/A"
   
        in
            Value
in
    fnGetRangeValue

L'astuce se trouve en fait dans ce bout de code

 Value=if bFound
                      then
                            let
                              //--------------------------------------------------------
                              // Traitements de recherches si le nom de champ est connu
                              //--------------------------------------------------------
                              StrRange="WbExcel{[Name=RangeName]}[Content]{Row}[Column" & Text.From(Column) & "]",
                              RecEval=[WbExcel=WbExcel, Row=Row, Column=Column, RangeName=RangeName],
                              Eval=Expression.Evaluate(StrRange,RecEval)   
                            in
                              Eval
                      else
                            "#N/A"