Mapping DOWN and UP entries on the base of a site id and of the min(timestamp)

[NewPBIe]

Hello everybody. 

Table A (ID, timestamp, status) has these entries:

• 01 2024-08-15 07:15:38 DOWN
• 02 2024-08-21 11:15:38 DOWN
• 03 2024-10-07 12:15:39 DOWN

Table B (ID, timestamp, status) has these entries:

• 01 2024-08-21 14:15:39 UP
• 02 2024-08-22 11:15:39 UP
• 04 2024-08-22 11:15:39 UP

Output:

• 01 2024-08-15 07:15:38 DOWN - 01 2024-08-21 14:15:39 UP
• 02 2024-08-21 11:15:38 DOWN - 02 2024-08-22 11:15:39 UP
• 03 2024-10-07 12:15:39 DOWN - 03 today DOWN

Requirements:

• ignore UPs without DOWNs (04 2024-08-22 11:15:39 UP not shown in Output)
• merge "DOWNs" with today timestamp (03 2024-10-07 12:15:39 DOWN - 03 today DOWN)
• merge only the entry that
  • has the same ID
  • has the oldest or min timestamp after each timestamp in the "DOWN" table: 01 2024-08-15 07:15:38 DOWN - 01 2024-08-21 14:15:39 UP, 02 2024-08-21 11:15:38 DOWN - 02 2024-08-22 11:15:39 UP.

[ToddChitt]

As I understand this, it might be difficult to do in Power Query as you need to do some JOINs, but not alwasy EQUALTIY joins. 

What is your source system? Can you write a query at that level? How good are you with T-SQL?

[NewPBIe]

exactly, it would be a "join" with the MIN of all the TableB[timestamp] that are bigger than TableA[timestamp].

My source id a unique csv file with all downs and ups together. 

Unfortunately no T-SQL Skills- only study level SQL

[Anonymous · Accepted solution]

Hi @NewPBIe ,

I think you can try Merge function (Left Outer Table A by [ID]) in Power Query Editor.

For reference: Merge queries overview - Power Query | Microsoft Learn

Then create custom columns by M Query and do some filter.

let
    Source = Table.NestedJoin(#"Table A", {"ID"}, #"Table B", {"ID"}, "Table B", JoinKind.LeftOuter),
    #"Expanded Table B" = Table.ExpandTableColumn(Source, "Table B", {"timestamp", "status"}, {"timestamp.1", "status.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table B", "timestamp B", each if [timestamp.1] = null then DateTime.LocalNow()
else if [timestamp.1] > [timestamp] then [timestamp.1] 
else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [timestamp B] <> null and [timestamp B] <> ""),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "status B", each "UP"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"timestamp.1", "status.1"})
in
    #"Removed Columns"

Result is as below.

You can download my sample file to learn more details.

Best Regards,
Rico Zhou

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