Erroneous Results from CALCULATE, MIN, FILTER (DAX)

[ivan_larson_cki]

I have a parent table where I need a DAX column to find the earliest date within filtered criteria from a child table. However' I'm sometimes returning results which don't match all of my filters.

The Parent table has a Unique ID by which it's linked to the Child table. The child table has that unique ID, a color, a size, and a Date. I would think that to return the earliest date of an associated child record that's "Green" and "Medium", I could use this formula:

Earliest Green Medium Date = 
CALCULATE(MIN('Dates'[Date].[Date]),
    FILTER('Dates','Dates'[Unique ID]='Accounts'[Unique ID]),
    FILTER('Dates','Dates'[Color]="Green"),
    FILTER('Dates','Dates'[Size]="Medium"))

However, I'm getting results in some cases where there are no "green Medium" records at all (e.g. A-101, A-103, A-107).

To get closer to the truth, I amended my formula to only look at cases where there are child records with "Green" color and "Medium" size:

Earliest Green Medium Date = 
IF(CALCULATE(COUNTROWS('Accounts'),
    FILTER('Dates','Dates'[Unique ID]='Accounts'[Unique ID]),
    FILTER('Dates','Dates'[Color]="Green"),
    FILTER('Dates','Dates'[Size]="Medium"))>0,
CALCULATE(MIN('Dates'[Date].[Date]),
    FILTER('Dates','Dates'[Unique ID]='Accounts'[Unique ID]),
    FILTER('Dates','Dates'[Color]="Green"),
    FILTER('Dates','Dates'[Size]="Medium")),
BLANK())

This cleaned things up a bit - no more dates for A-101 or A-103. But A-107 still shows a date, and bb-106 is showing the wrong date (table on the right abbreviated  to show data for both A-107 and bb-106):

I need this calculated column to return the minimum date that's tagged as "Green" and "Medium". How can I ensure that I don't get other dates instead?

[v-xicai]

Hi  @ivan_larson_cki  ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai · Accepted solution]

Hi @ivan_larson_cki ,

To get matched value, you may change the Cross filter direction of relationships among the these tables above from Single to Both , which will take the two tables treated as a single table. 

Then change your formula like DAX below.

Earliest Green Medium Date =
CALCULATE (
    MIN ( 'Dates'[Date]),
    FILTER (
        'Dates',
        'Dates'[Unique ID] = 'Accounts'[Unique ID]
            && 'Dates'[Color] = "Green"
            && 'Dates'[Size] = "Medium"
    )
)

Best Regards,

Amy 

Community Support Team _ Amy

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

[ivan_larson_cki]

Thanks, @v-xicai. Just changing the filtering conditions by using ampersands instead of multiple FILTER functions solved the problem.

[jdbuchanan71]

@ivan_larson_cki 

Can you share your .pbix file?  Need to understand the relationship between the tables.  If they are joined you should be able to do something like this.

Earliest Green Medium Date =
CALCULATE (
    MIN ( 'Dates'[Date] ),
    'Dates'[Color] = "Green",
    'Dates'[Size] = "Medium"
)

[ivan_larson_cki]

Hi @jdbuchanan71 ,

I'm not sure how to upload the .pbix file, but there's a 1:* relationship between Accounts and Dates on the "Unique ID" columns.

Also, here are the queries: 

