Need Help in Creating Unique list in Power Query

Power Query
1

Hello Guys,

Am looking to generate the List in Power query for large data where I need the latest entry based the Date and Time based on each Week Number.

1.Please find the sample data in excel where am unable to convert column A into date time format in PQ.
2. Please find the solution table am looking to generate in PQ based on the sample data.

Sample_Data.xlsx (9.8 KB)
Thank in Advance.

2

Hi @Dharma,

It seems from your sample your data is structured, if that is the case you can try this.
Just paste the full code below into a new blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZC9DoIwFIVfhXSG0HvbUno3djUOJsYQBmJKYtzU94/AIEJbKi69Hc6X81PXrMjKDDkCS9nFPvsXxv/Z2vtwd+3LsiaN6k6Ptutu1+1Ss5RiRJpwIDABYIqbg8pHORAvCXRy3HvyYJAxpFaYWbAJ4wSClEyq7Zwkwf/x0ySLuR/6uyF+umGwmwgx0G8YyCecfBOmNCncjHGSmjj4Z1zhYJgfzdxPRKoBYeFs/w0dKhcR0uklfsmnSpILK/RxzRs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Attendance Punch" = _t, ID = _t, #"Week Type" = _t, Reason = _t]),
    GroupAndTransform = Table.Group(Source, {"ID", "Week Type"}, {{"AllRows", each Table.LastN( _, 1), type table [Date=nullable text, Attendance Punch=nullable text, ID=nullable text, Week Type=nullable text, Reason=nullable text]}})[[AllRows]],
    ExpandTable = Table.ExpandTableColumn(GroupAndTransform, "AllRows", {"Date", "Attendance Punch", "ID", "Week Type", "Reason"}, {"Date", "Attendance Punch", "ID", "Week Type", "Reason"})
in
    ExpandTable

All I’m doing is grouping on ID and Week Type and keeping the last entry from that nested table.

If this doesn’t get you the expected result, you’ll have to add some additional filter logic.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZC9DoIwFIVfhXSG0HvbUno3djUOJsYQBmJKYtzU94/AIEJbKi69Hc6X81PXrMjKDDkCS9nFPvsXxv/Z2vtwd+3LsiaN6k6Ptutu1+1Ss5RiRJpwIDABYIqbg8pHORAvCXRy3HvyYJAxpFaYWbAJ4wSClEyq7Zwkwf/x0ySLuR/6uyF+umGwmwgx0G8YyCecfBOmNCncjHGSmjj4Z1zhYJgfzdxPRKoBYeFs/w0dKhcR0uklfsmnSpILK/RxzRs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Attendance Punch" = _t, ID = _t, #"Week Type" = _t, Reason = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each try DateTime.From([Date]) otherwise Date.From([Date])),
    GroupAndTransform = Table.Group(#"Added Custom", {"ID", "Week Type"}, {{"AllRows", (IT) => Table.LastN( Table.SelectRows( IT, each [Custom] = List.Max( IT[Custom])), 1), type table [Date=nullable text, Attendance Punch=nullable text, ID=nullable text, Week Type=nullable text, Reason=nullable text, Custom=datetime]}})[[AllRows]],
    ExpandTable = Table.ExpandTableColumn(GroupAndTransform, "AllRows", {"Date", "Attendance Punch", "ID", "Week Type", "Reason"}, {"Date", "Attendance Punch", "ID", "Week Type", "Reason"})
in
    ExpandTable

.
I hope this is helpful.

1 Like
3

Hi @Dharma, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

4

Hi @Dharma, we’ve noticed that no response has been received from you since Sep 2.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Hi @Dharma, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please create a new thread.