Reproduire la fonction NBVAL d’Excel dans Power Query

Publié le 31 mars 2020
par Sophie Marchand M.Sc., CPA, CGA, MVP
Dépivoter les colonnes

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.

Power Query NbVal

 

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).

Power Query

 

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.

Dépivoter les colonnes

 

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

Lignes groupées NBVAL

 

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

Résultat intermédiaire Power Query
 
 

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:

Tables fusion Power Query

 

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

Fusion Power Query

 

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

Calculs développés

 

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

Valeur remplacée

 

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 :

Bertrand Fortin
Écrit il y a 1 an
Simplement un gros WOW.

Je suis totalement satisfait de la formation. Je ne pouvais demandé mieux.

Stephanie Lambert
Écrit il y a 2 ans
Enfin du contenu que je ne connaissais presque pas

J'ai enfin pu avoir l'expérience d'un nouvel apprenant et comme je m'y attendais, elle fut très positive. Les notions sont bien expliquées et illustrées avec des exemples concrets. Il est très utile d'avoir les mêmes tables de données pour pouvoir reproduire les exemples. Les documents pdf fournis sont clairs et bien faits.

Daniel Harvey
Écrit il y a 2 ans
J'ai adoré.

Comme introduction a l'outil, pour avoir un aperçu de ce qu'il est possible de faire, je pense que la formation est vraiment géniale.


CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

3 réflexions sur “Reproduire la fonction NBVAL d’Excel dans Power Query”

  1. Bonjour Sophie
    Une petite remarque pour ceux qu’un peu de code ne rebute pas et qui n’ont pas trop de champs

    NBVAL = List.Count(List.RemoveNulls({[Evol1],[Evol2],[Evol3]}))

    Bravo pour votre blog qui m’apprends toujours beaucoup
    Stéphane

    1. Bonjour Stéphane,

      Merci pour vos commentaires toujours pertinents et pour votre apport sur le forum, qui est tant apprécié! Pour ce qui est de votre solution en M, ici, c’est en effet une très bonne formule, sauf dans le cas où une colonne Evol4 était introduite.

      Vous me dites quand vous êtes prêt à travailler pour Le CFO masqué 😉

      Au plaisir,

      Sophie

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top