Finding closest date and value in Multiple column pairs

Power Query
1

I need help adjusting or modifying this query. In this sample spreadsheet, I have columns that are in paris for instance Change Date, Value, Change Date 1, Previous Value 1, Change Date 2, Previous Value 2,etc. There is also a column called MQL Date.

I am trying to find the value closest to the MQL date by looking at the various Change Date. So in this example spreadsheet, i should see 2023-07-11 16:46 as the Closest Date and AKA103: Therapeutic-2023-All Seg-Vaccines-Email -Nurture 1: Vulnerable populations {PPD} as the closet value.

This is a complex query and where am I going wrong? Thanks in advance

closestcampaignmultiplepairs.txt (3.6 KB)

ClosestCampaignPairColumns - EDNA.pbix (90.7 KB)

2

Answer:

Hi @ysherriff,

Give something like this a go:

let
    dateFields = List.Select( Table.ColumnNames(Source), each Text.Contains(_, "change date", Comparer.OrdinalIgnoreCase)),
    valueFields = List.Select( Table.ColumnNames(Source), each Text.Contains(_, "value", Comparer.OrdinalIgnoreCase)),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZDLasMwEEV/ZfC6IpL8UKyd8uimNBgS0kXIQnWnsUCRjGx1U/rvVWIa2q6zGjgw98ydwyHjohDZQ7Z+UUwwCWrxTDjlOVHWwhZP17lTZOndO9muYGH8iG0HjQ6jw2DcCR59iGf4bJrVVwq6LtOasBKYkMU8IfWkGM0l7DoMusc4mvavY6/b1jgcyPqsjQWyiWGMASHds482afSrReh9H60ejXfDP5sgjAGrZFHdv0lJGAU6l+XlSxPlhPFUEVii+dSP8puQ34QbjMH/dFpiwA+0oN0bJAH0k3boTP/bOGVXwGpZ1AmJGaezyyXZ8fgN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Record ID" = _t, #"Most Recent Martech Campaign Current Value" = _t, #"Most Recent Martech Campaign Change Date" = _t, #"Most Recent Martech Campaign Previous Value (1)" = _t, #"Most Recent Martech Campaign Change Date (1)" = _t, #"Most Recent Martech Campaign Previous Value (2)" = _t, #"Most Recent Martech Campaign Change Date (2)" = _t, #"Most Recent Martech Campaign Previous Value (3)" = _t, #"Most Recent Martech Campaign Change Date (3)" = _t, #"Most Recent Martech Campaign Previous Value (4)" = _t, #"Most Recent Martech Campaign Change Date (4)" = _t, #"MQL Date" = _t]),
    SortRows = Table.Sort(Source, {{"Record ID", Order.Ascending}, {"Most Recent Martech Campaign Change Date", Order.Ascending}}),
    Custom = Table.AddColumn(SortRows, "Custom", each 
        [
            MQL = Date.FromText( [MQL Date] ),
            input = Record.ToList( Record.SelectFields(_, dateFields)),
            toDate = List.Transform( input, (x)=> Date.FromText( Text.BeforeDelimiter( x, " "), [Format = "yyyy-MM-dd"]) ),
            toTable = Table.AddIndexColumn( Table.FromColumns( {toDate}, type table [Date = date]), "i", 0, 1 ),
            index = Table.Sort( toTable, {{each Number.Abs( Duration.Days( [Date] - MQL )), Order.Ascending }} ){0}?[i]?,
            closestDate = try input{index} otherwise null,
            closestValue = try Record.ToList( Record.SelectFields(_, valueFields)){index} otherwise null
        ]),
    Expand = Table.ExpandRecordColumn(Custom, "Custom", {"closestDate", "closestValue"}, {"closestDate", "closestValue"})
in
    Expand

I hope this is helpful

3

Thanks Melissa, with the one row record it worked but when I added more rows it is given me null values.

I have attached the sample file for your review. Thanks for your help.

test.pbix (108.5 KB)

4

Answer:

Hi @ysherriff,