Dates-1 ("Enable Load" is turned off)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVzLjiS3EfyXPWtQRdb7aAiNRaPti30UdJCg9UOQfBDg/3d3FbsrIiOyR7PAzmIRIFlkRr7JH3748peP0vdfvvvy9Y9v3/57//2P33/67bf771K7pat9KV9+/O5Alfv//v3bL/e///rTH//6dmDuf/p+fWGqw5SRQYOZbuqmx2z1BRpfIz0hc1f6B6Z/YSYY6G/ffvnP/36//2MU2CyLmrrhsaTtBVnMkpZj4bCmFVDPoZZ9CwC0vWZ7Lem+BQNtQellSUNXZlpTKWa2rauVUdXtwX0HykanVwaL60ph2CgLW8MHlsmsa+0KL2tOplt4HxaVl/tAE69pdWPNcVnnvj8P8L7tK0lC7QVzn4q3vVdMbZu+EhWiuKxRpk4uvBa9tRVVywUQhZWpp2RYj62EkSbdyshOeygPGWfYoguvHcsJUQHWtH3KhbbhcLy9W9Vdgkc6O6JDWBcIVJWdus828EiD0Qe7zME4o1OJgpoM6n7GE8+XceEzKiyHSviMCQ8tPCoXVMPyhFV1UDkOMJAhDjRGQTeGoatBOvVcHoYhCJUezNI0xkZU0BUFC6NcmNuKekuGU42F2RZZ9nAoAxhoFcwoSmUzky1NMCsxIdCl1KgyilM+jS7BLqj2CfLktE8RdUdkgCNm5VomnbF24QvndD8rESHONh/awDPhNNjROLptvwtd4RmNWXiq12PCn38OVEBnowacM9tDt517taPUURqauwEga7NJRneYWoelORwAmgTE1ngHqadEVm3HOFdpJXbtsJMVslmAcgfUJB7mLHpAYzfxUKXoB5Zu5Q8kb+lU2LWEsQazWSAzO8aRYiV1tcN032d0dXfMbFb1ENO4Dbj1p4Yo5KfuQLP5A5mlHXVa55NmM09Ze/OVD/96DpLjcNNhlxBmjXjzU7Z37FjJhRZ2wNJKCThryktTO8UxBJwVSxHjhuw+FI6mlmNpJCmOJKeVjjurfu3cPLZAEFnVJBMCSch9iAJZzdZOpF0DTcAqRC6NZsr+iNGQmc6VOpiCKLUfG5oYYQlsWVCuwBFYfA3zOf007cocUJYktZs6NSAS66y8+F612NBFErnT2dBA5sZjs8ZDXcWVN8s4VNth/3Eop8amQyBw+c6+D7JbSo2HoIaztvajFJV73fuV0hKZBWke/8bUiAcUdISPtZduDovSWPtBjLARlhhmK4ra7hp1STGR3TPICNZD3b2yGW5E8xFIVrPYDnj9/fffEznAYkEUfKCcbzWT+jpwjiGlkE93AAeddm4qDGEuKwIe3QGy2aiFnM0D6HhSuy1O6vTYSCmNA2fjD9kSFxIWCgh2nLckcwdcP3Aa9B0BJk5q3a1SMVd04IwhKeRjHDBjSjh/c8CsIR/i2twprCTlB85wpqVM8BR8aM7h6wE0OZGxxVJwDiYmKZW8+pM3agumKCPOrNQxDmaPq4v760hTKPVzkkatYo2jWdoEF/PkTfyIIijNo4yH5o2cMT6hfKqzL+uho0lIbAa9dHGHDWsquUI5bZoE94E1urg65LQ5d6TGLbF2ZmhGfg28kfxo/E6T1+07oZYJUxbybU/GmGxWZHTNbUj/CWMedtfZmpiMQuf2pIx6t1V0vqt5LORvZpZmFnXZu6OaKGkcCQODRU1u7cykGq63Gm7slohTv6BSsvpkjCRAhAk2XmxRGUmvo8xA8adyBpJnNY5nfDPK2OacWbt4qD4B/KxT1fe0aQdRA22cRyjmV0PHhbKJmaUZMCz55f5jyyIzBYQN5rNfqG0a0CSEWz6YprXnVSnr1pDGOXjqVlqh8Q7u8w6Cc/nhqRtlhXpms/te61QX8l4a0jgILMkHrjhFRvqu4dyRjOhbN5w5Ec6SNJw1/1XHQ/uPX7LEL7Y0mlAjN5x3nDHh04CafHkIQpGtSVRaneMpkw3CtM8SxzRllWpOz5uhYgbsTc6GXZCUUaScgVARhak1YJMpiKtca2bgWdcon7GpkMJP6TQ6FtvszSSrs1EP+WVAJvGRqgiWz18WJ4N6HCt2VACZTAJcTtcXJAsZVmCTCKCM55xpUW62FvNwlpTDVr9VM6T35qocibVLWCIxREIXYRMZdIqwHIE3C5c7Oh0uOToF+gyoElgPbupUpF2NRu2wLRWsxswZH/wuVttbJsHEk0i0TXNiehyoJAVKOV6bin42GvSRSWqW/hSPNkd1UzCrun+JTaqisWxauhQjLy4semhAUaomA1cpKwtEMrEHZDYvuUGC+PqSFXAW+opLEhbh5l0S165SAuYSDBFKKJrUS+LWkd67ZMWbx2ADL9/6D+AUXKwFmrqwJvW/N6rVXZLMQaXQ4OLJMlFm8ZJQZcag5ZLQhBIfF4p+bCnmknSBPQlyotTKTORSXjIbgzn1C3HiDBfCXL6WXHm/LRmO7rWmTp5ckHLHyDKi+QFu+3jSwDi24+dEmDFhGXmArRorw3x0g+oy8iB0uQUWJMVaQFlPbGaMLdPUuCzb/xKmc0WaHjMkF3K9ksLK5cNnAraDUyBULncWVFQSvmxMztl8W5VFqfdLhYZL5mj1FP1fPmyaeY5qOrEMQUXZjsiVBSpxrSqrMcuHmcKPJx906VX5IBW9oO7dwSxdYIxtvujbJngyoPnmbTfFmJlKdW/YEPWitwxLlxgGULAsMLZDeGor74kPYmJSu4C+XbBo6sZyKHUJXtOpYoNidKWXlqqBbfLVSiz3X6yr1MLewIbo9kP6+5//zKKNEBHtQG+xp4ByhOCOvx1m+pFaAAFj+ZbVOaBsuWWl5MiOM/pqwMalHeTbt9cwZdI2v4QZbYplRE3zgFlazNg+sKOUFytljXeQrXf1lCLfcbbTKG6GIcdCCY4d5NymjeoFO8wGeSWMZbxUFvwdpfZ5wWB2x9itH+Qkbf88JmeEIuduDeG0rR3HMPHFj+iIzWEkmyJW8dLjIU0p5OAuepR6VwGLjPVFlRJQNv9YRRxMSWWl+O1FDK0YT0FokhaKNUiENRob6t43zMDaUUKNSpVboQYIYSRt0k0/BZitQEaaZb5UnNM4U6VEnWMyv1sXdaFzcZ++aWV2qOcSRdo21UcFkIQX6HQFfqAqjAuzTd5i1my2t1D7QrAfoVHvM/Oh+sQfUZTX1Hw7+6HRQRzOpDkWun72IokWdJYg/j7RQRdchCZQOosEdkcwYK4zMyFcSBSagAQFl8dbkY2KYQlPZhKNr1+/Zp7WQndGDqC3JCAfB8xfTYyDqae1UFR/oJJgfIk4zSFu5BIcKF8bWeNgelYbmp0D5Kw9atoD5a+jlBKmdCaF/bsD5sjS2ugI5+PyuGvJVUXqEj+A1uNtvcV4qq4j7CXnICPG7Zqo1HygXMJqouubBy67rTWFQ7MlkUHO1l3YqmjSlDfg+9bIBxuyF5VhNTJVZdMfWY/h08kckc8hfoO5y1j0uFyMEpp/T+YYD1HGM0nGgmnfnDxFziFxx/oI83Hi5qjjLKpwwoSKXI54Qx3K20XmgCwN4VSzwvyfYs6AmbKTOFHT1ag2TXfLQAHESRpnRpZ3vEERnv8UcXr0BjODs1KwpMw5lclbi4Ork8/4rFM/Nzkr5dgym2NkztUQN12Z8dAWYVfWSVnjMdjMl+pgn4EX78CWRagn+6SNOnxbFE0fR45xUhflT/ELbBaeb01mrFmNfnC2ZqTcgvIGzgGo/+/7j62VPAOtgjBbLgHF32C+3IvFvgZMIk8Z0Cq6Sj3+DenylrpAG37yvb+GTDLG6F03pDptc6frcye3Uaf/ATQV+ErZggazPeFAswazTluVjbapgJHS6A2ogeZE+eEG88l7vHzdgMYLWKkhp+E0I9B6Zhnmgp3JyItJKa9kfw6YN0AThQEpkwYiMDApLQCVjExSDuRBbfYfi4tAJ8miiQjaRNpIyah3dLJ6xvkOlS6wCJvw5rtqJMen2Wy4N0nYzZASiksaQCj19rEWCoySMxGN5IoyRskYT24gzxvYZIIb5YkrznTK9vTJilmmdjdd0U8DQplKquxfdmN/E2m1PniVT7bVmtEcsXnIYiXX/41t2ozN8c+MbCLRRhNWbG0AJllTzB+RNMcqhx2RFqqiGCLBxgjX/V3+ojL97p4fj+iSoAM9YZGzqXcDZtcy5ZSNl7dQOJ6ap0qVeOBTDMfR7QUuuXL1GoXGZBX43vg1jY0ofXb1mbiK7QtX3xk2U4btmjh3IyYTrtYUDZgiumbFzmc4fI6kBZ3WAglDOfPD3L9++HtlMxqzq63mFMp6X232gKuE14+sGRkTxtecI+gQXrNKTkWFfvXxz0hNoNeEGkMXFp+8eVH5BP2rSCuJlL8P0wWQqw/QE3fXhAuox64frhtmwyj2+uGrNyMlV5gJkLscWVSc2uJCtmfCKBTO3iGBL3NHMmMa8vphHwdreT5YUPZi28ITKhEmqr1dE5tRw6KSp/IKqx5XrRlkPvs4WM8Yl/4fqTKR0WDEftpIg9jaCTCz62vHgpkFLqxarWGgPtmrrfPPkeXeJgzk1129b/V80wrkXIPMmaKFq3WqBkrkXD/yNBm6kRkbVv4+x4aFbiUzHWAzA8Z0SNagpW1SWSXY99YHTefIwFfHmQyhYgO08mYYw73IBsrDz8oHySXy0tVVenjNI4NcXmVAbziSAXqO6udkeEgLw2xhH8zZr78mXS8b3VfZYb6wD/TbUdlVrramHWNLLvwe2Y7zDm0No+nhTHTbawfp8YzEwB2U5CunsF+GFD0qxx2UPA828ZSub7jSsyY7KtFEI8+ZXTgZeFv9kxVbWFh2BTKszGUeSzhvbykWsk07Lks7jmE89/YUGsMdpCH5M4MJQ/lcccUihlAEO3uDZDj9NdHbLQlHOAkgJIlF08CRLEeHOFNHXqhH5kUT37yDsm1NOvahBJqghx8555tf8EmOF080j4C2MRDlFMegCmwSiztHAkt8YuDFEqFvjST3NRXM/71oIoNtAeQLwqIxrI4qQay9EanU4CMsOS1ENBDu1iMmZ14UkYVFobDdrZSiSynCGQChCGz/GmTfOVYYbqcE4Sc4xJKgvxOtpX/t0FBEvYbBMESGGj/lR+Fb6C+GxHzOFEQneaOthl01JRNuu88J0ovs22THaggiHk/8QhdwbPTyb7AhaLaGP8GO8E7gzdZIuJP89pE9Mkk3bm8JP7jd52YbwgZM/d+S8Bszhrc0hVt5oKS2WMOSLC+qbIOtB8+MyXRXTwuzhoNDvZvN2vIL1rcsG1Wp9exm7UaleOJmSbGQm3bzjV8r1SRveQw+McpmzQvvgIs6tijDtnBYCOJqHBMljW8fPuSgtrGbNRUrmbCMCXx3n5mAzg9jrCPFqZGMDU9XthAdpJ428B64BpUaZstKUOhSRjJEOaiWC5C/HTIuQJN9TgV024J0+mvSG0+nXBgp2L19JBWLEpSZCcEHuh3gqRD6zm/WeRq6oDCSV9GZeG9ewoOzS0pLQd25ELAIykXf1ZAhk4JCdBAV/MYynJvAO+UrFLNSwThyQWk4FcXP6D2pIHseFKK7Fsrf9v65lE+swkAliowJVC5lJojPvloqYMzxxjBgQqrnKd2lXbxdGS0DqM9ALBfH0eNkN98G3FN4/9v9x7pKdJmuoewREVMb0KVo0RVvMN8JXPFLG9KSQxfoOlBf/WA//h8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, Color = _t, Size = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique ID", type text}, {"Color", type text}, {"Size", type text}, {"Date", type date}}),
    #"Appended Query" = Table.Combine({#"Changed Type", #"Dates-2", #"Dates-3"})
