Help to split some datetime rows on my table

[andreazambon]

Hi there.

I have a table on mi PBI where I have 2 key columns: event_datetime and event_end_datetime. Here a sample:

What I need to do is to split the rows in a way that let me to have the events start and end in the same day. So if I have an event with day(event_daytime) <> day(event_end_datetime) I have to:

-close the event at day(event_daytime) 23:59:59;

-open a copy of the row with event_datetime = day(event_daytime of the original row) 00:00:00 and event_end_daytime = vent_end_daytime of the original row.

If there are more then 1 day of difference, I need multiple rows with duration 1440 minutes. 

I give you some sample of what I need:

CASE 1: 1 day of difference:

AS-IS
event_datetime	event_end_datetime
18/10/2022 09:01	19/10/2022 23:05
TO-BE
18/10/2022 09:01	18/10/2022 23:59
19/10/2022 00:00	19/10/2022 23:05

CASE 2: more days of difference:

AS-IS
event_datetime	event_end_datetime
18/10/2022 09:01	22/10/2022 23:05
TO-BE
18/10/2022 09:01	18/10/2022 23:59
19/10/2022 00:00	19/10/2022 23:59
20/10/2022 00:00	20/10/2022 23:59
21/10/2022 00:00	21/10/2022 23:59
22/10/2022 00:00	22/10/2022 23:05

All others table values have to be copied. 

Do you think that the Unpivot can help me to handle this?

[Anonymous]

Hi @andreazambon ,

It's a bit complicated, but I made it. The solution is done in Power Query, because in my opinion, it is more difficult to add rows in Desktop.

Input:

Output:

The whole M codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTQNzTQNzIwMlIwsLQyMFTSUTIyggsZGVsZmAKFHJVidbCqNbTEUOukFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [event_datetime = _t, event_end_datetime = _t, Category = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"event_datetime", type datetime}, {"event_end_datetime", type datetime}}, "en-GB"),
    #"Inserted Date" = Table.AddColumn(#"Changed Type with Locale", "Date", each DateTime.Date([event_datetime]), type date),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Date",{{"Date", "StartDate"}}),
    #"Inserted Date1" = Table.AddColumn(#"Renamed Columns", "Date", each DateTime.Date([event_end_datetime]), type date),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Date1",{{"Date", "EndDate"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Date", each { Number.From([StartDate])..Number.From([EndDate]) }),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type datetime}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"event_datetime", "event_end_datetime", "Date", "Category", "StartDate", "EndDate"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Category"}, {{"AllRows", each _, type table [event_datetime=nullable datetime, event_end_datetime=nullable datetime, Date=nullable datetime, Category=nullable text, StartDate=date, EndDate=date]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([AllRows],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"AllRows"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom.1", each Table.Max([Custom],"Index")),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.2", each Table.Min([Custom],"Index")),
    #"Expanded Custom.2" = Table.ExpandRecordColumn(#"Added Custom3", "Custom.2", {"Index"}, {"Index"}),
    #"Expanded Custom.1" = Table.ExpandRecordColumn(#"Expanded Custom.2", "Custom.1", {"Index"}, {"Index.1"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded Custom.1",{{"Index.1", "MaxIndex"}, {"Index", "MinIndex"}}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Renamed Columns2", "Custom", {"event_datetime", "event_end_datetime", "Date", "StartDate", "EndDate", "Index"}, {"event_datetime", "event_end_datetime", "Date", "StartDate", "EndDate", "Index"}),
    #"Added Custom4" = Table.AddColumn(#"Expanded Custom", "StartDateTime", each if [Index]=[MinIndex] then [event_datetime] else  [Date]),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "EndDateTime", each if [Index]=[MaxIndex] then [event_end_datetime] else [Date]+#duration(0,23,59,0)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom5",{"event_datetime", "event_end_datetime", "Date", "StartDate", "EndDate", "Index", "MaxIndex", "MinIndex"})
in
    #"Removed Columns1"

The general idea is to fill  dates based on start and end dates.

Below is my reference:

How to Fill Dates Between Start Date and End Date in Power BI (Power Query) - Stuff by Yuki

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[andreazambon]

@Anonymous 

I think that the key rows are these:

    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([AllRows],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"AllRows"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom.1", each Table.Max([Custom],"Index")),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.2", each Table.Min([Custom],"Index"))

I think that I can't understand those elements very well, can you please explain what they do?

Thanks.

[andreazambon]

Ok, I get your changes but Something is missing. 

My fault, I forgot to say that the most of my rows is in the condition date(event_datetime)=date(event_end_datetime), in this case I don't have to do nothing because my row's format is already setted as I desire.

SO i setted the category like this:

= Table.AddColumn(#"Modificato tipo1", "Categoria", each if([Delta_days]=0) then "A" else 
if([Delta_days]=1) then "B" else 
if([Delta_days]>1) then "C" else "D")

where delta_days is 

