Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Julbak
Frequent Visitor

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

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: 

 

Julbak_0-1715948401462.png

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...

 

1 ACCEPTED SOLUTION
slorin
Super User
Super User

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

 

View solution in original post

3 REPLIES 3
Julbak
Frequent Visitor

Salut Stéphane

 

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

Julbak
Frequent Visitor

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
Super User
Super User

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

 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors