Regroupement de valeurs avec vérification de la cohérence des dates

Accueil – Le CFO masqué › Forums › Power BI › Regroupement de valeurs avec vérification de la cohérence des dates

10 sujets de 1 à 10 (sur un total de 10)
  • Auteur
    Articles
  • 26 décembre 2022 à 7 h 57 min #124216
    sprcyril
    Participant

    Bonjour,

    Je travaille sur un fichier de location d’appartements.
    Je suis bloqué sur une étape dans PowerBi dans laquelle j’aimerai compter le nombre maximum d’appartements à une adresse.
    Partant du principe qu’il ne peut pas y avoir 2 séjours au même moment dans un appartement. Si 2 séjours (ou plus) ont des dates de séjours qui se croisent ( si la date d’arrivée du séjour suivant est comprise dans la période du séjour précèdent à la même adresse, c’est qu’il y a au moins 2 appartements.

    J’ai tenté de retranscrire ma demande dans la capture d’écran (j’ai utilisé Excel par soucis de confidentialité des données RGPD)

    Auriez vous une idée de comment je peux procéder?
    Ps : mon fichier est une compilation de plusieurs autres fichiers. Je n’ai donc pas la possibilité de faire cette étape sur Excel.

    Merci pour votre aide.
    D’ici là passez de bonnes fêtes.

    Cyril

    Attachments:
    You must be logged in to view attached files.
    27 décembre 2022 à 13 h 51 min #124275
    Xavier Allart
    Participant

    Bonjour
    Voici une solution, il doit en exister d’autres plus optimisés.
    En power Query (Modification des données
    Requête 1 (sejour)
    Importer la source
    Supprimer la colonne “Durée”
    Ajouter une colonne Index à partir de 1
    Requête 2 (sejour 2)
    Créer par référence à « sejour »
    Supprimer la colonne “Adresse”
    Sélectionner la colonne « Index » et dépivoter les autres colonnes
    Renommer la colonne « Valeur » en « Date »
    Ajouter une colonne personnalisée « sejour » = »sejour »
    Requête 3 (sejour 3)
    Créer par référence à « sejour »
    Supprimer les colonnes pour ne conserver que la colonne « Index »
    Supprimer les lignes en doublon
    Ajouter une colonne personnalisée « Debut » = List.Min(Sejour[Date])
    Ajouter une colonne personnalisée « Fin » = List.Max(Sejour[Date])
    Ajouter une colonne personnalisée « Date » = {Number.From([Debut])..Number.From([Fin])}
    Développer la colonne « Date »
    Modifier son type en Date
    Supprimer les colonnes “Debut”, “Fin”
    Fusionner avec la table « Sejour 3 » sur les champs « Index » et « Date »
    Développer la nouvelle colonne sur les champs “Attribut”, “Date”, “Sejour”
    Sur la colonne « Valeur », remplir vers le bas
    Sur la colonne « Valeur », filtrer pour supprimer les null
    Sur la colonne « Attribut », remplir vers le bas
    Ajouter une colonne conditionnelle personnalisée
    if [Attribut] = “Fin” and [Sejour] = null then 0 else 1
    Filtrer pour conserver les « 1 »
    Supprimer les colonnes “Attribut”, “Sejour”, “Personnalisé”
    Fusionner avec « sejour » sur le champ « index »
    Développer la colonnes « Adresse »

    Requête 4 (sejour 4)
    Créer par référence à « sejour 4»
    Regrouper par
    Avancé
    Par Adresse et Date
    Compter les lignes

    Passer en mode visualisation
    Ajouter une mesure
    Appartements = max(‘Sejour (4)'[Nombre])

    Bonnes fetes de fin d’année
    Cordialement

    Attachments:
    You must be logged in to view attached files.
    27 décembre 2022 à 21 h 19 min #124280
    Xavier Allart
    Participant

    Bonjour
    La nuit portant conseil, voici une solution simplifiée, mais il doit en exister d’autres, plus optimisées encore.
    En power Query (Modification des données
    Requête 1 (sejour)
    Importer la source

    Requête 2 (sejour 2)
    Créer par référence à « sejour »
    Garder les colonnes “Adresse”,”Debut”,”Fin”,
    Ajouter une colonne personnalisée « Date » = {Number.From([Debut])..Number.From([Fin])}
    Développer la colonne « Date »
    Modifier son type en Date
    Supprimer les colonnes “Debut”, “Fin”
    Regrouper par
    Avancé
    Par Adresse et Date
    Compter les lignes

    Passer en mode visualisation
    Ajouter une mesure
    Appartements = max(‘Sejour (4)'[Nombre])

    Attachments:
    You must be logged in to view attached files.
    28 décembre 2022 à 15 h 21 min #124296
    sprcyril
    Participant

    Bonsoir et merci Xavier pour le temps consacré.
    Je teste cela au plus vite (si possible demain) et vous dirai.
    Bonne soirée
    Cyril

    28 décembre 2022 à 15 h 55 min #124303
    sprcyril
    Participant

    Je viens de regarder .pbix en pj.
    C’est exactement ce que je voulais !!

    Merci pour l’astuce, je vois que ca fonctionne un peu comme SQL.
    Si j’ai bien compris :
    – {Number.From([Debut])..Number.From([Fin])} va permettre de créer une liste avec l’ensemble des dates comprise entre le début et la fin
    – le Groupby va permettre de compter le nombre de fois ou chaque date apparait pour une adresse
    – la mesure max() va permettre d’obtenir le nombre maximum de fois ou une date ressort pour l’adresse et par conséquent le nombre d’appartements.

    Je pourrais ensuite si besoin créer une liaison 1-N de Sejour(2) vers Sejour. Ou bien est ce que cela n’a pas d’intérêt?

    Je viens d’apprendre une nouvelle fonction qui je pense va me servir souvent.

    J’use de votre gentillesse, mais à tout hasard auriez vous un bon site/forum/youtubeur … à me conseiller pour me perfectionner sur la pratique du Dax ?

    Merci encore

    29 décembre 2022 à 2 h 49 min #124305
    Xavier Allart
    Participant

    Bonjour

    Avec plaisir, pour la liaison ce sera en fonction des besoins.

    Pour DAX, pour commencer ici on est bien https://www.lecfomasque.com/blogue/?search=DAX

    Cordialement

    29 décembre 2022 à 3 h 33 min #124306
    sprcyril
    Participant

    Bonjour et merci pour le lien.

    Si je peux me permettre une autre question.
    Toujours sur le même exercice, j’ai mis en application le code que vous m’avez donné et cela fonctionne. Merci.

    En revanche, dans mon cas, je fonctionne sur plusieurs années et j’ai fait le regroupement par année puis par adresse.
    Je ne peux pas faire de liaison entre la table source et source (2) car je n’ai pas de valeur unique (les adresses et années sont répétées dans mes 2 tables).

    Si j’applique la mesure Max dans la table source 2, ca fonctionne parfaitement. En revanche si j’applique la mesure Max dans la table source, les données sont erronées.

    Je comprends que la mesure n’arrive pas a trouver les filtres à appliquer et affiche donc la valeur max de la colonne sans prendre en compte l’année et l’adresse. Ce qui est logique puisqu’il n’y a pas de liaison entre les 2 tables. En revanche je ne sais pas comment procéder.

    Cette fois j’ai mis les fichiers en pj.

    Merci d’avance pour votre aide.

    Attachments:
    You must be logged in to view attached files.
    29 décembre 2022 à 8 h 34 min #124311
    Xavier Allart
    Participant

    Bonjour

    Je ne trouve pas de solution pour mettre en place une relation entre les 2 tables
    Il existe une possibilité de fusionner les tables en power query
    Requête Source
    Ajouter une colonne index qu’on pourrait nommer séjour
    Requête Source 2
    Ajouter une colonne « iD » = [Adresse]&”-“&Date.ToText([Date],”yyyyMMdd”)
    Requête Source 3
    Créer une requête par référence à Source ou modifier Source
    Ajouter une colonne “Date”, = {Number.From([Debut])..Number.From([Fin])}
    Développer la colonne Date
    Modifier le type en date
    Ajouter une colonne « iD » = [Adresse]&”-“&Date.ToText([Date],”yyyyMMdd”)
    Fusionner avec la requête source 2 sur le champ « iD »
    Développer la table en conservant que la colonne Nombre
    Supprimer les lignes en doublon sur la colonne « Index »
    Il est possible de ne pas activer le chargement des requêtes « Source » et « Source 2 »
    Cordialement

    Attachments:
    You must be logged in to view attached files.
    29 décembre 2022 à 9 h 30 min #124315
    sprcyril
    Participant

    Parfait ca fonctionne, un grand merci.
    Y a t’il une manipulation à faire pour indiquer que le problème est résolu?

    Merci encore de m’avoir accordé du temps.

    Cordialement
    Cyril

    2 janvier 2023 à 15 h 17 min #124386
    sprcyril
    Participant

    Bonsoir,

    Petite rectification sur le code donné précédemment.
    Ma problématique était de pouvoir identifier des chevauchement dans des dates de location.


    @xavierallart     a eut la gentillesse de me répondre avec une très bonne solution.
    Toutefois, une fois mise en application, j’ai remarqué un problème pour ma situation.
    En effet, le jour de départ d’un vacancier peut aussi être le jour d’arrivée d’un autre vacancier.
    Or la formule, me compte cette situation comme étant 2 appartements.

    Pour résoudre le problème j’ai dû compléter le code avec “-1” après Number.From([Fin])}.
    De cette manière la formule me permet de répondre à ma problématique de vacanciers qui se croisent.

    Ce qui donne :

    En power Query (Modification des données
    Requête 1 (sejour)
    Importer la source

    Requête 2 (sejour 2)
    Créer par référence à « sejour »
    Garder les colonnes “Adresse”,”Debut”,”Fin”,
    Ajouter une colonne personnalisée « Date » = {Number.From([Debut])..(Number.From([Fin])-1)}
    Développer la colonne « Date »
    Modifier son type en Date
    Supprimer les colonnes “Debut”, “Fin”
    Regrouper par
    Avancé
    Par Adresse et Date
    Compter les lignes

    Passer en mode visualisation
    Ajouter une mesure
    Appartements = max(‘Sejour (4)'[Nombre])

    Bonne soirée.

  • Auteur
    Articles
10 sujets de 1 à 10 (sur un total de 10)
  • Vous devez être connecté pour répondre à ce sujet.