in
    #"Appended Query"

Dates-2 ("Enable Load" is turned off)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZzNrtw2EoXfxesE/JFEiU8wm2kYHS+DLALECAZwjMCYef9Ri5RUp85h33hzFz6gKLK++iPVv/766dv+7+cUy6efPv3rx9ev3/e/X/76/du3/e8SUg45pvjpt58u4br/xy9f/zCyLeR5l8XNyjYz3uPrH//5319twEjKeg34799//Pl1/7uGVL1snwRPcAo5vSaYrTAZ4TniHNJKwnw9+JrgLN44TaxbQl5ovFnMcJ8eP3hRa1NConcuSphSSAtNcuVJTiG99mVfEKPbxCRXJaxyGXP0b5MjGcSxybTPagNz2Px4MfGbyFeO9/4Zw6FFjGL79odO9Fy1ffsSFhIu9NzdZl5ms5uykSmitm6wA6LO8XbZRrKN1rkEXhS1bbtNp4k2WO5b47N+xFMK73C6l+UYjmniZd7I9iVN+3D0Hmo7yB8dKHmr6laQPEf+sUUJpYPb7WX7RyQtB+2gMyDddkAqwOh67io2OCbxKnP3Rx+CtJI9q43rS4jAzTRc7csSNUZ3vBFmrzYus/nFVaxyafhyZMLpTR9wZNz+IuPSAKPNY4S6LEbL8i1yISonfmzuziWNMLreozYrwBGZo6rignJra+BoqECaukMgkPBN5v7c6ClC2dqCNbxFjmIBazBG+hiEotXa1GOAT7WO6gHoEIvxEk1qDxK6n4eLQMZ3u8nzPr38e0JREUNhPvCAyHNPK9qQ/JC8lDChhFO4FMzWPAbp2wsUmFASsX9pVm2Gymo5K3jAhyakZ223ZlYafNoiJj71dOQeSHipuaXFRrSqiU+dxWvvEi/4vrsbmNQgprjXyzJWJKDrxMGjlSemgd1cRVEWa1WCM7pJTD0liEsjGGbI94cwuJEUCzm4gXTcT8tHLDQ8zdNk4EjdFjLwgOs5BdwWA4NZpmPvHAx+nOYWNyDBL8Dm0WMUulcxmkUMlMh+C817aoHdaLiKrN2HVeCAXEZuGWAGEsgDbzDSICisH1HwClYzzDuqZHjxHlo6qLO0RBBof22B9ZDJ1EuE+xJlQbl5m4u6nIyQJHkWzBa6hzILS6iw7pqFLWS0c7U7q0dPhYbcRMaslAPqaVYFHMjdOfZEYEiQnT4gYzKVwsIwqPyr4FBcA1ZvL5qH5FdTxuLCPFCYNYR+/z4qNJbugIyOI8NqgTg0kogMnuPQiVr9tM++EIdMJrVn5mJ0Knbvq2pK+kPGDitDgD9E7LFmaFMdopHLmt0TdZCIvUsVL6Fk46Vzs1N0vIKFH07xsR3UWtUgQm9OxozUXg7WW6RCBibuh6zQWFPvqdyrKyDp4NqRuJx4pcebU73pbpkn6pLcW6NKbWvwpsGYVIguFydvWgAXIyK6ZLc9kbdnoe3RkeNMM+xTmREhUoHDhSvCxCTCbisjl3w1FLeRw6ZJRjg4KmSnUqV3hr4jweEW1o52r771d372MqFKkAldeAhddTJRzy0Q2i5CVCkW3Suo5U/OV0hCbF17EUJ0r2637S4Zo/BhiRF58V0CBxIRK5OfmfJhXsNhvkIf4+KDbcxHGvZgKbEXGEQRiry8QROF1CTD+BS8vcrVL2zXvPw1eD8tEtvILl8VeCdIUSNic0j/UIXIRq+p8ix7QjMEpITiJqZjeIbq2iFi0yjznp8/f37blOqv2WSiEIdssqm4LISiqIlUttU7tdnIRtlWjv6pohpPEHubTG3VagNmkwlvBnA2lYj3CeqCJqtidbeeYpuXFXV5hUORplJl4Epz00ciq1fJdAuPbJpQETNBR6Dp2GG1GggG44COFUdTyZbV1W+0Dx1ElewNWKZeKdsW/KET54qzME6ZIPcqrDpsuI3nV0UnYLO3c7VjGQLoTQ6dlW/+mTK+TAoa8iKzH0tlYGfKGt9Tw5YpDxPxiNxDY0vcrKjxjSEmUOVhcye6Omz4XDd5c5PeKwum2XlNvKUyzBQb599zw4siur3ZJj03NJxORrdhWXbZc/APlbFm11VvAIqa3nIA7yq7kN5FDM5BIjQHOeTcNkDTY3LO5BkwlNkztIWGAac3fUC2sWyDUvuGx2NdvZ0MsjPyX7LAh4yQ4TFpnPcmogdWeBsSNx/PVNuDwxVR9TIu8bGZ+wacmaKDwKbyYIP+Y/L2K7mZgf6/939QyZjGqNmsLhNJWrKb0GXK1SU0ua7Uh+82MnWhPENMtrTuQhl1Er2wYgcy/q7TPeM0kZAjz9oz8GRlKl8rgXSi/ne3WLpO8LNZN9tlypHZcrbLpCfLSskIFdtv6yrZBwAP1YXyGstKMm5WTtCZ7rJNP9a2KbqSnRl2P5tMHrT0OIVWwMldCWzMsijN4k0kSTNUdgYkSvB4PNG+WcD1GYqGDd80AunOBpkPBdIKHeR3IOW3INk1TGQN+hwmE+viKgv2ogxLqgdDmyKi0ST2TjScNxsYDEu+U8mTU9txFtL5PUq9+YAy2dJMGRpFxJJhmAxBdNbwuqshSdwuo+2N4jg5s+vQRzWbeA+BUrvVhuOpgFSgW/0GpcXmfAYkvhDGzkilc7NaGFm5Cm+kQhI00gRHxidQCJY5XQzkOnRSlwNlCbKTMMNltGFUmnnrdOvtOo/LHic+PF8JTqapwBVCQxOVp0mSJGyLhIKlBbLA5wCk88JzulRMUYG74E9ZDWGL7vm+hWDmJMLQeUpoVJocs59PfYCTIDI/B7dgEvasno4YHUafsgBytd5zmL9N8DzZbquA/HN4uGkLs+fg/Ga214qfw9ObDUUyaYtgfs9BpAG38ZR96eJXXYeYJbhZifN/2mfBw2oj6RMyNJsBOaOSCVqCDhECcYdk3GLJw2oT4BEO58tVwEGl3A4GmvfadzgDDlSyuqFU9FgILHV71RuKihtbcEauVzxDF/iEwXt4m908R2nXhrgM6smMOzwIFXDe7FmwW1hxNLnuyc1M4lDhuOH586AHMHuZOqRJZA9qh/CSoA4RlUZSIQLXKqr7qxPzIFZ9DhQeyMW6+ciNKdC9HwCRvOmJi2EJSrbnIJd6+U5noeynao9+VRJhhprRWpiHBTKKUWi4JvUeiSlgNiGqkA2a+k+dNEEL4USBz5X9zFVAzgHtctQNc9mLOHeZoDH048fochh6oUM3iNwm4ztk0luZw5lDNLj4kiY3N3WTNfuJ8f7MYO2HiDfoNJp4i1TBvgQ/FGe0C5S4h0juEHzl9ZKpnAlagYdIlHsRrm8cKllapGTvxh464YwidDkOlfxmK9sbTIeM136xCdih0VlTwlUdHLPAJYJDp+6q2u7uIRL5Knyf+BLJTnG1eQrhYZLM7IxCf6HlTVVkT5FtVTmvCrcuLjjEzIqzxHuLbHoUBR+CyOxWdnDtpQwRse/paBPpbfQrpk9VCiTwA0aY78EnWZN7pLpUnJzxqLLiFT/dQgySqQQFzwUJUWkPhQgSf/VBMjLmbeCg7HcgRIktH6rghOK78yoin4LLyhcjwhF4LgcRPvnhBo7sTRQxu+58rD5+jOR/Rl8wukeqg6/k8R18HuTtUX348zEj1/1jBwn12lwAH12d9MBxc2pjKmXjHbo1jpA7zK8OXZnU+tgsU6yNHKcov7f3eNy+2nj0L1++yEOSbP1hE40KQttoaUp52hihp9yE0pct/sG8TZNQDa7v2UZCEw4OGqMfUFGCCXHTiVP6NdCaDBxadaPJs5EZ7rA2HdOywXFuUw0ausVthLhIWW1S30QqqGRw700nfdXiFkTmXXj7pukYmhUauU2lmiMV4mLTiSMRuEF/qETX6kqEqsOG3UxR4PjLDfa6K0Mz+LTTQwOt2snrVGjJgocROESYOA8p0Hq/ueH52SLr5kYk3vkdOe4iVByAY163eMDU5xHpn4FToAS8yRGnYrOfnm6wV/9cFW0qpA03PT7cEGLyS6LiH8lxfhP+RvVRFjZjhU4gSxp8o109E+oeH/GgSnqia3Cthdy0/ByP4pZqyFdoow+JydCT4EhjNt2vmuxDwg9o3MD4nrstVG9efPPas6ybwOc9ixEv5sAteZNUuBToV3lcbFbsN0KejFR+DRVr8BKSBwaqIb8TMkVbRezinSi2hPTE2ArYpBn/3f/JJG2C+7Zdpn9Nwv4+ThfKG8jGIXWZIgctoAu1hzNOqetkl9jW6F2nfyjM3mvtQvWJt/0phy5T8Wa2v8XRddydKVD3NNnwg8noX2TwVdjsN1hcCetfocFoIvzj58ldp1v2yd5d7MrBnTB+tP6tj+JXWn77wirpzrLtz3WhvMef4G5MU+qvKCeyfF0V2aLVsMQZr72GSSxZhsmkud6ZoWYzJAmEaQlFV+DssKURSaPjfWLJpILsFOTJC+3cqDswk2WpQ7EkLFCjRDssUML0wrCkLo4lGnBwOpk0TdQzn0jGCdxqm2CGJHHPhkYbnA2Tc5MsYXZAKNn0d6UtHtxBShqm93U3weTKApTJr5kC+31OJVa2GBWVoLYxKPlkkwHWZ2mZXL44rMFvowRH9vQh0v7qWy6V7JmroAlurBmOVJCj8URUmuAu6TuOsq3AhxjNcOgnQhLFQwpKsn210MuoikijRD39xcuGP410eo7f/g8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, Color = _t, Size = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique ID", type text}, {"Color", type text}, {"Size", type text}, {"Date", type date}})
in
    #"Changed Type"

