Power Query: C’est plus que du bonbon! (Démo 1)

Au moment de la publication de cet article, Power Query est disponible sous forme d’add-in, en téléchargement libre, sur le site web de Microsoft, pour les versions d’Excel 2010 et 2013, il est inclus dans la version Excel 2016 (avec certaines limitations selon le #sku… renseignez-vous avant de choisir votre version d’Excel 2016) et il est inclus dans Power BI Desktop. Les captures d’écrans de cet article proviennent majoritairement de Power BI Desktop mais les étapes sont pratiquement identiques à celles qui aurait été franchies dans Excel. Lorsque ce n’est pas le cas, nous prenons le soin de souligner les différences. Voici donc comment transformer un fichier .prn avec Power Query.

Power Query via Excel 2016 ou Power BI Desktop

Power Query est inclus dans la version Excel 2016. Il est imbriqué dans le menu Data (Données). Pour effectuer une requête, vous devez sélectionner New Query (Nouvelle requête). Pour voir vos requêtes, vous devez sélectionner Show Queries (Afficher les requêtes). Pour modifier une requête, vous devez double cliquer sur le titre de celle-ci dans le panneau de requêtes.

Power Query est inclus dans Power BI Desktop, à travers le menu Home/External Data. Pour effectuer une requête, vous devez sélectionner Get Data. Pour modifier une requête, vous devez cliquer sur Edit Queries.

Transformation d’un fichier .prn avec Power Query

Notre démo portera sur les transformations du fichier .prn d’inventaire suivant:

Lorsque nous importons ce fichier dans Excel via Power Query, nous obtenons ceci:

Lorsque nous importons ce fichier dans Power BI Desktop, nous obtenons plutôt ceci:

Ainsi, dans le cas d’Excel, il faudra d’abord ajouter une étape qui consiste à retirer les espaces vides de part et d’autres des lignes de données, via la fonction Trim (Supprespace).

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.

Étapes de transformation du fichier .prn avec Power Query

Nous créons d’abord une colonne personnalisée, à l’aide du code M, afin de ne retenir que les colonnes de données qui débutent par Store: .

Nous utilisons ensuite la fonctionnalité Fill/Down (Remplir/Vers le bas), pour obtenir une colonne qui servira plus tard, à identifier le numéro du magasin et la ville de ce dernier.

Nous ajoutons ensuite une colonne personnalisée, à l’aide du code M, pour obtenir le type d’items sous inventaire, soit Regular, Special ou Dump.

Nous utilisons à nouveau la fonctionnalité Fill/Down (Remplissage/Vers le bas) afin de créer une colonne avec les catégories d’items.

Nous ajoutons à nouveau une colonne personnalisée, cette fois-ci, pour obtenir une colonne avec la période. Nous ne retenons donc que les colonnes qui débutent par For Fiscal Period.

À nouveau, nous utilisons la fonctionnalité Fill/Down (Remplissage/Vers le bas) pour compléter la colonne de dates.

Nous séparons ensuite la nouvelle colonne à l’aide du délimiteur :. Nous obtenons ainsi une colonne avec For Fiscal Period et une colonne avec 15/02.

Nous supprimons ensuite la colonne qui comprend les expressions For Fiscal Period.

Nous transformons ensuite le format de la colonne de dates pour obtenir 2015-02-15.

Nous dupliquons ensuite notre première colonne. Cette étape n’est pas nécessaire mais dans le doute, nous préférons conserver une version originale de nos données.

Nous séparons ensuite notre nouvelle colonne, à partir de l’espace qui se trouve le plus à gauche.

Nous convertissons ensuite en format de nombre la première des 2 colonnes résultantes. Ce faisant, tous les chiffres sont convertis et tous les textes renvoient des erreurs.

Comme nous ne voulons conserver que les numéros d’items (les chiffres), nous supprimons les erreurs. À noter que les lignes éqivalentes ne contiennent aucune donnée d’inventaire, donc nous ne perdons aucune information par le biais de cette transformation.

Notre dernière colonne (voir image ci-haut) comprend maintenant le nom des items et leur valeur d’inventaire. Toutefois un nom d’item peut comporter de 1 à 4 mots. Nous séparons donc cette colonne avec le dernier espace à droite de l’expression. Nous obtenons ainsi deux colonnes, la première avec les noms d’items de 1 à 4 mots et la deuxième avec les valeurs d’inventaire.

Nous supprimons ensuite les noms d’items car nous avons déjà les numéros d’items et nous pourrons les lier à une table de dimensions d’items, dans notre modèle de données.

Nous supprimons également la première colonne. Pourquoi l’avoir dupliquer avant? Pour être certain de ne rien manquer, tout simplement. De plus, si le fichier source venait à changer, il serait plus aisé de repérer les changements à effectuer dans la requête, en planifiant le tout de cette façon.

Nous avons ensuite séparé notre première colonne par les :.

Nous avons retiré la première des 2 colonnes résultantes:

Dans Excel, à cette étape-ci, nous obtenons un espace devant les numéros de magasins, Nous devons donc ajouter une étape dans Excel, soit un Trim (Supprespace) pour retirer cet espace.

Ensuite, nous pouvons séparer notre première colonne par un espace puisque tous les cellules de cette colonne comprennent 2 données, soit le numéro de magasin et la ville du magasin.

Nous pouvons ensuite supprimer la colonne avec les villes car nous pourrons plus tard créer une relation avec une table de dimension de magasins (comprenant les villes), dans notre modèle de données. On ne veut pas dupliquer cette information inutilement. Nous modifions également les noms de colonnes.

À cette étape-ci, nous pouvons observer des 0 dans la colonne DeptID. Nous devons supprimer les lignes qui contiennent des 0, qui ne comportent pas de valeurs d’inventaire.

Enfin, nous obtenons une table avec les numéros de magasins, les catégories d’items, les dates d’inventaire, les départements et les valeurs d’inventaires.

Formation complémentaire

Cette technique est enseignée dans notre formation Excel – Power BI (niveau 3).

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