POWERQUERY :: Equivalent des fonctions Excel INDEX et EQUIV

[oguruma]

Ca faisait un petit moment que c'était en "gestation", c'est-à-dire produire l'équivalent des fonctions INDEX et EQUIV Excel en fonctions POWERQUERY.

Elles fonctionnent de la même manière.

En ce qui concerne le passage de la colonne en paramètre et quelque-soit INDEX ou EQUIV on peut soit la passer par son numéro soit par le nom de la colonne.

Ainsi en combinant ces deux fonction fnIndexExcel+fnEquivExcel on retrouve l'équivalent sous Excel avant d'avoir le RechercheX.
On peut donc retrouver n'importe quelle valeur à partir de n'importe quelle colonne soit par son N° soit par son nom.
Je ne me suis pas attardé sur les recherches par date.
Ainsi une autre manière de gérer une table de paramètres comportant plusieurs colonnes.

A tester.... voir si cela remplace avantageusement les JOIN sous PowerQuery.
Pour des combinaisons très simples de recherches de valeurs comme un RechercheV ou un RechercheX peut-être que ces deux fonctions sous PowerQuery peuvent faire "le TAFF" comme si on le ferait sous Excel.

Une nouvelle fois le code de ces fonctions pourrait permettre aux débutants en powerquery de "mettre le pied à l'étrier".

Le code de ces fonctions montre par exemple comment on peut imbriquer plusieurs let. Considérez en fait qu'un let ... in représente un bloc d'instructions que l'on peut placer dans un if.... then.... else (un peu comme en VBA). Et à la fin de ce bloc on récupère le résultat dans l'étape in.....
Il est vrai que ce concept est loin d'être simple à comprendre quand on débute en M

A tester sur une forte volumétrie entre plusieurs tables.

Exemple sur une table de paramètres comme suit

Environnement PowerQuery

Fonction fnIndexExcel

let
    //----------------------------------------------------------------------------------------
    // Simulatiion de la fonction INDEX (Excel)
    //----------------------------------------------------------------------------------------
    fnIndex =(pTable as any, pRow as number, pColumn as number) as any =>
  
    let
        // On calcule le Nbr de records
        NbRec=Table.RowCount(pTable),

        // Sécurisation
        Rec = if pRow > NbRec
                 or pRow < 0
                 or pColumn < 0 then null else pTable{pRow},

        // On calcule le Nbr de colonnes dans l'enregistrement trouvé
        NbFields=Record.FieldCount(Rec),

        // Retourne la valeur à la position ligne, colonne
        RecReturnValue=if pColumn > NbFields then
                          null
                          else Record.SelectFields(Rec,Record.FieldNames(Rec){pColumn})
    in
        Record.ToTable(RecReturnValue)[Value]{0}
in
    fnIndex

Fonction fnEquivExcel

let

    //----------------------------------------------------------------------------------------
    // Simulatiion de la fonction INDEX (Excel)
    //----------------------------------------------------------------------------------------

    fnEquivExcel =(pTable as any, pCol as number, pKey as any) as any =>
  
    let

        //-------------------------------------------------------------------------------------
        // On récupère les noms de colonnes de la table
        //-------------------------------------------------------------------------------------
        ListColumns=Table.ColumnNames(pTable),
      
        //-------------------------------------------------------------------------------------
        // Sécurisation si la colonne est < 0
        //-------------------------------------------------------------------------------------
        TargetColumn=if pCol < 0 then
                        null
                        else ListColumns{pCol},

        //-------------------------------------------------------------------------------------
        // On récupère le contenu de la colonne
        //-------------------------------------------------------------------------------------
        ListValues=Table.Column(pTable,TargetColumn),

        //-------------------------------------------------------------------------------------
        // On recherche la clef pour récupèrer la ligne où elle se trouve
        //-------------------------------------------------------------------------------------
        Position=List.PositionOf(ListValues,pKey)
          
    in
        //-------------------------------------------------------------------------------------
        // Sécuritsation si la valeur n'est pas trouvée
        //-------------------------------------------------------------------------------------
        if Position < 0 then null else Position