Dates-3 ("Enable Load" is turned off)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVvBjt02DPyXnBPIlC3b+oJe2kuLIjCCHgpkURRIegjQoujX18/Wszmc0W5yyAbBgJJJDYektJ8+vfv1gw3Du/fvfvj28vLX/vPH37/98bL/tJxsTHkwe/fb+xNmDvbTy+c///66/2NOtj5g+YLl/X9/fvm8//3L19+/fNl/5pRtxwz1wozO1BNlySpamgSqnrZsuFCF1ptTDnua1dbt/MJhvWDLZepyQ7IBt77ScrUtd3uqkp2aFoDYoHa0JvSA2WXI7bogJgsv7R7I8Gk2CkvkcZsY9XT4DSpq60uaccGZ/LR7YEJLizh4+4HKEGBjj0+nn25vVuGD0r7uMpSlzy2NGOCBD8G+oRkPnQjM89QNwAQ+5SMezVH4wNqxc7YUF8ZzRXeAC+2d+TkLSzNZUqGZkmEAJRlsQUtVW5qZD7jzMRmeKKPFHn4Kx46zzwOU8dwpn9fz45wpQYbd0AqnBchwH3QLqFmYesYY6YA+yOcZdptaxdbXmMis8nq5uapKOjjSTHjwODAlhQibMLRbWZBXWex8SiFz3mnqPlCVmRC+rTSHIxF4sYyZBahwf1205ang3YkopsIzwCap4Eg89aXhJsOCSVGQYf+4vjJ4X4Vzfvvciz+qrCND0OvABcQ8pIqZEF2QCbXwanYeKPd5igpjChJTydIcBS0PhBmTOyn//BNqJCd63gUHTgrDGkCKCo/wufgdOI5MPk9Li94BmoSxudHYbphU7VYC+Q+dac3d9dEYZ6oxrWHzPjwuikswVdW+CmSPB04XTBl068BJYoSPlDVTbSJ/f4LJMrWFwJvTKcmCNRmBfckxfClHYA9nCSBRqmZfGR6glfeV0xJAKimtUPY+YIIki6/CiCROUYZwxIz3PrSsFEiCqJV8Kqun9XSp39gkVwyOH5SWj8xKjk+FcvtiCNNtSBbWVCyZWs02IEuiCp8hWpEiMddFt5oJj5lBs0cUcQ3K+aFVc8QFKtKXIzBBfU4M8Ym/BJxs5wqx/OaIr7pySGYmE9UATd1FlNjY2oJrCpq0FX3QB97YSCIh2+3H/i0cbiZKhaK/S5TaIiCJ4jQiD4m1ROaCoCMilhZy58BSv7QFB82SmwB5TqwllDJivlPl1dL0Zn2DJwMlM91w5EhNqeTRlFdynzNy0Mxe01ECTJW3D3NR5kS1NaSoqyoEOSmOCC5Z6dLkjpTLeR8/fuzMpVboL08cS/7sN3aCZJzIlBqTDE2iPU4VXTURjInypADAZKBOpW4Z4cSJhJaBwydsJecW6EhPlOrLUaAOnIlkVdmcyXQ10TcYZ6tnTvMu6cmKS2snTnIGM8gJ5NGInT0cmBON+gjTyxPm1d3l+RxxSlpmGCaduA5t5hBZWYQd6gjHqVMqWzQ3dHJcnqNFJs8C44SbPG+0NTd7WJhfYY8fwJSYJtSk0SiZyIJsgRY9ssevanP8CNm4zMlibFVVRi4xroknqFRu8vB5Ii6qKQqdTcmc1ni9wZwZhvA3cxQRibGSO42KFrhDHlljnuCirPje/aaNkPwYUdW+XKOQ/BZvnnVqfpU3zwKUVIenVTUeYSU6z/IAiKiuRCh1ygpthcFPVB3nO+KXoE2BruJmjZxbQVQ7tyMlnmHRSWamtMl5y+AvWyJv/PXHGGE8C1tgxHzThgsmIy3h9LVjYgaWVyUPtV7isjyHhFEl640T6zwEa5I5UFz9u/+RU+IMOaLB5HTMnIA1nMpzY/JTwgZUE5qxJWtYWXFnUUDmzur911D6QtEgVzSovknJhXzIkVthYt1gslbAWfOJFMqT4b6kwbh8riA9DabmMeQ+PTITkRMkmuCyo8F6Y8s5+lmVbk//wXeo2WWmCHf6HZ+7G5CbThNe7lw+Qgna5dIIoxLHJbFBPtGKTFNLRGskEzX/vmp0XAq4CdoQRyWhpyuxWM4KMgibIJNjyUQfraq44S06hWkMnmz5SsLUCWNVqr7TdHySB2Ihg1zPjYl8Ixg1Jjo24pJmX7OSMdWSrjBN7NIJO9xX6BTudPp8yomkRLRCs2CnftsycHiVND30lYH6Vj8TjXnkU0CuhTAFBcMvUfVEPesO1iYSWcqssqwzrUp8EAoxU8nSBOMywSOYa1JeUM9fnrNxkiZxAZ3JoFKnDMP7LpeuGXmObKKPqd8pT9U3PV06FZH8dXPEatzpUel7BZvg3n3rdUfwgmiTs7iSHCs3Oa+GW8Gt0xBleD60yVud2d9ubR3SGAR8kxOEGU7u1injoGvdAll8mDPaUk3rDBDxHsYg+25h5OZls8CmZPfTLktukGJGab3PvStxj+bnMVun7VlgqrB1nwH42/RNDqbXNu66MdLlcwo+6BZp957kcM1fV2yyNltAdbYPnYubEunELMjR3ZIGI9z7bbocw6bqSQSujgvzgGNXmQi0cYTImBR/pYAswAl6YIEozuI50Q+QMBWIi5oKT302WWhV0JsnB9SUKCwnlWGNwZN1Vgn+FDyYyJDS6QWKya0vCGHzWcz8i3+42NcDdJasqqDcRCqENzNBEUg+MR923gtbIBWPMwuMsyIR4jMSE0yIb04zUEFMkye01LuLCcdThGWOwqG4UFLtccHV2COSSilChfFgZIOrWFAXRdOx+Ium7YN69WKtqjbggbj3HBClmZCtzwS3Yu0RIXZLZ1T++6/DhOpblgOl+vUVWqUDJsjQttWcdaBUcPAR7wFTFy0w9zxQzIgRRO0AcXxWyC4HqPNUsoTd86Rr9invwKj4ZKgjHjAx48Lq+wCJMTHMNQ6QaiLw/uKAySFJ3FXnTd6AfhA3KytcDR0gOWqc4JLuwClyFD8mOFDimeoAFeOBYmlefHp4YOTz4QXGQEQO/6ZkDIdQ08NfqQR6+OcFSzisnSnkGvamBGMFMQj8cD1j3JgS8goV3cUQXYW8yo+JLYk33gaTsIsfogwJwRQMWbzaEUHw2ae3xGOPyUvURQ96x5CjJdVJFMpxQjgKvIfvkmOG4Ulgh682YwroFVHzdzAkiP9FERrAB3qLxtrgWc3FDvWCKJ7oUeGmFNfsiHs4PEI/sOUlfkApFI6ZHPEaPFvtcGSBmTaJSOf3QYKK0K/g2GskKRQCUVpNKUqN7utyQPEMKsMIqkuSfeMx7cuZoB9xXCxhcQiR1KPcDFdGQUT0e0niSLxm2ff/2/8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, Color = _t, Size = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique ID", type text}, {"Color", type text}, {"Size", type text}, {"Date", type date}})
in
    #"Changed Type"

Dates

let
    Source = #"Dates-1",
    #"Filtered Rows" = Table.SelectRows(Source, each not Text.Contains([Unique ID], "100") and not Text.Contains([Unique ID], "105") and not Text.Contains([Unique ID], "110") and not Text.Contains([Unique ID], "115") and not Text.Contains([Unique ID], "120"))
in
    #"Filtered Rows"

Accounts

let
    Source = #"Dates-1",
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Unique ID"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

[jdbuchanan71]

You cank upload your file to OneDrive or DropBox and share the link here.