Minor adjustment, the issue was not due to multiple rows but date conversion and null values. Give this a go:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5R1ZbuPI9SoFfY8g7ov+ZMlueKbV1rTs7iST+aDJskwMRQpc3PAEAXKN/OUsOUpOkldkkaziIqutoqRMAA7Sluz3+Pa1Kr/8MlI0Uxv9MFrfzCRFnaIPOBwrkmyOV6sF+ug8JuNZEKD72fgrfhyTn9E8ClPHTdFNFG/hD+2JrE8U+FukTzV7KklotYSPZz/NZAng3T/j2NnhLPXdMfmtHNwab8ZfHNf1Q5yMr7eOH6DxpyxOsxgjeYq+ZEEIf/UYYLSLdlngpH4UJuhv8Ep/B9DmRJYLjNpUMyqMP/40k7Rp8c7wrTK+8qMZ+ZGlAD5zQid4hddxAhTAlyU5DzUCbaJQBMpUVZoICg4dCh+5FH5Ww1cq+PJU0wj8GYVfS6AH/k0QATfCDSq/fAu+alfw3y1iAGZQeMANXfwLA4JKhyS5Yvj115kMGGsEpe5Q+HcpKNd44ccYwKWx8/TkuwT8C46TXGPIH9VIgC3KJP9IBr5YLBli5AoYbIpAmsp6Rcf7GV8BhLfVKnjcN8ZUs1irk4Bhs6slz7BPOIuj0tLmOMYvOEBO6CHyJjsnTsHekmd/x5JilJhllcXc+ejlb0vwq6Nff8jdil6K0KytUm1xdxX7YTq+3e5inBRSy3+DSnVJ3rh8Kc7VWGd0NUKp0iayVnobWeoVsjmskHn1OvIxJ2ZBEasOBrUEu3YZEhjvxk+dYLKY37PGAD+idRa/4Fc0d7Y7x9+ECD7fORt8SXoghpw+95pD196E7odOnGNYYBdvH3GMNPQn+LXfEkS/bXgSXTgB3cCbGmwMrMHmkRosSwCKQJM5vTXhKw8HOMXeJaU7xAdpfGzM/4MQ8jRe3M6Ab2EGsQri1BNwMnQxeljPOmKVOVWsc8mMCZLHPwDVbEchq4jwlgHCubt5GFcMu1mvWBW/Db0sSeNXSCQwvH2Md1Gcdocemw8Ecn+Gsr6+G9/FGyeExCTBTuw+9+cnahUHrKnCoZDYHIi+dmX58DO6J6+csJDUApI9VWur+JHoqM6zQQaRhc1kBz6CbIqmOc7OefQDP33NhZq8uD15LJdPHclt3iPWGbJIdjfSQV3k67N5oDSgAHhEfOJst3WGgKeGymgNVSTIOYMg+oYedp0+QtU4jTQ7NJKChqQnwFugIPdj6CN2vLe8zjt0nIOknitr2psaW7Sq4f2RXSiCYjD0fliueusm+l1XvXGOWFQWamCX1iBuEFBQ50XMkqu+RfCsz7WIeqyJ3C6FLKm0/ikqitYuLuWWH/hhXmJ6RA+jHbEj1NWjYGWvS5wkdJ3Bon6nLH6WAWSJxJhYBQ6DOsjajQkjRJ9UMYTJfcU+FQ5WJjIlRWeUqqswJ5+BI45iJ41iHydog4mJBG9ZZEMqA6cJoinpK0pOFYLFEsM1ZnqyXfCh6nDs7EbzvqcGxuqzMuqtU85eIxuHV7F3wLjr0NtFfpgm6BP+1l3G2lQpO6O+PWzUl44VICfMzqrTUuGrP6/zEq+/y5TXejnLv36+vSLdw7LU63RI8hnVQAAt5sQoqxqGFLGPQYXBhwqt8Aga7xFm687kY/fsxFvHjV5AzwOHEHAhqVt/K3Pw0KTZezi3OoRh3ExDYiOFQKmcO+gdzyYu0vX3dXR1SA9pTnX7HfZJs00+qumjKuHs435vunm43Z0oQRNExLnbI8Lo4NIyszecKwMr67HBRKdBg9dcY0ihN3zTSYakJxa7PqjYLbZ/cfyjTrS2BpiDpg0nry//SIFWIClKZf1Enet1i+MfBjSrVnSiIVuQKNYNlc5e9fp6WfR6yT+ABC8ny9nUlNQS0cvsUOEayZLR0ZjN4YLdbSAXLQVB7M7zk13gvKIYp068wTkXv7NjKw3s7CWBVl888O4diQvt85p2XfTIfasls7zT7yQJsC7wk3S8vLt5eMvoz1Mr6Oa+5iLYJlpHru8EtZnmPzbNtLPHqMmsuxHAOb2iQ6bASwdgawcBr4YvpL2Tj19w/OK7wNP7w/BYOouno2c978OAohfyL/ytH1XRHa/CswE0XQXRZrwvSG8y38PoKYrR/MM9SnPdIagf4Q87kRTNjgqJqRROB2J7Lw4vzjYkCUCf/5pJkmIsijFZQudk46seghTaGquQWcoBFM2fnSDA4YawbEfgZ2HBzQp7H20K9TZ1z7HFwQ6JPUVZjJw0jf3HLCVIn9B8Oc/18GBEg4iqhcWUD8DyhrC+A5t+ALZVjD3fLbwNoayN/XB8RhNfY0g7j7aPkJFCCLpZ3+W2RSLhNvIgXPRjYRYmCRa7Tnprb/fZiXGOIoZ/kKCHnQT+l/ggNsYeAl9vqXiHyl0XzPmIQN/ymHrQ69efM3HJLkdRmtqccffs/kQ7UDwiMSfwfyfIPTdNkB+i6wd0GYmpGFr+/a92r6kZkwRz7eyFvGDGAa5i84T051Uxuz1q2fVllVgumKRZFY/krje/jx0Pj2fz+3wLIYFUyUn/849/JuiT39MjP3cvVgxF+8or8Ttj4l6bhd+/RzjourOACqFzD81WRuKnihejuSedPp+m2XXy+fPQ25YCNNuaqHJbs1UqGJJu0hduFYa5h1kvyBZ8iiFcrYpXzZOzKM62l1nsCqEI0jCpzFt0c6ANF7NrbmlrlA5DeYOO+fIOrbPt1k+RakiX51+OIaI+X6FOVQ6kqvd7kibEh3X3WmT/1GLQRpbVaJwLVpzy6I4FEltHQZYLqeA82y3NmXS7Xt19RldRkkYhWkINxDR3LsqajyVGr4gh27bKQJYMWJS2QMrDM/UMRjb621ZfIEv3nBR7aJZ3xj76Sdo0Z4sN3qTXtuS2kg/st3XO5xWuUCettvvDYPe22zrRyEaF5mjW7DnieDxv+DVtrnQVyhweT72wDdxhsyapH0fv+UO7cVTjlOcPpfcsE3SvVdl0JEeGGvVB4f0b+N+xfXumlvzw+77H8qtawLXYab5Al9n2mNZooKz07CdPB85KT7knpL3HtPue7lSGzuFOsUAkrDDdcwZi6HUSsY9Gkxm2YNSlk52GOL195hiVPRjz0QHFBnLEdK7zir7RY1i1L1ZrA61bE7nDN/bOYA9QtW6XbzUVTZB0NDogz0mRBKpaDZhVL7m0R/ZuikbMqhuSLnPWLqTivzi1EkNOteMBXkUWKYe3vLAuke4nSdLrxRvZ6lbc9prrVRy5z0DHiE9IFaUvIdX1gRNS8TsklIiupFWXSIPtAk92kA6KvKdLmucis9lqjT5dzbu7J/2t0EFFKDzxaF13oUv0zMAw587/qOcEZIh0+4f6ZIQfZWkSZbFLslpmnN8zwOWnFEOOivlETdx+AtdbqZseos/Zn34Nc8Bz9uK4z8Yc3RhAWzkEA6wbdGNitBXEbbIH+jtwUDdCDugVQA+Y4B5yYdVQotLYsmmY/nudBIDDbG1/65JOUx7LZDpvPGXV5QZroiC4fW3PxcT6YwnZc8eRLLF3HHWMWnPgGvrqvCYojdB1mBBS1pnr4iRB87svt4uxbBNkzQyx/zatgdMLXcxwpicfNEYCx3rn7mYJHus1BT30IRKRB1G7SydzJOp2znOKmsw6WlffNPz8GmocN99vhEoQxf7mOUWN0Plt15kVNUZB773lUi9TOXnIFydbcl03yIi5JpL3bCZXn9XDTirW++g3HPq/Fzf1NDtQbDCX7QMt7EiERpXP6drALvr7inh7orVN0xJmmqe/vUflvDC3FtikoDhF5MdJyqT5aOZ9jWIvQcssSH1yUOGg9rXItSqu+NIE2NFgdwOB7rQTxPImKCFX6P6fNQqE3Tt81vpXEBXcmf/+nPdyj1GXcuDsQyZjInJtiL1Hfa9DL3Jj2mpczNhrQ7gxx+nvQbHKoTTna0XRU7tykLlmCExC2ceosLByKS/OGuLAzxmvNctxKK0VsbVPDnkVL4tWIGQcpnyLu/wQOUk5b4fS1wFDCTdkjWwxZ4uWymfqfOfTVA856/b5rrRD5GVxcdIo9PAWqD6wZTiI2PahIwecuBZWB7q3DqG9dWBmKLrkaktCZTMM8fJiEcnvN+caDGuwe24GO2emQHo/TARsr6Xl1pXfBvXFB1yQBNbucMS3evZcnD70cRGRk6Tuho+sjv7XLsH5w2ywqNSz8alJuVgvSV3lzO2ialte/QV09sX38iO8Cz9xs/z/DqC7Yd470OZ79SKx2vQ4ymy4DpmYVmhDJqxIfv0v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Record ID" = _t, #"Most Recent Martech Campaign Current Value" = _t, #"Most Recent Martech Campaign Change Date" = _t, #"Most Recent Martech Campaign Previous Value (1)" = _t, #"Most Recent Martech Campaign Change Date (1)" = _t, #"Most Recent Martech Campaign Previous Value (2)" = _t, #"Most Recent Martech Campaign Change Date (2)" = _t, #"Most Recent Martech Campaign Previous Value (3)" = _t, #"Most Recent Martech Campaign Change Date (3)" = _t, #"Most Recent Martech Campaign Previous Value (4)" = _t, #"Most Recent Martech Campaign Change Date (4)" = _t, #"Most Recent Martech Campaign Previous Value (5)" = _t, #"Most Recent Martech Campaign Change Date (5)" = _t, #"Most Recent Martech Campaign Previous Value (6)" = _t, #"Most Recent Martech Campaign Change Date (6)" = _t, #"Most Recent Martech Campaign Previous Value (7)" = _t, #"Most Recent Martech Campaign Change Date (7)" = _t, #"Most Recent Martech Campaign Previous Value (8)" = _t, #"Most Recent Martech Campaign Change Date (8)" = _t, #"Most Recent Martech Campaign Previous Value (9)" = _t, #"Most Recent Martech Campaign Change Date (9)" = _t, #"Most Recent Martech Campaign Previous Value (10)" = _t, #"Most Recent Martech Campaign Change Date (10)" = _t, #"Most Recent Martech Campaign Previous Value (11)" = _t, #"Most Recent Martech Campaign Change Date (11)" = _t, #"Most Recent Martech Campaign Previous Value (12)" = _t, #"Most Recent Martech Campaign Change Date (12)" = _t, #"Most Recent Martech Campaign Previous Value (13)" = _t, #"Most Recent Martech Campaign Change Date (13)" = _t, #"Most Recent Martech Campaign Previous Value (14)" = _t, #"Most Recent Martech Campaign Change Date (14)" = _t, #"Most Recent Martech Campaign Previous Value (15)" = _t, #"Most Recent Martech Campaign Change Date (15)" = _t, #"MQL Date" = _t]),

    // Identify date fields and value fields
    dateFields = List.Select( Table.ColumnNames(Source), each Text.Contains(_, "change date", Comparer.OrdinalIgnoreCase)),
    valueFields = List.Select( Table.ColumnNames(Source), each Text.Contains(_, "value", Comparer.OrdinalIgnoreCase)),

    // Sort the table by 'Record ID' and 'Most Recent Martech Campaign Change Date'
    SortRows = Table.Sort(Source, {{"Record ID", Order.Ascending}, {"Most Recent Martech Campaign Change Date", Order.Ascending}}),

    // Custom transformation to find closest dates and corresponding values
    Custom = Table.AddColumn(SortRows, "Custom", each 
        [
            MQL = Date.FromText(Text.BeforeDelimiter([MQL Date], " ")),
            input = Record.ToList(Record.SelectFields(_, dateFields)),
            toDate = List.Transform(input, (x)=> Date.FromText(Text.BeforeDelimiter(x, " "))),
            toTable = Table.SelectRows( Table.AddIndexColumn(Table.FromColumns({toDate}, type table [Date = date]), "i", 0, 1), each [Date] <> null ),
            index = Table.Sort(toTable, {{each Number.Abs(Duration.Days([Date] - MQL)), Order.Ascending}}){0}?[i]?,
            closestDate = try input{index} otherwise null,
            closestValue = try Record.ToList(Record.SelectFields(_, valueFields)){index} otherwise null
        ]),
    Expand = Table.ExpandRecordColumn(Custom, "Custom", {"closestDate", "closestValue"}, {"closestDate", "closestValue"})
in
    Expand

I hope this is helpful

5

Thank you Melissa. Very helpful and what was needed.