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

Publié le 31 juillet 2017
par Sophie Marchand M.Sc., CPA, CGA, MVP
Power Query plusieurs colonnes conditionnelles

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.

 

Données de départ

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.

Données origines PQ Transposition

 

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

Power Query Reference

 

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.Power Query Group by Max

 

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

Power Query Drill Down

 

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

Power Query List.Repeat

 

J’ai ensuite converti cette liste en table.

Power Query List to Table

 

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

Power Query Ajout Index

 

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.

Power Query Liste

 

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

Power Query Table plus Index

 

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

Power Query Fusion Requêtes

 

J’ai donc obtenu le résultat suivant.

Power Query Transposition En Cours

 

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

Power Query plusieurs colonnes conditionnelles

 

Étape 7: Remplissage vers le haut et filtre

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

Power Query 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.

Power Query Filtrer sur 1

 

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

Power Query Résultat Final Transposition

 

Conclusion

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 :

Amélie Tremblay
Écrit il y a 4 mois
Une des rares formations où nous quittons avec une boîte à outils.

Très bonne formation applicable à ma réalité. Une des rares formations où nous quittons avec une boîte à outils.

Cécile BERNARD
Écrit il y a 5 mois
Je ne regrette absolument pas mon choix !

Excellent !! Je suivais déjà Sophie via Twitter ou directement sur le site du CFO masqué (forum et astuce) et quand il a fallu déterminer quel organisme de formation prendre, j'ai naturellement pensé au CFO Masqué. Je ne regrette absolument pas mon choix !

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

 

4 réflexions sur “Power Query: Voici un exemple de transposition de données utile”

  1. Bonjour
    Je voudrais vous proposer une autre solution à ce problème et je pense qu’elle est plus simple à mettre en œuvre.
    Je suis parti du tableau de départ que j’ai recopier en A1.
    Ensuite dans la cellule D2 j’ai tapé la formule suivante :
    =DECALER($B$2;(LIGNES(B$2:B2)-1)*8+COLONNES($B:B)-1;)
    J’ai recopié la formule jusqu’à la cellule K2
    Puis j’ai séléctionné la zone D2:K2 et recopié le tout vers le bas

    Il suffirait par la suite de sélectionner l’ensemble, de le copier et de faire un collage spécial valeurs pour s’affranchir des formules.
    Cordialement
    José

    1. Bonjour José,

      Merci beaucoup pour votre proposition de solution. Toutefois, ici, il s’agissait d’utiliser Power Query pour automatiser la transformation de données et éviter les opérations manuelles.

      Au plaisir,

      Sophie

  2. Voici une approche Power Query un peu plus simple:
    1. ajout d’une colonne Index
    2. ajout d’une colonne identifiant chacune des lignes de 1 à 8 en utilisant Number.Mod avec la colonne Index
    3. en utilisant cette dernière colonne les 8 lignes peuvent être pivotées

    Voici le code M:
    let
    Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
    #”Added Index” = Table.AddIndexColumn(Source, “Index”, 0, 1),
    #”Added Custom” = Table.AddColumn(#”Added Index”, “Custom”, each Number.Mod([Index],8)+1),
    #”Removed Columns” = Table.RemoveColumns(#”Added Custom”,{“Index”}),
    #”Changed Type” = Table.TransformColumnTypes(#”Removed Columns”,{{“Custom”, type text}}),
    #”Pivoted Column1″ = Table.Pivot(#”Changed Type”, List.Distinct(#”Changed Type”[Custom]), “Custom”, “Column2″),
    #”Removed Columns1″ = Table.RemoveColumns(#”Pivoted Column1”,{“Column1″}),
    #”Promoted Headers” = Table.PromoteHeaders(#”Removed Columns1″, [PromoteAllScalars=true])
    in
    #”Promoted Headers”

    Daniel

  3. The same result you can achieve using this code below.
    let
    Source = Excel.CurrentWorkbook(){[Name=”Tabela1″]}[Content],
    #”Grouped Rows” = Table.Group(Source, {“Column1”}, {{“lst”, each _[Column2], type list}}),
    Result = Table.FromRows(List.Skip(#”Grouped Rows”[lst],1), #”Grouped Rows”[lst]{0})
    in
    Result

    Regards 🙂

Laisser un commentaire

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

Scroll to Top