Split a line into 2 lines in Power Query according to conditions

[Julbak]

Hi the Community

I am very struggling for several months with a condition that I am not able to solve by myself.

Indeed, I am working in Production area so i need to create KPI. Today the "Production Date" starts at 5:00 AM and finish the next day at 5:00AM.

Example: 

Production date 06/05/2024: Starts at 06/05/2024 05:00 AM and finishes at 07/05/2024 05:00 AM

I have decided in my reports to define my results for the production date based on the Production Date. 

I have then created a new column in DAX to define according to each declaration the Production date to fit within this range. 

My problem is that sometimes I can have production stops that are aligned on 2 production days as for example: 

Line 3: The stop starts at 3:53:42 (07/05) in the morning and finishes at 06:36:41 (07/05)

Then the beginning of the stop is on the 06/05 Production date and the End on the 07/05 Production date. It means that I have to input the totality of the stop only on one Production Date as actually set in my database. But my daily reports will not be true...

I have managed till now to create manually on my source two different stops for one stop divided like this:

- Part 1 of the stop: "07/05/2024 03:53:42" to "07/05/2024 04:59:59" => Production date 06/05

- Part 2 of the stop: "07/05/2024 05:00:00" to "07/05/2024 06:36:31" => Production date 07/05

It worked but as now my reports are automatically generated, everything is false...

Then my question is how can I manage this in Power Query? I am able to use DAX but Power Query is black hole for me as I am not Data Engineer... The idea is to get a Power Query method to:

1) Detect this kind of stop (based on if COL ("Debut") is < 05:00 and COL (FIN) > 05:00)

2) Then add two new lines based on the example above (Part 1/Part 2) with all the same columns information

3) Then delete the duplicated line to create these two new lines.

Thanks a lot in advance for your help...

[Julbak]

Salut Stéphane

Le programme fonctionne parfaitement avec un peu de modification dans ma BDD, merci encore !

[Julbak]

Salut Stéphane, 

Merci beaucoup pour ta réponse et désolé pour la tardive de mon côté.

J'ai regardé je vais essayer quand j'aurai le temps mais à première vue je vois l'idée du code. Je pourrai l'adapter au besoin dans ma BDD.

Je note comme la solution car comme je suis un newbie en langage M, cela me donne une très bonne base pour le faire

Merci encore

Julien

[slorin · Accepted solution]

Bonjour @Julbak 

Une possibilité est de filtrer les lignes concernées,

de remplacer une première fois la date heure de début par la même date mais à 5:00,

de remplacer une seconde fois la date heure de fin par la même date mais à 4:49:59

et de recombiner le tout avec les lignes de départ qui ne sont pas concernées par le critère.

EDIT : utilisation de Table.TransformColumns au lieu de Table.ReplaceValue

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnfyVtJRcs1TSDm8Mi2xtATIMTDXNzDVNzIwMlEwMLQytEQTMgYJxerAtPok5iWn5qbmlSikpCrkZKbnpaKqNzS1MjTHEDIyRjLC5fDKktTkksz8PIVcIDMxB8NGU2M0ITMrYzOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Code Declaration" = _t, #"Description Declaration" = _t, Debut = _t, Fin = _t]),
Type_date = Table.TransformColumnTypes(Source,{{"Debut", type datetime}, {"Fin", type datetime}}),
Hors_Filtre = Table.SelectRows(Type_date, each not(Time.From([Debut]) < #time(5,0,0) and Time.From([Fin]) > #time(5,0,0) )),
Filtre = Table.SelectRows(Type_date, each Time.From([Debut]) < #time(5,0,0) and Time.From([Fin]) > #time(5,0,0) ),
Modif_Debut = Table.TransformColumns(Filtre,{{"Debut", each DateTime.From(Date.From(_))+#duration(0,5,0,0), type datetime}}),
Modif_Fin = Table.TransformColumns(Filtre,{{"Fin", each DateTime.From(Date.From(_))+#duration(0,4,59,59), type datetime}}),
Table_Finale = Table.Combine({Hors_Filtre, Modif_Fin, Modif_Debut})
in
Table_Finale

Stéphane