Combiner toutes les lignes (Cross Join) avec Power Query

Dernière mise à jour le 21/05/2024
Temps de lecture : 5 minutes

Cet article va vous expliquer comment combiner toutes les lignes de 2 colonnes différentes pour fabriquer une nouvelle liste. Pour cela, nous allons utiliser Power Query

Combiner toutes les cellules dans un seul Tableau

C'est quoi Power Query ?

Power Query est l'outil moderne d'importation et de transformation des données. Son utilisation est très simple et ne nécessite pas de connaissance avancé d'Excel ni de connaissance en programmation.

Si vous voulez vous former à l'utilisation de Power Query, je vous ai conçu cette formation sur Udemy très simple d'accès.

Formation Power Query Udemy

Point de départ, 2 listes distinctes

Prenons 2 listes,

  • l'une contient la liste de vos vendeurs (5 lignes).
  • l'autre contient 10 dates (jours ouvrés).

Ce que nous cherchons à faire, c'est de croiser toutes les cellules pour avoir autant de combinaisons possibles entre les vendeurs et dates soit =5*10 => 50 combinaisons à créer.

Pour éviter de fastidieux copier-coller, vous pouvez effectuer ce travail en 3 actions seulement.

Étape 1 : Insérer vos données dans deux Tableaux

Mettre ses données dans une Table, cela permet de donner un nom à la plage de données et cela va nous servir pour la suite. Pour insérer vos données dans une Table, il suffit de sélectionner l'une des cellules de vos données et d'activer le menu Insertion > Tableau

Menu insertion Tableau

Il est aussi important de donner un nom à vos Tables. De cette façon, il sera plus facile d'identifier les données par la suite (voir étape 2).

Étape 2 : Transférer un premier Tableau dans Power Query

La cellule active est toujours dans votre Tableau et là, vous sélectionnez le menu Données > A partir d'un Tableau

Menu à partir d'un tableau

Vos données sont maintenant chargées dans le nouvel outil de manipulation des données d'Excel, Power Query.

Chargement des données dans Power Query

Le nom de la requête est le nom de votre Tableau. D'où l'importance du choix du nom de votre Tableau.

Etape 3 : Charger ces données en mémoire

Vous n'avez pas besoin de transformer ou de modifier les données. Nous allons quitter Power Query et charger les données en mémoire.
Pour cela, nous allons choisir le menu Accueil > Fermer et Charger > Fermer et Charger dans ...

Charger le résultat de la requête en mémoire

Et dans la nouvelle boîte de dialogue choisissez l'option Ne créer que la connexion. De cette façon, les données seront chargées dans Power Query mais pas dans une feuille Excel.

Créer la connexion uniquement

Étape 4 : Transférer le deuxième Tableau dans Power Query

