Répartir une durée en plusieurs lignes

Accueil – Le CFO masqué Forums Power Query Répartir une durée en plusieurs lignes

  • Ce sujet contient 5 réponses, 2 participants et a été mis à jour pour la dernière fois par ismael.ezzehraoui, le il y a 5 mois et 3 semaines.
6 sujets de 1 à 6 (sur un total de 6)
  • Auteur
    Articles
  • #135110
    ismael.ezzehraoui
    Participant

    Bonjour je rencontre un problème depuis quelques semaines sur power query.

    Contexte
    J’ai à l’origine 4 colonnes qui sont les suivantes :

    TimeFromAjusté (il s’agit de la date de début de l’action en Date et heure)
    TimeToAjusté (il s’agit de la date de fin de l’action en Date et heure)
    Shift (il s’agit du créneau horaire dans lequel se déroule l’action “la valeur Matin appartient au créneau 5h à 13h ; la valeur Après-midi appartient au créneau 13h à 21h ; la valeur Nuit appartient au créneau 21h à 5h”)
    TR (il s’agit de la durée en secondes de mon action)

    Problématique
    Actuellement la colonne shift ne se base que sur la date de début. Ainsi si la date de fin est dans un créneau horaire différent (jour y compris) de la date de début, la durée de mon action ne sera pas répartie suivant tous les créneaux horaires dans lesquels elle s’est déroulée.
    Ps: information complémentaire mon changement de jour s’effectue à 5h et non à 0h pour me simplifier la tâche

    J’essaye donc de passer de la situation initiale ci dessous à la situation visée

    Situation initiale :

    | TimeFromAjusté | TimeToAjusté | Shift | TR |
    |———————|———————|——-|——–|
    | 30/06/2023 04:00:00 | 01/07/2023 14:00:00 | Nuit | 36000 |

    Situation visée :

    | TimeFromV2 | TimeToV2 | ShiftV2 | TR |
    |———————|———————|————|——-|
    | 30/06/2023 04:00:00 | 01/07/2023 05:00:00 | Nuit | 3600 |
    | 01/07/2023 05:00:00 | 01/07/2023 13:00:00 | Matin | 28800 |
    | 01/07/2023 13:00:00 | 01/07/2023 14:00:00 | Après-midi | 3600 |

    J’espère vous avoir donner une bonne visualisation du problème, merci à ceux qui prendront le temps de lire et de me transmettre leurs suggestions

    #135136
    Xavier Allart
    Participant

    bonjour
    voici une propositon
    J’ai ajouter une table pour avoir par Shift (Type) les heures de Début et de Fin de chaque.
    Dans cette table j’ai découpé la nuit en 2
    Type H_Début H_Fin
    1.Soir 00:00 05:00
    2.Matin 05:00 13:00
    3.Apres-Midi 13:00 21:00
    4.Soir 21:00 23:59

    Le code
    ajoute un N° d’index a chaque action
    decompose chaque action en autant de lignes qu’il y a de tranches horaires
    Associe à chaque tranche horaire le shift (Type) correspondant
    Regroupe les actions, par date et shift, pour calculer le TR correspondant

    let
    // Étape 1 : Charger les données depuis la feuille Excel appelée “Donnée”
    Source = Excel.CurrentWorkbook(){[Name=”Donnée”]}[Content],

    // Étape 2 : Modifier le type de colonne pour les colonnes “Début” et “Fin” en type datetime
    #”Type modifié” = Table.TransformColumnTypes(Source,{{“Début”, type datetime}, {“Fin”, type datetime}}),

    // Étape 3 : Ajouter une colonne d’index “Id”, pour identifier chaque action
    #”Id ajouté” = Table.AddIndexColumn(#”Type modifié”, “Id”, 1, 1, Int64.Type),

    // Décomposer une action, en autant de lignes qu’elle a de tranches horaires
    // Étape 4 : Calcul la durée entre “Début” et “Fin”
    #”DaysHours ajoutée” = Table.AddColumn(#”Id ajouté”, “Nb.TranchesHoraires”, each Duration.ToRecord([Fin]-[Début])),

    // Étape 5 : Étendre la colonne de durée en colonnes distinctes “Days” et “Hours”
    #”Nb.tranchesHoraires développé” = Table.ExpandRecordColumn(#”DaysHours ajoutée”, “Nb.TranchesHoraires”, {“Days”, “Hours”}, {“Days”, “Hours”}),

    // Étape 6 : Calculer le nombre total d’heures en ajoutant les jours convertis en heures et les heures
    Nb.TranchesHoraires = Table.AddColumn(#”Nb.tranchesHoraires développé”, “Nb.TranchesHoraires”, each [Days]*24+[Hours]),

    // Étape 7 : Supprimer les colonnes inutiles
    #”Colonnes supprimées” = Table.RemoveColumns(Nb.TranchesHoraires,{“Days”, “Hours”}),

    // Étape 8 : Extraire l’heure du début au format “hh”
    #”Texte inséré entre les délimiteurs” = Table.AddColumn(#”Colonnes supprimées”, “Debut.hh”, each Value.FromText(Text.BetweenDelimiters(Text.From([Début], “fr-FR”), ” “, “:”))),

    // Étape 9 : Créer une liste de tranches horaires basée sur l’heure de début et le nombre total d’heures
    List.TranchesHoraires = Table.AddColumn(#”Texte inséré entre les délimiteurs”, “List.TranchesHoraires”, each List.Durations(#duration(0, [Debut.hh], 0, 0), [Nb.TranchesHoraires], #duration(0, 1, 0, 0))),

    // Étape 10 : Étendre la liste de tranches horaires en plusieurs lignes
    #”List.TranchesHoraires développé” = Table.ExpandListColumn(List.TranchesHoraires, “List.TranchesHoraires”),

    // Étape 11 : Supprimer la colonne “Debut.hh”
    #”Colonnes supprimées1″ = Table.RemoveColumns(#”List.TranchesHoraires développé”,{“Debut.hh”}),

    // Étape 12 : Ajouter une colonne avec la date du début sans les heures
    #”Date insérée” = Table.AddColumn(#”Colonnes supprimées1″, “Debut.Date”, each Date.From([Début]), type date),

    // Étape 13 : Modifier le type de colonne “Debut.Date” en type datetime
    #”Type modifié1″ = Table.TransformColumnTypes(#”Date insérée”,{{“Debut.Date”, type datetime}}),

    // Étape 14 : Ajouter une colonne avec la date et l’heure de début de chaque tranche
    Debut.Tranche = Table.AddColumn(#”Type modifié1″, “Debut.Tranche”, each [Debut.Date]+[List.TranchesHoraires]),

    // Étape 15 : Sélectionner les colonnes nécessaires
    #”Autres colonnes supprimées” = Table.SelectColumns(Debut.Tranche,{“Id”, “Fin”, “Debut.Tranche”}),

    // Associer chaque tranche horaire à son Shift (Type)
    // Étape 16 : Fractionner la colonne “Debut.Tranche” en colonnes “Debut.Date” et “Debut.Heure”
    #”Fractionner la colonne par délimiteur” = Table.SplitColumn(Table.TransformColumnTypes(#”Autres colonnes supprimées”, {{“Debut.Tranche”, type text}}, “fr-FR”), “Debut.Tranche”, Splitter.SplitTextByDelimiter(” “, QuoteStyle.Csv), {“Debut.Date”, “Debut.Heure”}),

    // Étape 17 : Modifier le type des colonnes “Debut.Date” et “Debut.Heure”
    #”Type modifié3″ = Table.TransformColumnTypes(#”Fractionner la colonne par délimiteur”,{{“Debut.Date”, type date}, {“Debut.Heure”, type time}}),

    // Étape 18 : Fusionner les tables en utilisant une jointure affichant toutes les lignes des 2 tables
    #”Requêtes fusionnées” = Table.NestedJoin(#”Type modifié3″, {“Debut.Heure”}, Table_Shift, {“H_Début”}, “Table_Shift”, JoinKind.FullOuter),

    // Étape 19 : Étendre la table résultante
    #”Table_Shift développé” = Table.ExpandTableColumn(#”Requêtes fusionnées”, “Table_Shift”, {“Type”, “H_Fin”}, {“Type”, “H_Fin”}),

    // Étape 20 : Trier les lignes en fonction de l’heure de début
    #”Lignes triées” = Table.Sort(#”Table_Shift développé”,{{“Debut.Heure”, Order.Ascending}}),

    // Étape 21 : Remplir les valeurs manquantes vers le bas pour Type et H_Fin
    #”Rempli vers le bas” = Table.FillDown(#”Lignes triées”,{“Type”, “H_Fin”}),

    // Calculer la durée en secondes par Shift (Type)
    // Étape 22 : Ajouter une colonne TR avec la valeur 3600
    #”TR ajoutée” = Table.AddColumn(#”Rempli vers le bas”, “TR”, each 3600),

    // Étape 23 : Ajouter une colonne avec la date et l’heure de fin de la tranche
    #”Personnalisée ajoutée” = Table.AddColumn(#”TR ajoutée”, “Personnalisé”, each DateTime.FromText(Date.ToText([Debut.Date])&” “&Time.ToText([H_Fin]))),

    // Étape 24 : Ajouter une colonne conditionnelle pour déterminer la heure de fin
    #”Colonne conditionnelle ajoutée” = Table.AddColumn(#”Personnalisée ajoutée”, “Fin.Date”, each if [Personnalisé] > [Fin] then Time.From([Fin]) else [H_Fin]),

    // Étape 25 : Regrouper les actons en fonction de leur Id, de la date de début et du type
    #”Lignes groupées” = Table.Group(#”Colonne conditionnelle ajoutée”, {“Id”, “Debut.Date”, “Type”}, {{“Debut”, each List.Min([Debut.Heure]), type nullable time}, {“Fin”, each List.Max([Fin.Date]), type any}, {“TR”, each List.Sum([TR]), type number}, {“NB”, each Table.RowCount(_), Int64.Type}}),

    // Étape 26 : Trier les lignes groupées
    #”Lignes triées1″ = Table.Sort(#”Lignes groupées”,{{“Id”, Order.Ascending}, {“Debut.Date”, Order.Ascending}, {“Type”, Order.Ascending}})
    in
    #”Lignes triées1″

    cordialement

    Attachments:
    You must be logged in to view attached files.
    #135146
    ismael.ezzehraoui
    Participant

    Bonjour Xavier,

    Tout d’abord merci pour votre temps et votre aide.
    J’ai oublié de préciser dans mon premier post que je travaille sur Power BI et non Excel, cela a peut être une incidence notamment sur la table que vous avez créé sur le Shift ?

    Je voulais aussi revenir sur le cas du shift “nuit”, j’ai observé que vous séparez celui en 2 parties. J’ai personnellement modifié le changement de jour à l’horaire 5h au lieu de 0h afin de ne plus avoir le shift “nuit” qui coupe 2 jours distincts.

    Je vous partage également une capture d’écran de la situation où je me trouve actuellement.
    Je peux vous partager le fichier source si vous le souhaitez, je retirerai ainsi les informations confidentielles.

    Je vous remercie encore pour le temps que vous avez pris, et je m’excuse si je n’ai pas été clair.
    Le problème est assez difficile pour moi, et comme je suis novice sur Power Query et Power BI je ne suis pas toujours très explicite.

    Bien cordialement

    Attachments:
    You must be logged in to view attached files.
    #135150
    ismael.ezzehraoui
    Participant

    C’est de nouveau moi,

    Je m’excuse je pense raconter n’importe quoi à propos du changement de jour à 5 heures cela ne fonctionnera pas. Vous aviez bien raison de séparer le shift Nuit en 2 parties.

    #135190
    Xavier Allart
    Participant

    Bonjour

    Power Query pour Excel et Power BI, c’est identique.
    Pour la table soit vous l’enregistrer dans un classeur Excel, ou un Fichier csv et l’importé dans votre modele Power BI, soit directement la créer dans Power BI : Accueil > Créer une table, copier/coller depuis Excel, nommer la table et la charger.

    Cordialement

    #135193
    ismael.ezzehraoui
    Participant

    Bonjour,

    Parfait je vous remercie Xavier, ça fonctionne bien.

    Bonne journée,
    IE

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