in
    fnEquivExcel

Fonction fnIndexExcel_2
Cette fonction accepte soit le n° de colonne soit son nom

let
    //----------------------------------------------------------------------------------------
    // Simulatiion de la fonction INDEX (Excel)
    //----------------------------------------------------------------------------------------
    fnIndex =(pTable as any, pRow as number, ppColumn as any) as any =>
  
    let
        // On calcule le Nbr de records
        NbRec=Table.RowCount(pTable),

        //-------------------------------------------------------------------------------------
        // On récupère les noms de colonnes de la table
        //-------------------------------------------------------------------------------------
        ListColumns=Table.ColumnNames(pTable),

        pColumn = if ppColumn is number then
                  ppColumn else let
                                PosCol=if ppColumn is text then
                                          List.PositionOf(ListColumns,ppColumn)
                                          else null
                             in
                                PosCol,

        // Sécurisation
        Rec = if pRow > NbRec
                 or pRow < 0
                 or pColumn < 0 then null else pTable{pRow},

        // On calcule le Nbr de colonnes dans l'enregistrement trouvé
        NbFields=Record.FieldCount(Rec),

        // Retourne la valeur à la position ligne, colonne
        RecReturnValue=if pColumn > NbFields then
                          null
                          else Record.SelectFields(Rec,Record.FieldNames(Rec){pColumn})
    in
        Record.ToTable(RecReturnValue)[Value]{0}
in
    fnIndex

Fonction fnEquivExcel_V2

Idem on peut soit passer le n° de colonne soit le nom

let

    //----------------------------------------------------------------------------------------
    // Simulatiion de la fonction INDEX (Excel)
    //----------------------------------------------------------------------------------------

    fnEquivExcel =(pTable as any, ppCol as any, pKey as any) as any =>
  
    let

        //-------------------------------------------------------------------------------------
        // On récupère les noms de colonnes de la table
        //-------------------------------------------------------------------------------------
        ListColumns=Table.ColumnNames(pTable),
      
        //-------------------------------------------------------------------------------------
        // Sécurisation si la colonne est < 0
        //-------------------------------------------------------------------------------------
        pCol = if ppCol is number then
                  ppCol else let
                                PosCol=if ppCol is text then
                                          List.PositionOf(ListColumns,ppCol)
                                          else null
                             in
                                PosCol,                

        TargetColumn=if pCol < 0 then
                        null
                        else ListColumns{pCol},

        //-------------------------------------------------------------------------------------
        // On récupère le contenu de la colonne
        //-------------------------------------------------------------------------------------
        ListValues=Table.Column(pTable,TargetColumn),

        //-------------------------------------------------------------------------------------
        // On recherche la clef pour récupèrer la ligne où elle se trouve
        //-------------------------------------------------------------------------------------
        Position=List.PositionOf(ListValues,pKey)
          
    in
        //-------------------------------------------------------------------------------------
        // Sécuritsation si la valeur n'est pas trouvée
        //-------------------------------------------------------------------------------------
        if Position < 0 then null else Position
in
    fnEquivExcel

Utilisation des fonctions

let
    Source = fnEquivExcel(TB_PARAMS,1,"VALEUR_1")
in
    Source

let
    Source = fnIndexExcel(TB_PARAMS,0,0)
in
    Source

let
    Source = fnIndexExcel(TB_PARAMS,fnEquivExcel(TB_PARAMS,1,"VALEUR_1"),1)
in
    Source

let
    Source = fnIndexExcel(TB_PARAMS,fnEquivExcel(TB_PARAMS,1,"VALEUR_1"),2)
in
    Source

let
    Source = fnIndexExcel(TB_PARAMS,fnEquivExcel(TB_PARAMS,1,"VALEUR_10"),0)
in
    Source

let
    Source = fnEquivExcel_V2(TB_PARAMS,"VALEUR","VALEUR_1")
in
    Source

let
    Source = fnIndexExcel_2(TB_PARAMS,0,"VALEUR")
in
    Source