Reproduire la fonction NBVAL d’Excel dans Power Query

Récemment, une lectrice a demandé quelle était la fonction équivalent à la fonction NBVAL d’Excel dans Power Query. Cet article vise à répondre à cette question, avec une solution très simple à comprendre et à mettre en place pour tout niveau d’usager d’Excel. Aucun besoin de recourir à du langage M avancé ici.

La fonction NBVAL dans Power Query

Dans son exemple, la personne soumettait les données suivantes. Dans la colonne E, on retrouvait la fonction =NBVAL(B2:D2) pour la ligne 2 et ainsi de suite pour les lignes suivantes. Autrement dit, on se trouvait à calculer le nombre de valeurs Evol par PN.

Repenser sa façon de travailler… de cellules à base de données

Quand un usager d’Excel commence à travailler avec Power Query, il a souvent du mal à faire le passage du concept de cellules à bases de données. Qui plus est, il ignore souvent les principes de normalisation qu’il faut impérativement appliquer aux bases de données pour créer des transformations efficaces. Dans notre exemple, plus précisément, voici ce à quoi ressemble les données, une fois importées dans Power Query (à noter que les cellules vides d’Excel produisent des valeurs null).

La première chose à faire, selon les principes de normalisation, c’est de dépivoter les colonnes Evol1, Evol2, Evol3. En effet, on veut éviter de dupliquer une même dimension en plusieurs colonnes. Donc ici, on se retrouve avec des PN répétés sur plusieurs lignes.

Et pour savoir combien de valeurs possèdent chaque PN, on peut simplement utiliser une fonctionnalité de regroupement comme la suivante:

Ceci nous permet d’obtenir le résultat souhaité:

Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation  des données avec nos formations sur Power Query et Power Pivot dans Excel.

Solution alternative

Mais si on souhaite aussi voir les résultats pour A par exemple, avec la valeur de 0, et bien dans ce cas, il suffirait de fusionner côte-à-côte notre table de départ avec celle créée aux étapes précédentes, en se basant sur la colonne PN:

Ceci nous permettrait d’obtenir une table par PN (voir colonne Calculs).

Et on pourrait alors y extraire notre calcul représentant le nombre de valeurs par PN.

Ne resterait plus qu’à remplacer les null par des 0.

Réflexion finale

En conclusion, il n’existe pas vraiment de fonction NBVAL dans Power Query. C’est notre façon d’interpréter et de manipuler l’information de cellules à bases de données qui doit changer quand on passe d’Excel à Power Query.

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

J’ai déjà rédigé un article qui stipulait que si vous n’aviez qu’une seule formation à suivre, vous devriez choisir de vous former en Power Query (Article: Si vous deviez ne suivre qu’une formation cette année, ce serait celle-là!) car les retombées, en terme d’économies de temps sont simplement spectaculaires. Et vous le savez déjà, le temps, c’est de l’argent. Alors, c’est le temps d’automatiser vos fichiers Excel.

Voici quelques commentaires d’apprenants ayant suivi cette formation :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.