Filter/Show Latest Data Line for Each Value Type

[Sonia316]

I would like to only show the most current data point for each line.

Line is a drop down choice.

Audit Date and Time is being recorded automatically by the system.

I would prefer to add a column but I can't get the syntax correct.

It's probably b/c LINE is another list and the two lists are joined by a like ID.

if [AuditDateTime] = List.Max(let currentProperty = [Line] in Table.SelectRows(#"Changed Type", each [Line] = currentProperty)[AuditDateTime]) then 1 else 0

[Sonia316]

Most Recent Max Check =

VAR ThisLine =

MIN( 'Line Master'[Title] )

VAR LatestTimeSlot =

CALCULATE(

MAX( 'Quality Check'[AuditDateTime] ),

'Line Master'[Title] = ThisLine

)

VAR LatestMaxLine =

LOOKUPVALUE(

'Quality Check'[AuditDateTime],

'Line Master'[Title], ThisLine,

'Quality Check'[AuditDateTime], LatestTimeSlot

)

RETURN

LatestMaxLine

It is working to a point but not getting to the most RECENT only by Line.

[AlB]

@Sonia316 

And another option:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVm7ji25DfyVxY0trEhRD05qBw6cOVxsoCdsGNg1DAP+fZdOa84caU5vMnOFuWw2RbJYxf7llx8uiuiPPz1/h59Jf2b+ST5cuI5pHtl9EOEck+UUqpjKNhthYaOpDdN6oSTN2dzHG7O//v7fn/78j17/hX+T//Hrnx6Ovb0cP36/ONb9CW7+WWpQ17o1XKQbyZmMRuuN9FQ9q1Wb0xuzv9f/5H9Pp+HplJdT3pyK7NYyn5Y5iGNrTWIiI6FYoz1n0y1nbqJtxONdJR7RMj0dy3Isb6N9HO0HzVtPrnqfSzZVixoR702hRqZRzpXUudrSbuXfxxqWy/A21mXsZoaC2BrJF9NSGkaGOlNSTSZYHtnG7GzcX/RxvTeRpuU23UdKH3YeeygtOeuMo1aMuD5M5qSGOVdHlRyNslult5GGldXA9y75w82/2toaJe4Gbq2R1sUkCt2kMCilmOvwZbei9y7dcun2y+XdeBZEiDkQUzANv3C5Fd44JJMputxzk85utwqvLuPT5SqhIPf55I+rnbKGVhTtqahawVWihFhMaMOGgOYdGjerR/e/z2eIy23cL/c1vSj98OiYmmzgajgiPsTVTBZCpC3GNqi51O1u9b5ywyqhsJeQuM34Udc15dFsV4PMNUSq0WhwQKhehrOq3Zb+3ep9pHEhUrT3kcoHz4LQDByk4UwbGW5Da6ao4Nhwv5ZyrHpYvcejuCo38n2keIN5tCUTxYIy6gM5HaGgaIM1lmrWHDI7T7sV30e6Sim+1o5+OP9y9Beu9Ky+RngMJVdEOlBKHbecuLrwKO08dit5H+lCoxg2l0KbMT8y3/IYbXgTahIj0Q1TPLBhiB3NqXRfy251j7txlVJMe6SvgYcPmn/tbL0T9GmU2TRZPKAhyixhiV1UdMMXWMW3kaZVRsneRxoucGol56ahGnJzoMbggAqo3lDcBN3UGufdKt1GmlYpJd4jfS2leEWKC5bRPSItGeM0MJsSGDmNYzgKKfjSdqubSFcZJbmPNF6zItlukw9irC0OkSqq15I3vUYNUW3QkDcrvkektHKa0r3bdD0gllr9kAl/ICsY+xhsgqmaG3oHt8tN8251f8FKl1ul3e2e5ke0rbsI1tAxsxvcBl+N0iQtSQQJLtzS2Kz+IFpdedUrr2wXjm0zw13UwUZrg8Av+ALYkXprSlEciVBnHG0mOsz8vWO/HPsXx/Jhw/6ER0lm353TEk2OBY65Z1Oi0zl6uozmM2I+zN6XlK7cLvg8nF5HMJ55Xcll6xp748NkDwqEUJ+CsR7dXshTZr9bvWUP3l79ev1+uWDejB9oGikRcgtgEEw5kdgMgraghGlSQ5fgeLc6+e/zer3l5ZbvI6Vr2hQbU8hgZs0BlKRlDPZWkom++6xpFK1pt3o7bbyV5VLuI6Ur0h4cFRkFpcNApgAc1JS6ASaRMpXemHarP4g0LLdhj/Q1O3zltNaQUnbVVA9mJgwWjBu3BsWlNvagmPW71U1O03KZ7iNd3E67jz1G1A1A0UhFm2ZG9xRVGS6pC8eL3nNfT6uUyN5HurRJZK9dYjXeokFR78WU0VHHo/SMIstu7Fl6QMT3SGmVER3wQNvxIWhstVAP6JDZGnA5YdGqGEakRKnW0vfrutcznpdbfnWrV+18Hv01ZqVSC1PFJAddIT00EBdtJoIOD1bObruuZfU9Ul7Vy7K5ZN2MH/zXh9ArwMBE7QGw3ypmW6kgiVqCsEbIxs3qnv96XtXL4T7ScFUva7XVcTCcCNhXazWlhgE9472P1o2xvWy4q17W5VLvXcYLGqpYLq6TQWppkhYyKREYeI921IZow9it3kODu4bb9fvmcuNqGEg2jOxseMQ4o4SeiYBDX30lgU+U2G513zBu5dTtOXWvBZGuITWUALPdTXqGPi34MRHXjEGWyAbfFmt+Wsm927jcxnu3CH7edxlZpmoyKYPki0gHGZ3wIL6QUmw27i3A90BIbvXq64se8LCOyeYaIcQRY5qi0RajUr2pJU2+VWLy6bvVq1v/9OqX193N2yOwHQMpGRf81I0oqaSFTQi4fKq+5PLG6tWr+/QqCwrl9U5pwfx5bq6BJ4AHJw+BI60FgOEALbY2OugORqkdZidPe4YrC5mEdwvn35xd9jZbDDfPGR3bMWoSCB7GnGp0eJsq4TA7q+or4pXdrQxh8facwVK4DDExjrnHCpg6LuGH9lgDd7C1w7F8k3NPxyvBckTo3ZuzxlY9KmhWEs82AhrHwKb3UXvD0KnuyNBjaD4R4+l0weJ+P2vjd567V8Yo7QacWOfWpU4RiWNOTYtiwoZ0mJ2Y8ZXfNdrlsGB6c2bgX2QBjZkDSErIRitKrKBzi1hn22iH2en4K78LlUUPi/jmnGPKsYEw5WGBVqGCiScigBe4cbNzDWMPs2+LiU/Ha1Pq/WHh6M2ZqGkFUJrHIkZ6TtB2NDB8m3Q0lR/9eN99Vfp0utpoJ4dyLe7Os5MC4o++qSPEueLCqG0gUh5TGFqvax7+MDv793nNtEYCvY4E+Tgo1eMoHBprUtM0wa0CHyFhG/hbH1YacfL7Q+w55uU5EVZy9+Knjx1F1nkEAKUHS0WMYGwB3E2tL8ZDcFFHS7Q9XLq06fepK2vqypZJvlToeR6JS+eEfnVlqnYLwV7gmlsbwXEKnxuHL7OTvz3D9cvxRhPpGEWf51IgBbQN6I0JGVQh82oTABZEFzRYxXQ6zE6QlKe+W3tTfTsCv9ThvGaqIpCz3oyodop3Rg9pMVnmtksT7Rsvdt9FyNf2VNf+SQ8RkvYnPOBMMPMpC0CRHWZ+BMMpoSFqJL3HTujh0+ycg1/fOXTtTzW+c/zUpu5RGBV6GWFSmcyKUGHagjNhJD8X5fDudqtzJry4Xd91VHe3sj3gUsgB/K2hsCqjnKRhFmmfm5LiU4tuYFDk3epM7/OaUf9XYVna3W6Yc9EGSL1Qu4PCaxnpxejB+GeL9CLv2hsw47D65vY5FOyavdbdR0sXxKZqswqiHcVDYgpkdZprMB8i1Zrq4HFI8G+5/Yp2Td6Twb2+N18jkJVUcpzfzqjPbx0F1GqwQWLVapIBsNqtzkH0Eu2ir5sfaD7ZHvDIkpKNtfipQUZCD2HSpzyc4WKtr8G6UPaXfXyLuIl2YaTV+2jdxZqdB+QP6OkOSQSNgFmvvmd0kBvWZRdT193q9kOhp1VStEvajd24a8/pmQIkbjSuopCkoHGBUGCUvbrqMJsk83er99HSumQ6NMIxlh7fsVJpA8md4U1FJM2UBooF5hoITUvRH9PrnH5f0fIa9rxrEbdviS65xl5biWzsCKhk1JBJ7PADKq6XDKbndrffhNhXtLwumek+Wn8tpyQn6GdCDVmZq1wM+6SRIAed75Sjsm271S04el4NtG3qDrfhavwUiPJoHopofunpthkNqYLHymSxFWLQ7VbfcvvlduWWj9zS9oBr4dDc3NaCQbYKt8BglJQAmOPcRNQ2yrbWCn/UQLyo6zbedVcmcc3cnGSCtxkO3SpJwWqcn6wdaDFwF3Hjv/H7yP2K1q2SOuTtFm1co2D0bhmYnMlO2QnSjGyzccopdVuA2Hm3uh8FbjFIx/e5Tdd7Q7q3HLo1FoAEavP4nhfB6xAzDWjdEstudZ9bt0bBVg3P7yDP5cAM3naA0qQzgx3UgWW0bMxsmoU28GmkUfeXpZvlyapi5+9d6vXOo1n8x467BTQAoTKoo0PzkBIcNnK57lmytyPeuyXB3LGZ2h9wwafNHpWcDf7zHHoIF26zGSDspJD4LtTd6h4qZDWPHDpiWyh8nlVdznP/pm62T5MHlcLwVR1QhV1bOwXjHXf0tOYP7fOH6fsRQq/6CU/MgGUQ47nPhVuUdUtBQ/N5X5884PZv//yt//SX3//3248XIeRW07qNk9Nex59nVyXanBvk9KRSJQyjUsQQJR7WTnWdD7N3lfzr/wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"ID 2" = _t, Modified = _t, Created = _t, GUID = _t, AuditDateTime = _t, AuditType = _t, LineId = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"ID 2", Int64.Type}, {"Modified", type datetime}, {"Created", type datetime}, {"GUID", type text}, {"AuditDateTime", type datetime}, {"AuditType", type text}, {"LineId", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"LineId"}, {{"MaxDate", each List.Max([AuditDateTime]), type nullable datetime}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"LineId"}, #"Grouped Rows", {"LineId"}, "Res_", JoinKind.LeftOuter),
    #"Expanded Res_" = Table.ExpandTableColumn(#"Merged Queries", "Res_", {"MaxDate"}, {"MaxDate"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Res_", each ([MaxDate] = [AuditDateTime])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"MaxDate"})
in
    #"Removed Columns"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

@Sonia316 

Another option, following up on what you were attempting

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVm7ji25DfyVxY0trEhRD05qBw6cOVxsoCdsGNg1DAP+fZdOa84caU5vMnOFuWw2RbJYxf7llx8uiuiPPz1/h59Jf2b+ST5cuI5pHtl9EOEck+UUqpjKNhthYaOpDdN6oSTN2dzHG7O//v7fn/78j17/hX+T//Hrnx6Ovb0cP36/ONb9CW7+WWpQ17o1XKQbyZmMRuuN9FQ9q1Wb0xuzv9f/5H9Pp+HplJdT3pyK7NYyn5Y5iGNrTWIiI6FYoz1n0y1nbqJtxONdJR7RMj0dy3Isb6N9HO0HzVtPrnqfSzZVixoR702hRqZRzpXUudrSbuXfxxqWy/A21mXsZoaC2BrJF9NSGkaGOlNSTSZYHtnG7GzcX/RxvTeRpuU23UdKH3YeeygtOeuMo1aMuD5M5qSGOVdHlRyNslult5GGldXA9y75w82/2toaJe4Gbq2R1sUkCt2kMCilmOvwZbei9y7dcun2y+XdeBZEiDkQUzANv3C5Fd44JJMputxzk85utwqvLuPT5SqhIPf55I+rnbKGVhTtqahawVWihFhMaMOGgOYdGjerR/e/z2eIy23cL/c1vSj98OiYmmzgajgiPsTVTBZCpC3GNqi51O1u9b5ywyqhsJeQuM34Udc15dFsV4PMNUSq0WhwQKhehrOq3Zb+3ep9pHEhUrT3kcoHz4LQDByk4UwbGW5Da6ao4Nhwv5ZyrHpYvcejuCo38n2keIN5tCUTxYIy6gM5HaGgaIM1lmrWHDI7T7sV30e6Sim+1o5+OP9y9Beu9Ky+RngMJVdEOlBKHbecuLrwKO08dit5H+lCoxg2l0KbMT8y3/IYbXgTahIj0Q1TPLBhiB3NqXRfy251j7txlVJMe6SvgYcPmn/tbL0T9GmU2TRZPKAhyixhiV1UdMMXWMW3kaZVRsneRxoucGol56ahGnJzoMbggAqo3lDcBN3UGufdKt1GmlYpJd4jfS2leEWKC5bRPSItGeM0MJsSGDmNYzgKKfjSdqubSFcZJbmPNF6zItlukw9irC0OkSqq15I3vUYNUW3QkDcrvkektHKa0r3bdD0gllr9kAl/ICsY+xhsgqmaG3oHt8tN8251f8FKl1ul3e2e5ke0rbsI1tAxsxvcBl+N0iQtSQQJLtzS2Kz+IFpdedUrr2wXjm0zw13UwUZrg8Av+ALYkXprSlEciVBnHG0mOsz8vWO/HPsXx/Jhw/6ER0lm353TEk2OBY65Z1Oi0zl6uozmM2I+zN6XlK7cLvg8nF5HMJ55Xcll6xp748NkDwqEUJ+CsR7dXshTZr9bvWUP3l79ev1+uWDejB9oGikRcgtgEEw5kdgMgraghGlSQ5fgeLc6+e/zer3l5ZbvI6Vr2hQbU8hgZs0BlKRlDPZWkom++6xpFK1pt3o7bbyV5VLuI6Ur0h4cFRkFpcNApgAc1JS6ASaRMpXemHarP4g0LLdhj/Q1O3zltNaQUnbVVA9mJgwWjBu3BsWlNvagmPW71U1O03KZ7iNd3E67jz1G1A1A0UhFm2ZG9xRVGS6pC8eL3nNfT6uUyN5HurRJZK9dYjXeokFR78WU0VHHo/SMIstu7Fl6QMT3SGmVER3wQNvxIWhstVAP6JDZGnA5YdGqGEakRKnW0vfrutcznpdbfnWrV+18Hv01ZqVSC1PFJAddIT00EBdtJoIOD1bObruuZfU9Ul7Vy7K5ZN2MH/zXh9ArwMBE7QGw3ypmW6kgiVqCsEbIxs3qnv96XtXL4T7ScFUva7XVcTCcCNhXazWlhgE9472P1o2xvWy4q17W5VLvXcYLGqpYLq6TQWppkhYyKREYeI921IZow9it3kODu4bb9fvmcuNqGEg2jOxseMQ4o4SeiYBDX30lgU+U2G513zBu5dTtOXWvBZGuITWUALPdTXqGPi34MRHXjEGWyAbfFmt+Wsm927jcxnu3CH7edxlZpmoyKYPki0gHGZ3wIL6QUmw27i3A90BIbvXq64se8LCOyeYaIcQRY5qi0RajUr2pJU2+VWLy6bvVq1v/9OqX193N2yOwHQMpGRf81I0oqaSFTQi4fKq+5PLG6tWr+/QqCwrl9U5pwfx5bq6BJ4AHJw+BI60FgOEALbY2OugORqkdZidPe4YrC5mEdwvn35xd9jZbDDfPGR3bMWoSCB7GnGp0eJsq4TA7q+or4pXdrQxh8facwVK4DDExjrnHCpg6LuGH9lgDd7C1w7F8k3NPxyvBckTo3ZuzxlY9KmhWEs82AhrHwKb3UXvD0KnuyNBjaD4R4+l0weJ+P2vjd567V8Yo7QacWOfWpU4RiWNOTYtiwoZ0mJ2Y8ZXfNdrlsGB6c2bgX2QBjZkDSErIRitKrKBzi1hn22iH2en4K78LlUUPi/jmnGPKsYEw5WGBVqGCiScigBe4cbNzDWMPs2+LiU/Ha1Pq/WHh6M2ZqGkFUJrHIkZ6TtB2NDB8m3Q0lR/9eN99Vfp0utpoJ4dyLe7Os5MC4o++qSPEueLCqG0gUh5TGFqvax7+MDv793nNtEYCvY4E+Tgo1eMoHBprUtM0wa0CHyFhG/hbH1YacfL7Q+w55uU5EVZy9+Knjx1F1nkEAKUHS0WMYGwB3E2tL8ZDcFFHS7Q9XLq06fepK2vqypZJvlToeR6JS+eEfnVlqnYLwV7gmlsbwXEKnxuHL7OTvz3D9cvxRhPpGEWf51IgBbQN6I0JGVQh82oTABZEFzRYxXQ6zE6QlKe+W3tTfTsCv9ThvGaqIpCz3oyodop3Rg9pMVnmtksT7Rsvdt9FyNf2VNf+SQ8RkvYnPOBMMPMpC0CRHWZ+BMMpoSFqJL3HTujh0+ycg1/fOXTtTzW+c/zUpu5RGBV6GWFSmcyKUGHagjNhJD8X5fDudqtzJry4Xd91VHe3sj3gUsgB/K2hsCqjnKRhFmmfm5LiU4tuYFDk3epM7/OaUf9XYVna3W6Yc9EGSL1Qu4PCaxnpxejB+GeL9CLv2hsw47D65vY5FOyavdbdR0sXxKZqswqiHcVDYgpkdZprMB8i1Zrq4HFI8G+5/Yp2Td6Twb2+N18jkJVUcpzfzqjPbx0F1GqwQWLVapIBsNqtzkH0Eu2ir5sfaD7ZHvDIkpKNtfipQUZCD2HSpzyc4WKtr8G6UPaXfXyLuIl2YaTV+2jdxZqdB+QP6OkOSQSNgFmvvmd0kBvWZRdT193q9kOhp1VStEvajd24a8/pmQIkbjSuopCkoHGBUGCUvbrqMJsk83er99HSumQ6NMIxlh7fsVJpA8md4U1FJM2UBooF5hoITUvRH9PrnH5f0fIa9rxrEbdviS65xl5biWzsCKhk1JBJ7PADKq6XDKbndrffhNhXtLwumek+Wn8tpyQn6GdCDVmZq1wM+6SRIAed75Sjsm271S04el4NtG3qDrfhavwUiPJoHopofunpthkNqYLHymSxFWLQ7VbfcvvlduWWj9zS9oBr4dDc3NaCQbYKt8BglJQAmOPcRNQ2yrbWCn/UQLyo6zbedVcmcc3cnGSCtxkO3SpJwWqcn6wdaDFwF3Hjv/H7yP2K1q2SOuTtFm1co2D0bhmYnMlO2QnSjGyzccopdVuA2Hm3uh8FbjFIx/e5Tdd7Q7q3HLo1FoAEavP4nhfB6xAzDWjdEstudZ9bt0bBVg3P7yDP5cAM3naA0qQzgx3UgWW0bMxsmoU28GmkUfeXpZvlyapi5+9d6vXOo1n8x467BTQAoTKoo0PzkBIcNnK57lmytyPeuyXB3LGZ2h9wwafNHpWcDf7zHHoIF26zGSDspJD4LtTd6h4qZDWPHDpiWyh8nlVdznP/pm62T5MHlcLwVR1QhV1bOwXjHXf0tOYP7fOH6fsRQq/6CU/MgGUQ47nPhVuUdUtBQ/N5X5884PZv//yt//SX3//3248XIeRW07qNk9Nex59nVyXanBvk9KRSJQyjUsQQJR7WTnWdD7N3lfzr/wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"ID 2" = _t, Modified = _t, Created = _t, GUID = _t, AuditDateTime = _t, AuditType = _t, LineId = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"ID 2", Int64.Type}, {"Modified", type datetime}, {"Created", type datetime}, {"GUID", type text}, {"AuditDateTime", type datetime}, {"AuditType", type text}, {"LineId", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"LineId", Order.Ascending}, {"AuditDateTime", Order.Descending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Max_", each List.Max(Table.SelectRows(#"Changed Type", (inner)=>inner[LineId] = [LineId])[AuditDateTime]), type datetime),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Max_] = [AuditDateTime])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max_"})
in
    #"Removed Columns"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

@Sonia316 

Here's one option. Place the following M code in a blank query to see the steps. Note the first steps is simply inputting your data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVm7ji25DfyVxY0trEhRD05qBw6cOVxsoCdsGNg1DAP+fZdOa84caU5vMnOFuWw2RbJYxf7llx8uiuiPPz1/h59Jf2b+ST5cuI5pHtl9EOEck+UUqpjKNhthYaOpDdN6oSTN2dzHG7O//v7fn/78j17/hX+T//Hrnx6Ovb0cP36/ONb9CW7+WWpQ17o1XKQbyZmMRuuN9FQ9q1Wb0xuzv9f/5H9Pp+HplJdT3pyK7NYyn5Y5iGNrTWIiI6FYoz1n0y1nbqJtxONdJR7RMj0dy3Isb6N9HO0HzVtPrnqfSzZVixoR702hRqZRzpXUudrSbuXfxxqWy/A21mXsZoaC2BrJF9NSGkaGOlNSTSZYHtnG7GzcX/RxvTeRpuU23UdKH3YeeygtOeuMo1aMuD5M5qSGOVdHlRyNslult5GGldXA9y75w82/2toaJe4Gbq2R1sUkCt2kMCilmOvwZbei9y7dcun2y+XdeBZEiDkQUzANv3C5Fd44JJMputxzk85utwqvLuPT5SqhIPf55I+rnbKGVhTtqahawVWihFhMaMOGgOYdGjerR/e/z2eIy23cL/c1vSj98OiYmmzgajgiPsTVTBZCpC3GNqi51O1u9b5ywyqhsJeQuM34Udc15dFsV4PMNUSq0WhwQKhehrOq3Zb+3ep9pHEhUrT3kcoHz4LQDByk4UwbGW5Da6ao4Nhwv5ZyrHpYvcejuCo38n2keIN5tCUTxYIy6gM5HaGgaIM1lmrWHDI7T7sV30e6Sim+1o5+OP9y9Beu9Ky+RngMJVdEOlBKHbecuLrwKO08dit5H+lCoxg2l0KbMT8y3/IYbXgTahIj0Q1TPLBhiB3NqXRfy251j7txlVJMe6SvgYcPmn/tbL0T9GmU2TRZPKAhyixhiV1UdMMXWMW3kaZVRsneRxoucGol56ahGnJzoMbggAqo3lDcBN3UGufdKt1GmlYpJd4jfS2leEWKC5bRPSItGeM0MJsSGDmNYzgKKfjSdqubSFcZJbmPNF6zItlukw9irC0OkSqq15I3vUYNUW3QkDcrvkektHKa0r3bdD0gllr9kAl/ICsY+xhsgqmaG3oHt8tN8251f8FKl1ul3e2e5ke0rbsI1tAxsxvcBl+N0iQtSQQJLtzS2Kz+IFpdedUrr2wXjm0zw13UwUZrg8Av+ALYkXprSlEciVBnHG0mOsz8vWO/HPsXx/Jhw/6ER0lm353TEk2OBY65Z1Oi0zl6uozmM2I+zN6XlK7cLvg8nF5HMJ55Xcll6xp748NkDwqEUJ+CsR7dXshTZr9bvWUP3l79ev1+uWDejB9oGikRcgtgEEw5kdgMgraghGlSQ5fgeLc6+e/zer3l5ZbvI6Vr2hQbU8hgZs0BlKRlDPZWkom++6xpFK1pt3o7bbyV5VLuI6Ur0h4cFRkFpcNApgAc1JS6ASaRMpXemHarP4g0LLdhj/Q1O3zltNaQUnbVVA9mJgwWjBu3BsWlNvagmPW71U1O03KZ7iNd3E67jz1G1A1A0UhFm2ZG9xRVGS6pC8eL3nNfT6uUyN5HurRJZK9dYjXeokFR78WU0VHHo/SMIstu7Fl6QMT3SGmVER3wQNvxIWhstVAP6JDZGnA5YdGqGEakRKnW0vfrutcznpdbfnWrV+18Hv01ZqVSC1PFJAddIT00EBdtJoIOD1bObruuZfU9Ul7Vy7K5ZN2MH/zXh9ArwMBE7QGw3ypmW6kgiVqCsEbIxs3qnv96XtXL4T7ScFUva7XVcTCcCNhXazWlhgE9472P1o2xvWy4q17W5VLvXcYLGqpYLq6TQWppkhYyKREYeI921IZow9it3kODu4bb9fvmcuNqGEg2jOxseMQ4o4SeiYBDX30lgU+U2G513zBu5dTtOXWvBZGuITWUALPdTXqGPi34MRHXjEGWyAbfFmt+Wsm927jcxnu3CH7edxlZpmoyKYPki0gHGZ3wIL6QUmw27i3A90BIbvXq64se8LCOyeYaIcQRY5qi0RajUr2pJU2+VWLy6bvVq1v/9OqX193N2yOwHQMpGRf81I0oqaSFTQi4fKq+5PLG6tWr+/QqCwrl9U5pwfx5bq6BJ4AHJw+BI60FgOEALbY2OugORqkdZidPe4YrC5mEdwvn35xd9jZbDDfPGR3bMWoSCB7GnGp0eJsq4TA7q+or4pXdrQxh8facwVK4DDExjrnHCpg6LuGH9lgDd7C1w7F8k3NPxyvBckTo3ZuzxlY9KmhWEs82AhrHwKb3UXvD0KnuyNBjaD4R4+l0weJ+P2vjd567V8Yo7QacWOfWpU4RiWNOTYtiwoZ0mJ2Y8ZXfNdrlsGB6c2bgX2QBjZkDSErIRitKrKBzi1hn22iH2en4K78LlUUPi/jmnGPKsYEw5WGBVqGCiScigBe4cbNzDWMPs2+LiU/Ha1Pq/WHh6M2ZqGkFUJrHIkZ6TtB2NDB8m3Q0lR/9eN99Vfp0utpoJ4dyLe7Os5MC4o++qSPEueLCqG0gUh5TGFqvax7+MDv793nNtEYCvY4E+Tgo1eMoHBprUtM0wa0CHyFhG/hbH1YacfL7Q+w55uU5EVZy9+Knjx1F1nkEAKUHS0WMYGwB3E2tL8ZDcFFHS7Q9XLq06fepK2vqypZJvlToeR6JS+eEfnVlqnYLwV7gmlsbwXEKnxuHL7OTvz3D9cvxRhPpGEWf51IgBbQN6I0JGVQh82oTABZEFzRYxXQ6zE6QlKe+W3tTfTsCv9ThvGaqIpCz3oyodop3Rg9pMVnmtksT7Rsvdt9FyNf2VNf+SQ8RkvYnPOBMMPMpC0CRHWZ+BMMpoSFqJL3HTujh0+ycg1/fOXTtTzW+c/zUpu5RGBV6GWFSmcyKUGHagjNhJD8X5fDudqtzJry4Xd91VHe3sj3gUsgB/K2hsCqjnKRhFmmfm5LiU4tuYFDk3epM7/OaUf9XYVna3W6Yc9EGSL1Qu4PCaxnpxejB+GeL9CLv2hsw47D65vY5FOyavdbdR0sXxKZqswqiHcVDYgpkdZprMB8i1Zrq4HFI8G+5/Yp2Td6Twb2+N18jkJVUcpzfzqjPbx0F1GqwQWLVapIBsNqtzkH0Eu2ir5sfaD7ZHvDIkpKNtfipQUZCD2HSpzyc4WKtr8G6UPaXfXyLuIl2YaTV+2jdxZqdB+QP6OkOSQSNgFmvvmd0kBvWZRdT193q9kOhp1VStEvajd24a8/pmQIkbjSuopCkoHGBUGCUvbrqMJsk83er99HSumQ6NMIxlh7fsVJpA8md4U1FJM2UBooF5hoITUvRH9PrnH5f0fIa9rxrEbdviS65xl5biWzsCKhk1JBJ7PADKq6XDKbndrffhNhXtLwumek+Wn8tpyQn6GdCDVmZq1wM+6SRIAed75Sjsm271S04el4NtG3qDrfhavwUiPJoHopofunpthkNqYLHymSxFWLQ7VbfcvvlduWWj9zS9oBr4dDc3NaCQbYKt8BglJQAmOPcRNQ2yrbWCn/UQLyo6zbedVcmcc3cnGSCtxkO3SpJwWqcn6wdaDFwF3Hjv/H7yP2K1q2SOuTtFm1co2D0bhmYnMlO2QnSjGyzccopdVuA2Hm3uh8FbjFIx/e5Tdd7Q7q3HLo1FoAEavP4nhfB6xAzDWjdEstudZ9bt0bBVg3P7yDP5cAM3naA0qQzgx3UgWW0bMxsmoU28GmkUfeXpZvlyapi5+9d6vXOo1n8x467BTQAoTKoo0PzkBIcNnK57lmytyPeuyXB3LGZ2h9wwafNHpWcDf7zHHoIF26zGSDspJD4LtTd6h4qZDWPHDpiWyh8nlVdznP/pm62T5MHlcLwVR1QhV1bOwXjHXf0tOYP7fOH6fsRQq/6CU/MgGUQ47nPhVuUdUtBQ/N5X5884PZv//yt//SX3//3248XIeRW07qNk9Nex59nVyXanBvk9KRSJQyjUsQQJR7WTnWdD7N3lfzr/wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"ID 2" = _t, Modified = _t, Created = _t, GUID = _t, AuditDateTime = _t, AuditType = _t, LineId = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"ID 2", Int64.Type}, {"Modified", type datetime}, {"Created", type datetime}, {"GUID", type text}, {"AuditDateTime", type datetime}, {"AuditType", type text}, {"LineId", Int64.Type}}),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"LineId", Order.Ascending}, {"AuditDateTime", Order.Descending}})),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"LineId"})
in
    #"Removed Duplicates"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

Hi @Sonia316 

Can you share some sample data (in text-tabular format so that it can be copied) aling with the expected result?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Sonia316]

Thank you - I will try all of these...seems like such a large amount of code...

[AlB]

@Sonia316 

---

@Sonia316 wrote:

Thank you - I will try all of these...seems like such a large amount of code...

---

It's not.

Like I said earlier, the first step, which takes up almost all the space, is just to copy the input data. It will not be necessary when you load your actual data.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Sonia316]

My data resides in SharePoint - all 3 lists.  Does that make a difference?

[AlB]

It doesn't.

Note in the examples I have taken part of your data only (all rows but only a few relevant columns)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Sonia316]

Ok perfect - I will be back. ty!😀

[Sonia316]

I was able to add one of them.

but it only shows the two columns - While I do need these I also need the measurement columns.

[AlB]

I don't follow

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Sonia316]

I added the Power Query to my Quality Control list and fixed the list connection (mapping of Ids) but all that showed up were those two fields, and the rest just disappeared.  

Was I supposed to create a seperate query?

[Sonia316]

Link to Sample - https://docs.google.com/spreadsheets/d/1mOAo4i0i1-Q4WqyvltyytXzOCEIdhmOq/edit?usp=sharing&ouid=11155...

What I would like to accomplish is to only show the most current AuditDateTime per line.