Nearest match from TABLE1 to Table2

[Topaz_Installer]

I have two tables and both contains time. I would like match time couln from table1 to time column in the table2. Table1 is fixed and no no need change anything. If I find the same record or match in table2, I can match with merge queries option and I am unable to match the Table1 records with the closest match from Table2. Below the example data.

Table1	Table2
10:00:00	10:00:00
10:00:05	10:00:01
10:00:10	10:00:06
10:00:15	10:00:10
	10:00:16

I tried matching both the tables in power query and I am getting below result

Table1	Table2
10:00:00	10:00:00
10:00:05	null
10:00:10	10:00:10
10:00:15	null

But my goal is match all the records from table1 to the closet match of Table2. I am looking the below result.

Table1	Table2
10:00:00	10:00:00
10:00:05	10:00:06
10:00:10	10:00:10
10:00:15	10:00:16

Please advise how to do this task in power query

[m_dekorte · Accepted solution]

Hi @Topaz_Installer,

You can copy this into a new blank query

let
    Tbl1 = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSwMgAhpVgdOMcUiWOILGMIlIkFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Table1 = _t]),{{"Table1", type time}}),
    Tbl2 = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSwMgAhpVgdOMcQmWOGxDFEVmYIlIkFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Table2 = _t]),{{"Table2", type time}}),
    AddCustom = Table.AddColumn(Tbl1, "Approx Match", each List.First( Table.SelectRows( Tbl2, (x)=> x[Table2] >= [Table1])[Table2] ), type time)
in
    AddCustom

Give this a go for an approx match

Table.AddColumn( Tbl1, "Approx Match", each List.First( Table.SelectRows( Tbl2, (x)=> x[Table2] >= [Table1])[Table2] ), type time)

with this result

Ps. If this helps solve your query please mark this post as Solution, thanks!

[ThxAlot · Accepted solution]

let
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSwMgAhpVgdOMcMiWOILGMIlIkFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t]),
    #"Changed Type Table2" = Table.TransformColumnTypes(Table2,{{"Timestamp", type time}}),
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSwMgAhpVgdOMcUiWOILGMIlIkFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t]),
    #"Changed Type Table1" = Table.TransformColumnTypes(Table1,{{"Timestamp", type time}}),

    #"Added Match" =
        let l = #"Changed Type Table2"[Timestamp]
        in Table.AddColumn(
            #"Changed Type Table1",
            "Match",
            each let dev = List.Transform(l, (ts) => Number.Abs(Number.From(ts - [Timestamp]))), pos = List.PositionOf(dev, List.Min(dev))
                in l{pos}
        )
in
    #"Added Match"

A showcase of powerful Excel worksheet formulas,

=INDEX(Table2[Timestamp];LET(dev;ABS(Table2[Timestamp]-[@Timestamp]);MATCH(MIN(dev);dev;)))

[m_dekorte · Accepted solution]

Hi @Topaz_Installer,

You can copy this into a new blank query

let
    Tbl1 = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSwMgAhpVgdOMcUiWOILGMIlIkFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Table1 = _t]),{{"Table1", type time}}),
    Tbl2 = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSwMgAhpVgdOMcQmWOGxDFEVmYIlIkFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Table2 = _t]),{{"Table2", type time}}),
    AddCustom = Table.AddColumn(Tbl1, "Approx Match", each List.First( Table.SelectRows( Tbl2, (x)=> x[Table2] >= [Table1])[Table2] ), type time)
in
    AddCustom

Give this a go for an approx match

Table.AddColumn( Tbl1, "Approx Match", each List.First( Table.SelectRows( Tbl2, (x)=> x[Table2] >= [Table1])[Table2] ), type time)

with this result

Ps. If this helps solve your query please mark this post as Solution, thanks!

[Topaz_Installer]

Thank you for the help and much appreciated. Please let me know how convert table or csv file to JSON format in power query as when I use table or csv, because of data size loading or refreshing very slow. please guide me how I can make my query loading faster