Attribuer automatiquement une catégorie aux lignes non catégorisées dans Power Query et DAX
Le scénario
pour rendre compte de la possession des sièges.
Chaque siège de l’immeuble de bureaux doit être attribué à une unité organisationnelle (UO).
Il existe une liste de tous les sièges et l’unité d’organisation propriétaire doit être définie pour chaque siège. Mais certains sièges n’ont pas d’ensemble d’UO propriétaire.
Par conséquent, ils ont décidé d’attribuer tous les sièges non attribués à l’unité d’organisation qui possède le plus de sièges.
Cela doit être fait par pièce et par étage.
Regardez le plan d’étage suivant :
Regardez les sièges marqués dans la salle B.
Comme vous pouvez le constater, « OU 3 » possède le plus de sièges dans cette salle.
Par conséquent, ces sièges doivent être attribués à cette unité d’organisation.
Mais si l’on considère l’ensemble de l’étage, « OU 5 » possède le plus de sièges dans toutes les salles.
S’il y a une salle à cet étage où tous les sièges ne sont pas attribués, ils doivent être attribués à « OU 5 ».
Il s’agit des règles métier pour l’attribution des sièges non attribués.
La source de données
Toutes les données sont stockées dans des fichiers Excel.
Chaque fichier a une date au début du nom de fichier.
Par exemple:
- 20260331_Seatlist.xlsx
- 20260430_Seatlift.xlsx
- 20260531_Seatlist.xlsx
Il existe un fichier par mois contenant tous les sièges existants dans le bâtiment, ainsi que leurs affectations pour ce mois.
En effet, les affectations changent avec le temps et l’utilisateur doit pouvoir voir les différences.
Mais seul le dernier ensemble de données (fichier Excel) doit être utilisé pour attribuer les sièges non attribués.
Faites-le dans Power Query
Comme vous le savez peut-être grâce à mes articles précédents, mon objectif est d’effectuer des transformations de données le plus tôt possible dans la chaîne de chargement.
Il était donc naturel de commencer à travailler dans Power Query.
Ce que je devais faire, c’était suivre les étapes suivantes pour chaque ligne sans unité d’organisation attribuée :
- Trouver le dernier fichier parmi tous les fichiers chargés
- Lire ce fichier
- Rechercher les lignes de la pièce actuelle (La pièce dans la ligne actuelle)
- Comptez le nombre de sièges par unité d’organisation dans cette salle
- Trier par ordre décroissant par nombre de places
- Conservez uniquement la première rangée – celle avec l’UO qui a le plus grand nombre de sièges
- Attribuer cette unité d’organisation à la ligne actuelle
Pour cela, j’ai créé une fonction M [CheckMax_ForSeat].
Le code de cette fonction est composé des segments suivants :
1. Recherchez le dernier fichier :
Source = Folder.Files(SourceFolder & "\Seatlists\"),
#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "20")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Name", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1)Ce code fonctionne, car la date est au début du nom du fichier, comme mentionné ci-dessus.
2. Lisez le fichier :
#"Added Custom" = Table.AddColumn(#"Kept First Rows", "FullFilePath", each [Folder Path] & [Name], type text),
#"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "ReadSingleFile_Seatlist", each ReadSingleFile_Seatlist_ForSeat([FullFilePath])),
#"Removed Other Columns" = Table.SelectColumns(#"Invoked Custom Function",{"Name", "Date modified", "ReadSingleFile_Seatlist"}),
#"Expanded ReadSingleFile_Seatlist" = Table.ExpandTableColumn(#"Removed Other Columns", "ReadSingleFile_Seatlist", {"OU-No.", "Room-No."}, {"OU-No.", "Room-No."}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded ReadSingleFile_Seatlist",{{"OU-No.", type text}, {"Room-No.", type text}})Comme vous pouvez le voir, j’utilise une deuxième M-Function pour lire le fichier : ReadSingleFile_Seatlist_ForSeat
Ce fichier est utilisé pour lire le fichier courant.
Il contient le code M pour lire un fichier Excel et conserver uniquement les colonnes nécessaires.
Vous obtenez le même code M lorsque vous importez un seul fichier Excel dans Power Query.
Pour cette raison, je ne le montrerai pas ici.
3. Conservez uniquement les lignes de la salle actuelle et obtenez l’UO avec le plus grand nombre de sièges :
#"Filter out Empty OE" = Table.SelectRows(#"Changed Type", each ([#"Room-No."] = RoomNo) and ([#"OU-No."] <> null)),
#"Grouped Rows" = Table.Group(#"Filter out Empty OU", {"OU-No.", "Assigned_OU"}, {{"Seat_Count", each Table.RowCount(_), Int64.Type}}),
#"Sorted Seat_Count" = Table.Sort(#"Grouped Rows",{{"Seat_Count", Order.Descending}, {"Assigned_OU", Order.Ascending}}),
#"Kept highest Seat Count" = Table.FirstN(#"Sorted Seat_Count",1)Je dois effectuer ces opérations deux fois :
- Une fois pour les places dans la même salle
- Une fois pour les chambres du même étage
Le résultat est deux colonnes, contenant l’UO à attribuer en fonction de la même pièce ou du même étage.
Si la salle n’a pas de sièges attribués, définissez l’unité d’organisation sur l’unité d’organisation ayant le plus grand nombre de sièges sur tout l’étage.
Sinon, prenez l’UO avec le plus de sièges attribués dans la même salle.
Cela a très bien fonctionné sur mon ordinateur portable.
Mais alors…
Ce n’est pas pratique
J’ai rencontré deux problèmes majeurs :
Dès que j’ai basculé la source vers un dossier réseau, les performances ont considérablement chuté.
Un dossier SharePoint était le pire, suivi d’un dossier partagé sur un serveur de fichiers.
Le problème était que les fonctions M mentionnées ci-dessus devaient être exécutées une fois pour chaque ligne de l’ensemble de données.
Cela a entraîné la lecture d’environ 1 Go de données, alors que le total des trois fichiers disponibles est de 300 Ko.
La cause de la baisse des performances n’était pas la quantité de données lues, car cela fonctionnait bien sur mon ordinateur portable. La raison était la latence du trafic réseau. Chaque aller-retour coûtait du temps, ce qui entraînait un temps important de chargement des données,
À propos, le fichier Power BI ne fait que 4 Mo après le chargement des données et l’attribution des unités d’organisation à tous les sièges.
Il a fallu environ une heure pour charger les données depuis le dossier partagé – plus de deux heures depuis SharePoint.
L’autre problème était encore plus grave.
Bien que cela fonctionnait dans Power BI Desktop, cela n’a pas fonctionné après sa publication sur le service.
La raison en est que les sources de données dynamiques ne sont pas autorisées dans Power Query.
Ici sont quelques links à propos de cette question.
J’ai essayé les approches mentionnées ici, mais elles n’étaient pas applicables car le chemin change toujours entre les exécutions, puisque le dernier fichier peut changer.
Par conséquent, j’ai été obligé d’abandonner cette approche et de repenser la manière de la résoudre.
Faites-le dans DAX
C’est maintenant au tour du DAX.
Je dois créer deux colonnes calculées :
- Marquer l’ensemble de données/fichier le plus récent
- Attribuer l’unité d’organisation aux sièges non attribués
L’expression DAX pour marquer le fichier le plus récent est très simple :
IsNewestFile =
VAR LatestFileDate =
CALCULATE(MAX('Raumliste_HP'[FileDate])
,REMOVEFILTERS('Roomlist')
)
RETURN
IF( LatestFileDate = 'Raumliste_HP'[FileDate]
,TRUE()
,FALSE()
)La première étape consiste à tirer parti de la transition de contexte pour obtenir la date du fichier le plus récent.
Pour y parvenir, j’ai extrait les 8 premiers caractères du nom de fichier (par exemple, 20260731_Seatlist.xlsx) et les ai stockés dans le [FileDate] colonne. Je l’ai fait dans Power Query.
Ensuite, j’ai comparé le FileDate de la ligne actuelle à cette valeur.
Quand cela correspond, j’attribue TRUE ; sinon, FAUX.
Ensuite, j’ai ajouté une autre colonne calculée pour attribuer l’unité d’organisation à chaque siège.
Pour développer cette logique, j’ai écrit une requête DAX pour simuler et tester les résultats pour une pièce à la fois.
Tout d’abord, j’ai créé une liste d’unités d’organisation attribuées pour une pièce spécifique à partir de l’ensemble de données le plus récent (les noms de colonnes sont en allemand, car j’ai travaillé avec un ensemble de données allemand) :
DEFINE
VAR RoomNr = "HP 8D01"
VAR ListOfOU = CALCULATETABLE(
SUMMARIZECOLUMNS(Raumliste_HP[Raum-Nr.]
,Raumliste_HP[Rauminhaber_OE]
)
,REMOVEFILTERS(Raumliste_HP)
,Raumliste_HP[Raum-Nr.] = RoomNr
,Raumliste_HP[IsNewestFile] = TRUE()
)
EVALUATE
ListOfOUVoici le résultat :
Ensuite, j’exclus les lignes sans [Assigned_OU] (Colonne Rauminhaber_OE), et je compte les lignes pour les lignes restantes :
DEFINE
VAR RoomNr = "HP 8D01"
VAR ListOfOU = CALCULATETABLE(
SUMMARIZECOLUMNS(Raumliste_HP[Raum-Nr.]
,Raumliste_HP[Rauminhaber_OE]
,"@RowNo", COUNTROWS(Raumliste_HP)
)
,REMOVEFILTERS(Raumliste_HP)
,Raumliste_HP[Raum-Nr.] = RoomNr
,Raumliste_HP[IsNewestFile] = TRUE()
,NOT ISBLANK(Raumliste_HP[Rauminhaber_OE])
)
EVALUATE
ListOfOUIci vous voyez le résultat :
La troisième étape consiste à trier le résultat par nombre de lignes par ordre décroissant :
DEFINE
VAR RoomNr = "HP 8D01"
VAR ListOfOU = CALCULATETABLE(
SUMMARIZECOLUMNS(Raumliste_HP[Raum-Nr.]
,Raumliste_HP[Rauminhaber_OE]
,"@RowNo", COUNTROWS(Raumliste_HP)
)
,REMOVEFILTERS(Raumliste_HP)
,Raumliste_HP[Raum-Nr.] = RoomNr
,Raumliste_HP[IsNewestFile] = TRUE()
,NOT ISBLANK(Raumliste_HP[Rauminhaber_OE])
)
EVALUATE
ListOfOU
ORDER BY [@RowNo] DESCLe résultat de cette requête est le suivant :
La dernière étape consiste à obtenir la première ligne, c’est-à-dire l’unité d’organisation avec le plus de sièges attribués :
DEFINE
VAR RoomNr = "HP 8D01"
VAR ListOfOU = CALCULATETABLE(
SUMMARIZECOLUMNS(Raumliste_HP[Raum-Nr.]
,Raumliste_HP[Rauminhaber_OE]
,"@RowNo", COUNTROWS(Raumliste_HP)
)
,REMOVEFILTERS(Raumliste_HP)
,Raumliste_HP[Raum-Nr.] = RoomNr
,Raumliste_HP[IsNewestFile] = TRUE()
,NOT ISBLANK(Raumliste_HP[Rauminhaber_OE])
)
EVALUATE
TOPN( 1
,ListOfOU
,[@RowNo], DESC
)Le résultat est une ligne :
Comme vous pouvez le voir, je peux omettre ORDER BY car les paramètres de la ligne 82 ont le même effet.
Mais que se passe-t-il si plusieurs UO disposent du même nombre de sièges attribués dans une même salle ?
La requête ci-dessus renverra deux lignes, comme TOPN() ne peut pas les distinguer :
Nous pouvons avoir l’algorithme le plus élaboré pour répondre à cette question, ou nous pouvons ajouter l’UO comme colonne de tri pour obtenir une ligne :
DEFINE
VAR RoomNr = "HP 8B01"
VAR ListOfOU = CALCULATETABLE(
SUMMARIZECOLUMNS(Raumliste_HP[Raum-Nr.]
,Raumliste_HP[Rauminhaber_OE]
,"@RowNo", COUNTROWS(Raumliste_HP)
)
,REMOVEFILTERS(Raumliste_HP)
,Raumliste_HP[Raum-Nr.] = RoomNr
,Raumliste_HP[IsNewestFile] = TRUE()
,NOT ISBLANK(Raumliste_HP[Rauminhaber_OE])
)
EVALUATE
TOPN( 1
,ListOfOU
,[@RowNo], DESC
,[Rauminhaber_OE], ASC
)Maintenant, nous obtenons une seule ligne dans le résultat :
J’ai maintenant toute la logique pour attribuer une unité d’organisation à chaque siège d’une salle.
Voici le code de la colonne calculée :
Assigned OU_ByRoom =
VAR RoomNo = 'Raumliste_HP'[Raum-Nr.]
VAR ListOfOU = CALCULATETABLE(
SUMMARIZECOLUMNS(Raumliste_HP[Raum-Nr.]
,Raumliste_HP[Rauminhaber_OE]
,"@RowNo", COUNTROWS(Raumliste_HP)
)
,REMOVEFILTERS(Raumliste_HP)
,Raumliste_HP[Raum-Nr.] = RoomNo
,Raumliste_HP[IsNewestFile] = TRUE()
,NOT ISBLANK(Raumliste_HP[Rauminhaber_OE])
)
VAR Result =
TOPN( 1
,ListOfOU
,[@RowNo], DESC
,[Rauminhaber_OE], ASC
)
RETURN
IF(NOT ISBLANK('Raumliste_HP'[Rauminhaber_OE])
,'Raumliste_HP'[Rauminhaber_OE]
,SUMMARIZE(Result
,[Rauminhaber_OE])
)Maintenant, je dois appliquer la même logique à tout l’étage.
J’ajoute une deuxième colonne calculée en utilisant presque la même logique, mais cette fois pour l’étage plutôt que pour la pièce.
Enfin, je dois attribuer l’affectation OU calculée aux lignes sans affectation OU.
Cela suivra la même logique que celle que j’ai implémentée dans la solution Power Query.
Je l’ai fait avec une instruction SWITCH() :
Assigned seat OU =
SWITCH(TRUE()
,ISBLANK('Raumliste_HP'[Rauminhaber_OE]) && ISBLANK('Raumliste_HP'[Assigned OU_ByRoom])
,'Raumliste_HP'[Assigned OU_ByFloor]
,ISBLANK('Raumliste_HP'[Rauminhaber_OE])
,'Raumliste_HP'[Assigned OU_ByRoom]
,'Raumliste_HP'[Rauminhaber_OE])Le résultat final ressemble à ceci :
Comme vous pouvez le constater, l’UO est attribuée par pièce lorsqu’une valeur est présente. Si aucune affectation n’est possible par pièce, l’UO est affectée par étage.
Conclusion
Ce fut un voyage intéressant pour construire cette solution.
J’ai suivi le principe de transformer les données le plus tôt possible, et le résultat n’était pas viable.
Même si cela fonctionnait dans Power BI Desktop avec des fichiers locaux.
À mon avis, il s’agit d’une faille dans Power BI : je peux créer une solution dans Power BI et ne recevoir aucun avertissement ou indication indiquant qu’elle pourrait ne pas fonctionner pendant le développement ou lors de sa publication sur le service cloud.
C’est la deuxième fois que je vis cela.
L’autre situation était celle où j’ai combiné des données cloud avec des données sur site. Cela fonctionnait dans PBI Desktop, mais les données ne pouvaient pas être chargées dans le service cloud. Le message d’erreur était qu’il n’était pas autorisé à charger des données provenant de différentes sources, même si j’avais défini le niveau de confidentialité sur le même (la source cloud était un dossier SharePoint Online de l’entreprise).
Dans les deux situations, j’ai été obligé de construire la solution dans le modèle de données et avec DAX.
Dans le cas décrit ici, la solution DAX était moins complexe que la solution Power Query. Mais ce n’est pas toujours le cas.
Connaître les limites de Power Query dans le cloud permet d’éviter de passer trop de temps à créer une solution qui doit être refactorisée en raison de combinaisons de données non prises en charge ou de problèmes de performances.
Mais cette connaissance vient avec le temps.
Quoi qu’il en soit, il y a un problème lorsque vous le faites dans DAX par rapport à Power Query : maintenant, j’ai des colonnes dans le modèle de données qui contiennent des résultats intermédiaires.
Je l’ai résolu en ajoutant l’annexe « _original » aux colonnes à remplacer à partir des données d’origine. De plus, j’ai défini ces colonnes comme masquées et les ai ajoutées à un dossier d’affichage nommé « Colonnes intermédiaires/Ne pas utiliser ». De cette façon, je peux m’assurer qu’ils ne sont pas utilisés.
Cela peut être évité en préparant les données plus tôt. Ensuite, je peux supprimer les colonnes intermédiaires et charger uniquement les colonnes avec des données nettoyées dans le modèle de données.
En conclusion, j’ai réalisé que la « bonne façon » de faire quelque chose n’est pas toujours la bonne manière.
Ne vous accrochez pas dogmatiquement à la « bonne voie ». Réfléchissez de manière critique et choisissez la bonne façon de faire quelque chose en fonction de votre expérience.
Mais c’est le processus normal pour apprendre à prendre une mauvaise décision. Cela arrive à tout le monde.
N’en ayez pas peur.
Références
Les données sont fictives et générées par moi.
Il n’y a aucun rapport avec les données réelles.
Ici, vous pouvez en savoir plus sur la transition de contexte dans DAX :
You may also like
Comment décoder le paramètre de température dans les LLM
