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

Publié le 19 novembre 2015
par Sophie Marchand M.Sc., CPA, CGA, MVP
PowerQueryStock

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.

PowerQueryExcel2016

 

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.

PowerQueryPowerBIDesktop

 

Transformation d’un fichier .prn avec Power Query

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

transformer un fichier .prn

 

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

transformer un fichier .prn

 

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

transformer un fichier .prn

 

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

PowerBIDesktopPowerQuery_3

 

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.

PowerBIDesktopPowerQuery_4

 

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.

PowerBIDesktopPowerQuery_5

 

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

PowerBIDesktopPowerQuery_6

 

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.

PowerBIDesktopPowerQuery_7

 

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

PowerBIDesktopPowerQuery_8

 

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.

PowerBIDesktopPowerQuery_9

 

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

PowerBIDesktopPowerQuery_10

 

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

PowerBIDesktopPowerQuery_11

 

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.

PowerBIDesktopPowerQuery_12

 

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

PowerBIDesktopPowerQuery_13

 

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.

PowerBIDesktopPowerQuery_14

 

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.

PowerBIDesktopPowerQuery_15

 

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.

PowerBIDesktopPowerQuery_16

 

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.

PowerBIDesktopPowerQuery_17

 

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.

PowerBIDesktopPowerQuery_18

 

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

PowerBIDesktopPowerQuery_19

 

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

PowerBIDesktopPowerQuery_20

 

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.

PowerQueryStock2

 

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.

PowerBIDesktopPowerQuery_21

 

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.

PowerBIDesktopPowerQuery_22

 

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

PowerBIDesktopPowerQuery_23

 

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 :

Le CFO masqué - Commentaires d'apprenants ayant suivi la formation : Power BI - Niveau 3

CFO-Masque_Formations-en-ligne_FBLa 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é.

1 réflexion sur “Power Query: C’est plus que du bonbon! (Démo 1)”

Laisser un commentaire

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

Scroll to Top