= Table.AddColumn(#"crea event_end_date", "Delta_days", each [event_end_date]-[event_date])

and then I follow your steps. 

Results: 

all the "A" category rows are wrong, I have this situation:

In "B" category seems that I have ALWAYS the 23:59:59 time as EndDatatime.

In "C" category I have the same thing, seems the same problem of the A case:

Here the whole code:

let
    Origine = PowerBI.Dataflows(null),
    #"9fcad8d6-d0e8-43eb-bfbe-78504deed8ce" = Origine{[workspaceId="9fcad8d6-d0e8-43eb-bfbe-78504deed8ce"]}[Data],
    #"fd9b00fb-f97c-4f82-ab46-f813a00658c3" = #"9fcad8d6-d0e8-43eb-bfbe-78504deed8ce"{[dataflowId="fd9b00fb-f97c-4f82-ab46-f813a00658c3"]}[Data],
    #"SAPProdEvent-1" = #"fd9b00fb-f97c-4f82-ab46-f813a00658c3"{[entity="SAPProdEvent-2"]}[Data],
    #"crea duration_min" = Table.AddColumn(#"SAPProdEvent-1", "duration_min", each [duration_s]/60, type number),
    #"crea event_end_date" = Table.AddColumn(#"crea duration_min", "event_end_date", each DateTime.Date([event_end_datetime]),type date),
    #"crea Delta_days" = Table.AddColumn(#"crea event_end_date", "Delta_days", each [event_end_date]-[event_date]),
    #"Modificato tipo1" = Table.TransformColumnTypes(#"crea Delta_days",{{"Delta_days", Int64.Type}}),
    #"Crea categoria" = Table.AddColumn(#"Modificato tipo1", "Categoria", each if([Delta_days]=0) then "A" else 
if([Delta_days]=1) then "B" else 
if([Delta_days]>1) then "C" else "D"),
    #"Modificato tipo2" = Table.TransformColumnTypes(#"Crea categoria",{{"Categoria", type text}}),
    #"Crea intervallo date" = Table.AddColumn(#"Modificato tipo2", "Intervallo_date", each {Number.From([event_date])..Number.From([event_end_date])}),
    #"Tabella Intervallo_date espansa" = Table.ExpandListColumn(#"Crea intervallo date", "Intervallo_date"),
    #"Modificato tipo" = Table.TransformColumnTypes(#"Tabella Intervallo_date espansa",{{"Intervallo_date", type datetime}}),
    #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"Categoria"}, {{"Tuttelerighe", each _, type table [event_id=nullable number, prod_order_id=nullable text, prod_order_row=nullable text, event_date=nullable date, event_time=nullable time, operator_code=nullable text, work_center_id=nullable text, event_type=nullable text, event_datetime=nullable datetime, event_end_datetime=nullable datetime, duration_s=nullable number, duration_hrs=nullable number, prod_order_key=nullable text, duration_min=number, event_end_date=date, Delta_days=nullable number, Categoria=text, Intervallo_date=nullable datetime]}}),
    #"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Raggruppate righe", "Personalizzato", each Table.AddIndexColumn([Tuttelerighe],"Index",1,1)),
    #"Rimosse colonne" = Table.RemoveColumns(#"Aggiunta colonna personalizzata1",{"Tuttelerighe"}),
    #"Aggiunta colonna personalizzata2" = Table.AddColumn(#"Rimosse colonne", "Personalizzato.1", each Table.Max([Personalizzato],"Index")),
    #"Aggiunta colonna personalizzata3" = Table.AddColumn(#"Aggiunta colonna personalizzata2", "Personalizzato.2", each Table.Min([Personalizzato],"Index")),
    #"Tabella Personalizzato.2 espansa" = Table.ExpandRecordColumn(#"Aggiunta colonna personalizzata3", "Personalizzato.2", {"Index"}, {"MinIndex"}),
    #"Tabella Personalizzato.1 espansa" = Table.ExpandRecordColumn(#"Tabella Personalizzato.2 espansa", "Personalizzato.1", {"Index"}, {"MaxIndex"}),
    #"Tabella Personalizzato espansa" = Table.ExpandTableColumn(#"Tabella Personalizzato.1 espansa", "Personalizzato", {"event_id", "prod_order_id", "prod_order_row", "event_date", "event_time", "operator_code", "work_center_id", "event_type", "event_datetime", "event_end_datetime", "duration_s", "duration_hrs", "prod_order_key", "duration_min", "event_end_date", "Delta_days", "Intervallo_date", "Index"}, {"event_id", "prod_order_id", "prod_order_row", "event_date", "event_time", "operator_code", "work_center_id", "event_type", "event_datetime", "event_end_datetime", "duration_s", "duration_hrs", "prod_order_key", "duration_min", "event_end_date", "Delta_days", "Intervallo_date", "Index"}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Tabella Personalizzato espansa", "Startdatetime", each if [Index]=[MinIndex] then [event_datetime] else [Intervallo_date]),
    #"Aggiunta colonna personalizzata4" = Table.AddColumn(#"Aggiunta colonna personalizzata", "EndDatetime", each if [Index]=[MaxIndex] then [event_end_datetime] else [Intervallo_date]+#duration(0,23,59,0)),
    #"Riordinate colonne" = Table.ReorderColumns(#"Aggiunta colonna personalizzata4",{"Categoria", "event_id", "prod_order_id", "prod_order_row", "event_date", "event_time", "operator_code", "work_center_id", "event_type", "event_datetime", "event_end_datetime", "Startdatetime", "EndDatetime", "duration_s", "duration_hrs", "prod_order_key", "duration_min", "event_end_date", "Delta_days", "Intervallo_date", "Index", "MaxIndex", "MinIndex"}),
    #"Filtrate righe" = Table.SelectRows(#"Riordinate colonne", each ([Categoria] = "C"))
in
    #"Filtrate righe"

Indeed, I don't understand how the condition Index = MaxIndex can be filled, as in my MaxIndex column I have just 4 results: