Forum Discussion

Topaz_Installer's avatar
Topaz_Installer
Regular Visitor
3 years ago
Solved

Nearest match from TABLE1 to Table2

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.

Table1Table2
10:00:0010:00:00
10:00:0510:00:01
10:00:1010:00:06
10:00:1510:00:10
 10:00:16

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

Table1Table2
10:00:0010:00:00
10:00:05null
10:00:1010:00:10
10:00:15null

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

Table1Table2
10:00:0010:00:00
10:00:0510:00:06
10:00:1010:00:10
10:00:1510:00:16

Please advise how to do this task in power query

Need Help
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    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's avatar
    ThxAlot
    3 years ago

     

    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;)))

     

3 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    3 years ago

    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's avatar
      Topaz_Installer
      Regular Visitor
      3 years ago

      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

  • ThxAlot's avatar
    ThxAlot
    Super User
    3 years ago

     

    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;)))