Nous allons maintenant charger le deuxième Tableau de données (les dates) dans Power Query.

  1. Insertion des données dans une Table (comme à l'étape 2).
  2. Données > A partir d'un Tableau ou d'une plage.
Les dates ont été importées au format Dates et Heures

Étape 5 : Changer le type de données

Dans Power Query, il est très important de respecter le type de données contenues dans les colonnes. C'est le même principe que pour une base de données. Nous allons donc indiquer que le type de données de ce second Tableau c'est des dates uniquement.

  1. Cliquer sur l'icône en haut à droite de l'entête de colonne pour faire apparaître le menu des types de données.
  2. Choisissez le type date (et pas date et heure comme c'est le cas actuellement).
Changement du type de données dans Power Query

Et donc maintenant votre colonne ne contient plus que des dates, sans les heures.

Format Date seule dans Power Query

Etape 6 : Combiner les 2 requêtes

Nous allons maintenant croiser les 2 requêtes (c'est le nom une fois les données chargées dans Power Query).

  • Restez dans la requête contenant les dates.
  • Allez dans le menu Ajouter une colonne > Colonne personnalisée.
Ajouter une colonne personnalisée dans Power Query

C'est maintenant que la magie opère. Commencez par donner un nom à la colonne qui va être ajoutée (1) et écrivez la formule suivante :

= NomdelaRequete[NomdelaColonne]

Dans cet exemple la fonction s'écrit

= tblVendeur[Vendeur]

Formule pour récupérer le contenu de la seconde requête

Il est très important de respecter exactement l'écriture de la requête et de la colonne que vous voulez importer. Power Query est sensible à la casse c'est-à-dire aux majuscules et minuscules.

Le résultat se traduit par l'ajout d'une colonne avec l'instruction List.

Ajout d'une colonne avec l'indication List

Étape 6 bis: Détailler le contenu d'une cellule List

Pour comprendre ce qui se cache derrière l'indication List, il suffit de cliquer sur l'une des cellules qui contient le mot List. Mais attention, ne cliquez pas sur le mot List, juste dans la cellule. Cette action vous affiche dans une nouvelle fenêtre ce que contient l'instruction List. Et là, vous voyez bien s'afficher la liste des vendeurs.

Détail du contenu d'une List dans Power Query

Étape 7 : Déployer la liste

Pour combiner les deux listes, il suffit de cliquer sur l'icône de l'entête de la colonne et de sélectionner l'option Développer sur de nouvelles lignes.

Déployer une liste dans Power Query

Et Voilàààà ! Toutes les lignes sont immédiatement croisées.

Toutes les lignes des 2 colonnes sont maintenant combinées

Etape 8 : Charger le résultat dans Excel

Il ne nous reste plus qu'à transférer ce résultat dans Excel en cliquant sur le menu Accueil > Fermer & Charger. Le résultat s'affiche dans Excel dans un nouveau Tableau

Les 2 Tableaux sont maintenant fusionnés pour toutes les combinaisons possibles

Articles liés

3 Comments

  1. Lavabre
    20/11/2020 @ 09:13

    Impeccable !!! Merci beaucoup!!

    Reply

  2. Gaelle
    14/09/2020 @ 11:43

    Bonjour,
    Est-il possible d'utiliser cette technique de combinaison, en lien avec la création d'une relation entre les deux tableaux (dans l'exemple suivant la parcelle).

    Par exemple :
    Tab1:
    Parcelle1 Infra1
    Parcelle1 infra2
    Parcelle2 infra2

    Tab2 :
    Parcelle1 Proprio1
    Parcelle1 Proprio2
    Parcelle2 Proprio2

    Tableau recherché :
    Parcelle1 Proprio1 Infra1
    Parcelle1 Proprio2 Infra1
    Parcelle1 Proprio1 Infra2
    Parcelle1 Proprio2 Infra2
    Parcelle2 Proprio2 Infra2

    Merci de votre réponse

    Reply

  3. Nadine JACQUES
    03/09/2020 @ 15:41

    Bonjour,

    J'ai une grande BDD (8000 lignes) et je voudrais faire du tri.
    Mon objectif est que pour chaque catégorie, une nouvelle feuille se crée mais que mon tableau et la feuille soit liés.

    Je souhaiterais que les mises à jours se mette des deux côté.

    Un conseil? Query, connexion indirect? Ou autre...

    Merci pour votre aide !

    Nadine J

    Reply

Laisser un commentaire

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

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

Microsoft MVP 2024

Combiner toutes les lignes (Cross Join) avec Power Query

Reading time: 5 minutes
Dernière mise à jour le 21/05/2024

Cet article va vous expliquer comment combiner toutes les lignes de 2 colonnes différentes pour fabriquer une nouvelle liste. Pour cela, nous allons utiliser Power Query

Combiner toutes les cellules dans un seul Tableau

C'est quoi Power Query ?

Power Query est l'outil moderne d'importation et de transformation des données. Son utilisation est très simple et ne nécessite pas de connaissance avancé d'Excel ni de connaissance en programmation.

Si vous voulez vous former à l'utilisation de Power Query, je vous ai conçu cette formation sur Udemy très simple d'accès.

Formation Power Query Udemy

Point de départ, 2 listes distinctes

Prenons 2 listes,

  • l'une contient la liste de vos vendeurs (5 lignes).
  • l'autre contient 10 dates (jours ouvrés).

Ce que nous cherchons à faire, c'est de croiser toutes les cellules pour avoir autant de combinaisons possibles entre les vendeurs et dates soit =5*10 => 50 combinaisons à créer.

Pour éviter de fastidieux copier-coller, vous pouvez effectuer ce travail en 3 actions seulement.

Étape 1 : Insérer vos données dans deux Tableaux

Mettre ses données dans une Table, cela permet de donner un nom à la plage de données et cela va nous servir pour la suite. Pour insérer vos données dans une Table, il suffit de sélectionner l'une des cellules de vos données et d'activer le menu Insertion > Tableau

Menu insertion Tableau

Il est aussi important de donner un nom à vos Tables. De cette façon, il sera plus facile d'identifier les données par la suite (voir étape 2).

Étape 2 : Transférer un premier Tableau dans Power Query

La cellule active est toujours dans votre Tableau et là, vous sélectionnez le menu Données > A partir d'un Tableau

Menu à partir d'un tableau

Vos données sont maintenant chargées dans le nouvel outil de manipulation des données d'Excel, Power Query.

Chargement des données dans Power Query

Le nom de la requête est le nom de votre Tableau. D'où l'importance du choix du nom de votre Tableau.

Etape 3 : Charger ces données en mémoire

Vous n'avez pas besoin de transformer ou de modifier les données. Nous allons quitter Power Query et charger les données en mémoire.
Pour cela, nous allons choisir le menu Accueil > Fermer et Charger > Fermer et Charger dans ...

Charger le résultat de la requête en mémoire

Et dans la nouvelle boîte de dialogue choisissez l'option Ne créer que la connexion. De cette façon, les données seront chargées dans Power Query mais pas dans une feuille Excel.

Créer la connexion uniquement

Étape 4 : Transférer le deuxième Tableau dans Power Query

Nous allons maintenant charger le deuxième Tableau de données (les dates) dans Power Query.

  1. Insertion des données dans une Table (comme à l'étape 2).
  2. Données > A partir d'un Tableau ou d'une plage.
Les dates ont été importées au format Dates et Heures

Étape 5 : Changer le type de données

Dans Power Query, il est très important de respecter le type de données contenues dans les colonnes. C'est le même principe que pour une base de données. Nous allons donc indiquer que le type de données de ce second Tableau c'est des dates uniquement.

  1. Cliquer sur l'icône en haut à droite de l'entête de colonne pour faire apparaître le menu des types de données.
  2. Choisissez le type date (et pas date et heure comme c'est le cas actuellement).
Changement du type de données dans Power Query

Et donc maintenant votre colonne ne contient plus que des dates, sans les heures.

Format Date seule dans Power Query

Etape 6 : Combiner les 2 requêtes

Nous allons maintenant croiser les 2 requêtes (c'est le nom une fois les données chargées dans Power Query).

  • Restez dans la requête contenant les dates.
  • Allez dans le menu Ajouter une colonne > Colonne personnalisée.
Ajouter une colonne personnalisée dans Power Query

C'est maintenant que la magie opère. Commencez par donner un nom à la colonne qui va être ajoutée (1) et écrivez la formule suivante :

= NomdelaRequete[NomdelaColonne]

Dans cet exemple la fonction s'écrit

= tblVendeur[Vendeur]

Formule pour récupérer le contenu de la seconde requête

Il est très important de respecter exactement l'écriture de la requête et de la colonne que vous voulez importer. Power Query est sensible à la casse c'est-à-dire aux majuscules et minuscules.

Le résultat se traduit par l'ajout d'une colonne avec l'instruction List.

Ajout d'une colonne avec l'indication List

Étape 6 bis: Détailler le contenu d'une cellule List

Pour comprendre ce qui se cache derrière l'indication List, il suffit de cliquer sur l'une des cellules qui contient le mot List. Mais attention, ne cliquez pas sur le mot List, juste dans la cellule. Cette action vous affiche dans une nouvelle fenêtre ce que contient l'instruction List. Et là, vous voyez bien s'afficher la liste des vendeurs.

Détail du contenu d'une List dans Power Query

Étape 7 : Déployer la liste

Pour combiner les deux listes, il suffit de cliquer sur l'icône de l'entête de la colonne et de sélectionner l'option Développer sur de nouvelles lignes.

Déployer une liste dans Power Query

Et Voilàààà ! Toutes les lignes sont immédiatement croisées.

Toutes les lignes des 2 colonnes sont maintenant combinées

Etape 8 : Charger le résultat dans Excel

Il ne nous reste plus qu'à transférer ce résultat dans Excel en cliquant sur le menu Accueil > Fermer & Charger. Le résultat s'affiche dans Excel dans un nouveau Tableau

Les 2 Tableaux sont maintenant fusionnés pour toutes les combinaisons possibles

Articles liés

3 Comments

  1. Lavabre
    20/11/2020 @ 09:13

    Impeccable !!! Merci beaucoup!!

    Reply

  2. Gaelle
    14/09/2020 @ 11:43

    Bonjour,
    Est-il possible d'utiliser cette technique de combinaison, en lien avec la création d'une relation entre les deux tableaux (dans l'exemple suivant la parcelle).

    Par exemple :
    Tab1:
    Parcelle1 Infra1
    Parcelle1 infra2
    Parcelle2 infra2

    Tab2 :
    Parcelle1 Proprio1
    Parcelle1 Proprio2
    Parcelle2 Proprio2

    Tableau recherché :
    Parcelle1 Proprio1 Infra1
    Parcelle1 Proprio2 Infra1
    Parcelle1 Proprio1 Infra2
    Parcelle1 Proprio2 Infra2
    Parcelle2 Proprio2 Infra2

    Merci de votre réponse

    Reply

  3. Nadine JACQUES
    03/09/2020 @ 15:41

    Bonjour,

    J'ai une grande BDD (8000 lignes) et je voudrais faire du tri.
    Mon objectif est que pour chaque catégorie, une nouvelle feuille se crée mais que mon tableau et la feuille soit liés.

    Je souhaiterais que les mises à jours se mette des deux côté.

    Un conseil? Query, connexion indirect? Ou autre...

    Merci pour votre aide !

    Nadine J

    Reply

Laisser un commentaire

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

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.