Combining Two Tables with Date & Time

[Anonymous]

Hey there,

I'm new to powerBI.

For my Masterthesis i need to create a PowerBI report.

Therefore i import two different tables.

First Table is manuel created: Has Date & Time of an action 

Second Table is automatic created by a machine: Has Date and Time of the same action, but there can be an difference like 1h between the Time  of those two Tables.

Is there a way to combine them with PowerBI, something like defining a timeslot of an hour around my Time in first Table and then checking if any time value of Second Table fits in it:

If (Time.SecondTable) < (Time.FirstTable +30Min) and (Time.SecondTable) > (Time.FristTable -30min) 

so it checks through the whole table, if there are matches and them combines the data to the right rows?

Sincerely,

Jonas

[sevenhills]

Is your scenario is like this one?

https://stackoverflow.com/questions/48255421/powerquery-powerbi-merge-2-tables-based-on-condition-be...

Sorry to ask, I am trying to understand ...

[Anonymous]

Coilnumber	Ordernumber	Date & Time	Time -30 min	Time +30min
726233	933219	12.11.2021 17:15:00	12.11.2021 16:45:00	12.11.2021 17:45:00
765457	635424	25.11.2021 23:50:00	25.11.2021 23:20:00	26.11.2021 00:20:00
524457	214014	07.01.2022 21:55:00	07.01.2022 21:25:00	07.01.2022 22:25:00

Date & Time	Swapped	Material
12.11.2021 17:20:00	Yes	Steel
25.11.2021 23:45:00	Yes	Hardend
07.01.2022 22:10:00	Yes	Aluminum
23.11.2021 15:10:00	Yes	Steel

Those is a part of the two tables.

I added the two Columns Time-30mins and Time +30mins so i have a time window of 1h, in which the time of the other table should be. 

A result should look like this: I Need the Coilnumber, the Ordernumber, Date & Time, Swapped and Material in my Solution the rest can be deleted but dont needs to.

Coilnumber	Ordernumber	Date & Time	Time -30 min	Time +30min	Time_new	Swapped	Material
726233	933219	12.11.2021 17:15:00	12.11.2021 16:45:00	12.11.2021 17:45:00	12.11.2021 17:20:00	Yes	Steel
765457	635424	25.11.2021 23:50:00	25.11.2021 23:20:00	26.11.2021 00:20:00	25.11.2021 23:45:00	Yes	Hardend
524457	214014	07.01.2022 21:55:00	07.01.2022 21:25:00	07.01.2022 22:25:00	07.01.2022 15:10:00	Yes	Aluminum

[ronrsnfld · Accepted solution]

I suggest (starting with coilNumber/OrderNumber/Date & Time

• add a custom column that has a List of each Time at one minute interval during your time frame (1 hour before to 1 hour after)
• Do an innerJoin between the manual and the machine generated table

M Code

let

//Read in the two tables and set the data types
    Source = Excel.CurrentWorkbook(){[Name="machineTable"]}[Content],
    machineTable = Table.TransformColumnTypes(Source,{{"Date & Time", type datetime}, {"Swapped", type text}, {"Material", type text}},"en-DE"),

    Source2 = Excel.CurrentWorkbook(){[Name="manualTable"]}[Content],
    manualTable = Table.TransformColumnTypes(Source2,
        List.Zip({Table.ColumnNames(Source2),{Int64.Type,Int64.Type,DateTime.Type}}),"en-DE"),

//add a custom column with a list of the hour before to hour after at one minute intervals
// (if times are recorded to the second, could round the times first to the nearest minute,
//  or make the list at one second intervals)
    #"Added Custom" = Table.AddColumn(manualTable, "machineTime", 
        each List.DateTimes([#"Date & Time"]-#duration(0,1,0,0),
        120,
        #duration(0,0,1,0))),

//expand the list of minutes to rows
    #"Expanded allMinutes" = Table.ExpandListColumn(#"Added Custom", "machineTime"),

//Do an inner join with the machine generated table
    #"Join with Machine Table" = Table.NestedJoin(
        #"Expanded allMinutes","machineTime",machineTable,"Date & Time","joined",JoinKind.Inner),

//expand the Swapped and Material Rows from the joined table
//  and delete the machineTime Column
    #"Expanded joined" = Table.ExpandTableColumn(#"Join with Machine Table", "joined", {"Swapped", "Material"}, {"Swapped", "Material"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded joined",{"machineTime"})
in
    #"Removed Columns"

Instead of a Join, you could instead add a filtered table in a custom column, where the filter is set to return anything from the machine table that is in the range of +/- one hour, but I suspect the join method will be faster.  If it is slow, then we can try the filter method

Filter Method Code

let

//Read in the two tables and set the data types
    Source = Excel.CurrentWorkbook(){[Name="machineTable"]}[Content],
    machineTable = Table.TransformColumnTypes(Source,{{"Date & Time", type datetime}, {"Swapped", type text}, {"Material", type text}},"en-DE"),

    Source2 = Excel.CurrentWorkbook(){[Name="manualTable"]}[Content],
    manualTable = Table.TransformColumnTypes(Source2,
        List.Zip({Table.ColumnNames(Source2),{Int64.Type,Int64.Type,DateTime.Type}}),"en-DE"),
    
    #"Added Custom" = Table.AddColumn(manualTable, "Custom", each 
        let 
            tStart = [#"Date & Time"]-#duration(0,1,0,0),
            tEnd = [#"Date & Time"] + #duration(0,1,0,0),
            mt = Table.SelectRows(machineTable, each 
                        ([#"Date & Time"]>=tStart) and ([#"Date & Time"]<=tEnd))
        in 
            mt),
    
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", 
        {"Swapped", "Material"}, {"Custom.Swapped", "Custom.Material"})
in
    #"Expanded Custom"

[Anonymous]

Thank you for your help.

The filter Metho is working for me.

[mahoneypat]

This is possible. To get a specific solution from the community, please provide sample data. The best way would be to make a pbix file with mock data in your two tables and provide a link to it here (from Google Drive, OneDrive, etc.). Or insert two tables into your post (not images).

Pat