Power BI : Création une table à partir d’une non-équi-jointure (utilisation d’un produit cartésien intermédiaire)

Lors de la création d’un P.O.C. Power BI pour un client j’ai été confronté au besoin de réaliser une non-équi-jointure pour alimenter une table à partir de deux onglets Excel. La solution adoptée n’étant pas immédiate, je vous la soumets.

Cas d’utilisation :

Création d’un calendrier prévisionnel pour une liste de contrats fournis dans un onglet Excel sur la base d’un calendrier fourni dans un autre document Excel.

Fichier Contrat

Nom Date Début contrat Date Fin contrat
Adam 01/04/2015 31/01/2016
Bob 01/06/2015 29/02/2016
Joe 01/01/2016

Fichier Calendrier

Mois Nb Jours ouvrés
01/01/2016 22
01/02/2016 20
01/03/2016 23

Première étape : Créer une table dans le dataset pour le fichier calendrier

Table calendrier

Deuxième étape : Produit cartésien pour obtenir une ligne par mois et par personne

Nous importons désormais le fichier Contrats.
Dans le requête permettant l’alimentation de la table Contrats, nous ajoutons une colonne de clé fictive qui va nous permettre de réaliser le produit cartésien. Cette clé fictive aura la valeur 1 et sera présente dans les deux jeux de données à lier. Dans la table contrats :
Cle Fictive Contrat
Nous faisons de même dans la requête d’import de la table Calendrier
Cle Fictive Calendrier
Nous utilisons ici la jointure interne basée sur la clé fictive (fusion de requêtes) de manière à ce que chaque ligne de la table Calendrier joigne avec chaque ligne de la table Contrat.
Fusion
Nous développons ensuite la table jointe pour en conserver les colonnes pertinentes « Mois » et « Nb Jours ouvrés ».
Développement de la table jointe
Nous avons réalisé un produit cartésien !

Le code M de la requête :

let
    Source = Excel.Workbook(File.Contents("source.xlsx"), null, true),
    Contrats_Sheet = Source{[Item="Contrats",Kind="Sheet"]}[Data],
    #"En-têtes promus" = Table.PromoteHeaders(Contrats_Sheet),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"Nom", type text}, {"Début", type date}, {"Fin", type date}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "CleFictive", each 1),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Personnalisée ajoutée",{"CleFictive"},Calendrier,{"CleFictive"},"NewColumn",JoinKind.Inner),
    #"NewColumn développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "NewColumn", {"Période", "NbJours"}, {"NewColumn.Période", "NewColumn.NbJours"})
in
    #"NewColumn développé"

Troisième étape : Filtrer le résultat de la deuxième étape pour limiter à la période comprise dans le contrat

Nous utilisons ici la fonction Table.SelectRows pour ne conserver que les lignes comprises dans l’intervalle du contrat.
La condition de jointure est : debut ≤ periode && (fin ≥ periode or fin is null)
L’utilisation de cette fonction n’est pas associée à ma connaissance à un designer, nous allons donc éditer la requête et introduire une sélection de ligne juste avant le « in » :

let
    Source = Excel.Workbook(File.Contents("source.xlsx"), null, true),
    Contrats_Sheet = Source{[Item="Contrats",Kind="Sheet"]}[Data],
    #"En-têtes promus" = Table.PromoteHeaders(Contrats_Sheet),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"Nom", type text}, {"Début", type date}, {"Fin", type date}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "CleFictive", each 1),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Personnalisée ajoutée",{"CleFictive"},Calendrier,{"CleFictive"},"NewColumn",JoinKind.Inner),
    #"NewColumn développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "NewColumn", {"Période", "NbJours"}, {"NewColumn.Période", "NewColumn.NbJours"}),
    #"periodes sélectionnées" = Table.SelectRows(#"NewColumn développé", each ([Début] = [NewColumn.Période] or [Fin] = null)))
in
    #"periodes sélectionnées"


NB : Exploitant un produit cartésien intermédiaire, il faut être sûr d’en maîtriser la volumétrie avant d’appliquer cette méthode au risque d’obtenir des temps de chargement du dataset exponentiels.
Bien entendu cette méthode n’a d’intérêt que si le traitement ne peut être réalisé sur la source comme c’est le cas ici.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s