Fusionner des requêtes
Rapprocher deux requêtes par une colonne commune ; choisir le type de jointure adapté ; développer les colonnes utiles ; réaliser l'équivalent d'une recherche approximative (classes d'âge).
Pourquoi ?
Une table de ventes contient un code canal, mais pas son libellé ; une liste de commandes contient un numéro de client, mais pas sa région. Fusionner des requêtes ajoute à droite d'une table les colonnes d'une autre table en rapprochant une clé, comme RECHERCHEV dans Excel. C'est aussi un outil de contrôle : les jointures « anti » isolent les lignes sans correspondance.
Dans Power BI, beaucoup de ces rapprochements se font plutôt par une relation dans le modèle sémantique (partie 3). Fusionnez dans Power Query lorsque vous voulez réellement aplatir deux tables en une seule, ou contrôler des correspondances.
Notions clés
Les six types de jointure
L'exemple compte 5 clients. Le client 1 a passé 1 commande, le client 2 en a passé 2, le client 3 en a passé 3, les clients 4 et 5 aucune. Le client 10 a passé une commande mais n'existe pas dans la table Client, ce qu'une base de données respectant l'intégrité référentielle interdirait.
Table Client :
| client_id |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
Table Commandes :
| commande_id | client_id_commande | montant |
| 1 | 1 | 100 |
| 2 | 2 | 200 |
| 3 | 2 | 300 |
| 4 | 3 | 400 |
| 5 | 3 | 500 |
| 6 | 3 | 600 |
| 7 | 10 | 1 000 |
La fusion part de Client (première table) et rapproche client_id de client_id_commande.
| Jointure | Lignes conservées | Lignes | Usage |
| Externe gauche (par défaut) | Tous les clients, avec leurs commandes éventuelles | 8 | Le choix par défaut, à utiliser dans le doute |
| Externe droite | Toutes les commandes, même celle du client 10 | 7 | Garder toutes les lignes de la seconde table |
| Externe entière | Tous les clients et toutes les commandes | 9 | Comparer deux listes dans leur ensemble |
| Interne | Seulement les clients ayant des commandes et les commandes ayant un client | 6 | Ne garder que les correspondances |
| Anti gauche | Seulement les clients sans commande | 2 | Isoler des cas particuliers |
| Anti droite | Seulement les commandes sans client | 1 | Détecter des non-conformités |
Les jointures anti s'appelaient « gauche opposée » et « droite opposée » dans d'anciennes versions de l'interface. Source
Résultat de la jointure externe gauche (les colonnes de Commandes valent null pour les clients 4 et 5) :
| client_id | commande_id | client_id_commande | montant |
| 1 | 1 | 1 | 100 |
| 2 | 2 | 2 | 200 |
| 2 | 3 | 2 | 300 |
| 3 | 4 | 3 | 400 |
| 3 | 5 | 3 | 500 |
| 3 | 6 | 3 | 600 |
| 4 | null | null | null |
| 5 | null | null | null |
Résultat de la jointure externe droite :
| client_id | commande_id | client_id_commande | montant |
| 1 | 1 | 1 | 100 |
| 2 | 2 | 2 | 200 |
| 2 | 3 | 2 | 300 |
| 3 | 4 | 3 | 400 |
| 3 | 5 | 3 | 500 |
| 3 | 6 | 3 | 600 |
| null | 7 | 10 | 1 000 |
La jointure externe entière donne les 6 lignes de l'interne, plus les lignes des clients 4 et 5 (colonnes de Commandes à null) et celle de la commande 7 (client_id à null). La jointure interne donne les 6 premières lignes du tableau ci-dessus. L'anti gauche donne les clients 4 et 5 avec des colonnes de Commandes à null, l'anti droite la seule commande 7 avec client_id à null.
Le kit pédagogique contient l'exercice « sample23-Query-Join », qui reprend cet exemple et ajoute une comparaison entre deux périodes.
Pas à pas
L'exemple utilise le classeur Data_Ventes.xlsx du kit pédagogique : la table tblVentes contient un code canal de vente, la table tblCanalVente son libellé.
1. Dans Power BI Desktop, Accueil > Obtenir les données > Classeur Excel, sélectionnez Data_Ventes.xlsx, cochez tblVentes et tblCanalVente, puis cliquez sur Transformer les données.
2. Sélectionnez la requête tblVentes, puis Accueil > Fusionner des requêtes (ou Fusionner des requêtes comme étant nouvelles pour garder tblVentes intacte).
3. Dans la fenêtre Fusionner, cliquez sur la colonne CodeCanalVente de tblVentes, choisissez tblCanalVente dans la liste déroulante, cliquez sur sa colonne CodeCanalVente, puis laissez Externe gauche comme type de jointure.
4. Vérifiez le message de correspondance en bas de la fenêtre, puis cliquez sur OK. Une colonne tblCanalVente apparaît, avec la valeur Table sur chaque ligne.
5. Cliquez sur le bouton de développement (deux flèches) à droite de l'en-tête de cette colonne.
6. Cochez uniquement Libelle et décochez Utiliser le nom de la colonne d'origine comme préfixe, sinon la colonne s'appelle tblCanalVente.Libelle. Cliquez sur OK.
7. Renommez la colonne Libelle en Canal vente, puis décochez Activer le chargement sur tblCanalVente si elle ne sert plus dans le modèle.
Si le message de correspondance ne s'affiche pas et qu'un bandeau signale une erreur Formula.Firewall après la fusion, consultez la page « Niveaux de confidentialité et erreur Firewall ».
Exemple
Le code de la fusion
L'étape générée utilise Table.NestedJoin, qui ajoute une colonne de tables, puis Table.ExpandTableColumn pour le développement :
= Table.NestedJoin(tblVentes, {"CodeCanalVente"}, tblCanalVente, {"CodeCanalVente"}, "tblCanalVente", JoinKind.LeftOuter)= Table.ExpandTableColumn(#"Requêtes fusionnées", "tblCanalVente", {"Libelle"}, {"Libelle"})Les autres types s'obtiennent en remplaçant JoinKind.LeftOuter par JoinKind.RightOuter, JoinKind.FullOuter, JoinKind.Inner, JoinKind.LeftAnti ou JoinKind.RightAnti. Source
L'équivalent d'un RECHERCHEV avec correspondance approximative
Dans Excel, RECHERCHEV avec VRAI en dernier argument trouve la classe d'âge d'une personne à partir d'une table de seuils :
Power Query n'a pas de jointure « par intervalle ». La méthode par l'interface repose sur une jointure externe entière, un tri et un remplissage vers le bas :
1. Chargez les deux tables, Personnes (colonne Age) et tblSeuilAge (colonnes SeuilAge et ClasseAge).
2. Sur Personnes, Accueil > Fusionner des requêtes : colonne Age, table tblSeuilAge, colonne SeuilAge, jointure Externe entière.
3. Développez la colonne tblSeuilAge (SeuilAge et ClasseAge), sans préfixe.
4. Ajouter une colonne > Colonne conditionnelle : si Age est égal à null, alors SeuilAge, sinon Age. Nommez-la Tri et typez-la en Nombre entier.
5. Triez la colonne Tri par ordre croissant, puis sélectionnez ClasseAge et clic droit > Remplir > Vers le bas : chaque personne reçoit la classe du seuil qui la précède.
6. Supprimez les colonnes SeuilAge et Tri, puis filtrez la colonne Age pour masquer les null (les lignes de seuils).
Vérifiez en ajoutant des âges dans le fichier source, par exemple 15, 30 et 40 ans, puis en actualisant :
Variante en M (nouvelle) : une colonne personnalisée sélectionne le dernier seuil inférieur ou égal à l'âge. Code testable dans une requête vide :
let
Seuils = Table.Buffer(#table(
type table [SeuilAge = number, ClasseAge = text],
{{0, "Moins de 18 ans"}, {18, "18-34 ans"}, {35, "35-54 ans"}, {55, "55 ans et plus"}}
)),
Personnes = #table(type table [Nom = text, Age = number], {{"Alice", 15}, {"Bruno", 30}, {"Chloé", 40}, {"David", 55}}),
AvecClasse = Table.AddColumn(
Personnes,
"ClasseAge",
each Table.Last(Table.SelectRows(Seuils, (s) => s[SeuilAge] <= [Age]))[ClasseAge],
type text
)
in
AvecClasseRésultat attendu : Alice « Moins de 18 ans », Bruno « 18-34 ans », Chloé « 35-54 ans », David « 55 ans et plus ». La table des seuils doit être triée par SeuilAge croissant.
Correspondance approximative sur du texte
Pour rapprocher des libellés mal orthographiés (« Sté Dupont » et « Société Dupont »), cochez Utiliser la correspondance approximative pour effectuer la fusion dans la fenêtre Fusionner et réglez le seuil de similarité. Cette option ne fonctionne que sur des colonnes de texte. Source
Pièges et bonnes pratiques
- Une clé en double dans la seconde table multiplie les lignes de la première : contrôlez son unicité avec Distribution des colonnes avant de fusionner.
- Les clés doivent avoir le même type des deux côtés (texte et texte, nombre et nombre), sinon aucune ligne ne correspond.
- La comparaison respecte la casse et les espaces : nettoyez les clés (Format > Supprimer les espaces, casse uniforme) avant la fusion.
- Ne développez que les colonnes utiles ; une fusion suivie d'un développement complet alourdit la requête.
À retenir
- Fusionner ajoute des colonnes en rapprochant une clé ; ajouter empile des lignes.
- Externe gauche est le type par défaut et le plus sûr ; les jointures anti servent au contrôle.
- Après la fusion, développez la colonne de tables en ne cochant que les champs utiles, sans préfixe.
- Une recherche par intervalle s'obtient par jointure externe entière, tri et remplissage vers le bas, ou par une colonne personnalisée en M.
Pour aller plus loin
- Vue d'ensemble de la fusion de requêtes. Source
- Jointure externe gauche. Source
- Anti jointure gauche. Source
- Correspondance approximative. Source