Power Query: Voici un exemple de transposition de données utile

Récemment, un client m’a soumis une problématique de transposition de données intéressante, que j’ai résolue avec Power Query. Cet article vise à expliquer la démarche que j’ai utilisée pour transposer des données dans Power Query, qui n’étaient pas dans un format idéal au départ.

Données à transposer dans Power Query

Le client possède un système qui enregistre les coordonnées de ses clients à lui. Ce système exporte les données dans le format ci-dessous.

Afin de pouvoir analyser ses données et de les croiser avec d’autres sources de données, mon client souhaitait accéder à ces données sous forme de base de données régulière, i.e. une colonne pour les noms, une colonne pour les adresses, une colonne pour les villes, etc.

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.

Requête de transformation pour transposer les données dans Power Query

Afin de transformer les données de mon client dans le format recherché, j’ai effectué 7 grandes étapes de transformations.

Étape 1 : Importation des données

J’ai importé les données dans Power Query et j’ai nommé cette requête TableOrigine.

Étape 2: Création d’une requête Max

J’ai fait une référence à la requête TableOrigine, afin de créer la requête Max.

Dans ma requête Max, j’ai effectué un regroupement (Group By ou Regroupement par) afin d’obtenir la valeur maximum de ma colonne 1.

Cette valeur maximum correspond au nombre d’enregistrements différents dans mes données sources, dans ce cas-ci, 3. En effet, j’ai un premier enregistrement qui désigne les titres de colonnes, j’ai un deuxième enregistrement pour Paul Lebeau et j’ai un troisième enregistrement pour Lucie Lavigne.

Afin d’extraire la valeur de ce maximum, j’ai cliqué avec le bouton droit de ma souris sur le 3 et j’ai choisi l’option Drill Down (Forer).

Étape 3: Création d’une liste

J’ai créé une liste qui répète les chiffres 1 à 8, 3 fois. Notez que le 3 est déterminé par ma requête Max, qui évoluera selon le nombre d’enregistrements sous-jacents.

J’ai ensuite converti cette liste en table.

Et j’ai ajouté une colonne Index, à partir de 1.

J’ai donc obtenu le résultat suivant, que j’ai nommé Liste. L’idée est de jumeler cette liste avec ma table d’origine afin de me servir des chiffres de 1 à 8 pour identifier les lignes qui correspondent à des noms, des adresses, des villes, etc.

Étape 4: Fusion de requêtes

J’ai à nouveau fait une référence à ma table d’origine et  j’ai inséré un colonne d’index à partir de 1. J’ai renommé cette requête PowerQuery.

Ceci m’a permis de fusionner cette requête avec la requête Liste.

J’ai donc obtenu le résultat suivant.

Étape 5: Désengorgement de la requête fusionnée

J’ai supprimé les 8 premières lignes, qui contenaient les titres de colonnes et j’ai supprimé ma colonne d’index.

Étape 6: Ajout de colonnes conditionnelles

J’ai ajouté des colonnes conditionnelles, pour aller récupérer les valeurs des cellules de noms, d’adresses, de villes, etc.

J’ai donc obtenu le résultat suivant.

Étape 7: Remplissage vers le haut et filtre

J’ai effectué un remplissage vers le haut sur ces colonnes (Fill up).

Ce faisant, j’ai pu obtenir l’ensemble des données recherchées sur chacune des lignes dont la valeur de la colonne Column 1.1 était égale à 1.

J’ai donc fait un filtre sur cette colonne, pour ne conserver que les valeurs égales à 1.

Voyez le tout en action dans ce tutoriel

Abonnez-vous à notre chaîne YouTube !

Conclusion sur cette approche pour transposer des données dans Power Query

Il existe sans aucun doute une panoplie de solutions alternatives. Si vous avez d’autres suggestions, n’hésitez pas à les partager dans la zone de commentaires, ci-dessous.

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

Afin d’explorer les principales fonctions et fonctionnalités de Power Query, qui permet d’importer, de transformer et de fusionner des données de diverses sources et de pouvoir les analyser efficacement, suivez la formation Excel – Introduction à Power Query et au langage M.

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