POWERQUERY :: Excel - RechercheX ou XLookup simplifié

[oguruma]

Dans ce post une forme simplifiée du RechercheX (XLookup) sous Excel.
Deux fonctions qui simulent la combinaison des fonction EQUIV+INDEX en passant soit par une table soit par les listes

Environnement de travail

fnTableXLookup

let
    //----------------------------------------------------------------------------------------
    // Cette fonction simule de manière simplifiée la fonction RechercheX sous Excel
    // pTable  : table des données
    // pField1 : colonne de recherche
    // pKey    : clef de recherche
    // pField2 : colonne résultat
    //----------------------------------------------------------------------------------------
    
    fnIndexEquiv = (pTable as any, pField1 as any, pKey as any, pField2 as any) as any =>

    let
        ParamSource = if pTable is text then
                         Excel.CurrentWorkbook(){[Name=pTable]}[Content]
                         else pTable,

        //------------------------------------------------------------------------------------
        // Construction de la chaine de recherche
        //------------------------------------------------------------------------------------
        ToString=if pKey is text then
                    "each (["  & pField1 & "] = """ & pKey & """)"
                    else if pKey is number then
                        "each (["  & pField1 & "] = " & Number.ToText(pKey) & ")"
                        else null,

        //------------------------------------------------------------------------------------
        // Evaluation PowerQuery de la chaine de recherche
        //------------------------------------------------------------------------------------
        ToEval=Expression.Evaluate(ToString),

        //------------------------------------------------------------------------------------
        // Recherche dans la table
        //------------------------------------------------------------------------------------
        ParamRow = Table.SelectRows(ParamSource, ToEval),

        //------------------------------------------------------------------------------------
        // On sécurise le retour si la clef n'est pas trouvée
        //------------------------------------------------------------------------------------
        Value=
            if Table.IsEmpty(ParamRow)=true
                then null
                else Record.Field(ParamRow{0},pField2)

    in
        try Value otherwise null

in
    fnIndexEquiv

fnListXLookup

let

    //----------------------------------------------------------------------------------------
    // Cette fonction simule de manière simplifiée la fonction RechercheX sous Excel
    // pTable  : table des données
    // pField1 : colonne de recherche
    // pKey    : clef de recherche
    // pField2 : colonne résultat
    //----------------------------------------------------------------------------------------

    //----------------------------------------------------------------------------------------
    // MAIS là on passe par les listes
    //----------------------------------------------------------------------------------------

    fnListXLookup = (pTable as any, pField1 as any, pKey as any, pField2 as any) =>

    let
        //---------------------------------------------------------------------------------
        // Exemple d'importation d'un fichier .csv non dynamique
        //---------------------------------------------------------------------------------
        Source = if pTable is text then
                    Excel.CurrentWorkbook(){[Name=pTable]}[Content]
                    else pTable,

        //----------------------------------------------------------------------------------
        // Construction dynamique de la colonne de recheche pour obtenir la liste
        //----------------------------------------------------------------------------------
        Field1 = pField1,
        List1 = Expression.Evaluate("Source[" & Field1 & "]",[Source=Source]),

        //----------------------------------------------------------------------------------
        // Construction dynamique de la colonne résultat pour obtenir la liste
        //----------------------------------------------------------------------------------
        Field2 = pField2,
        List2 = Expression.Evaluate("Source[" & Field2 & "]",[Source=Source]),

        //----------------------------------------------------------------------------------
        // On recherche la position de la clef
        //----------------------------------------------------------------------------------
        Key=pKey,
        Position=List.PositionOf(List1,Key),

        //----------------------------------------------------------------------------------
        // On renvoie la ligne correspondante de la liste résultat
        //----------------------------------------------------------------------------------
        Return=if Position <0 then null else List2{Position}
        
    in
        Return
in
    fnListXLookup

Utilisation des fonctions
Pour plus d'exemples voir le fichier joint