Dynamically select sheets based on latest date/time as sheet name

jenzushsu 

Assuming you don't have data for more than 1 year. Here is the M query get the latest row.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMMzaINzRMzFWK1QFzjSzjLQuQeUbGBih8QxS+BVAzCtcMVRKJZx5vgaLVPN4chWcJckQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}}), 
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Item", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Item.1", "Item.2"}),/*Split by delilmiter _*/
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Item.1", type date}, {"Item.2", type text}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type1", "Item.2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Item.2.1", "Item.2.2"}),/*select the am/pm column; split->digits to non digits*/
    #"Added Custom" = Table.AddColumn(#"Split Column by Character Transition", "Minutes", each if Text.Length([Item.2.1]) > 2 then Text.End([Item.2.1],2) else 00),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Hour", each if Text.Length([Item.2.1]) =3 then Text.Start([Item.2.1],1) else if Text.Length([Item.2.1]) =4 then Text.Start([Item.2.1],2) else [Item.2.1]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Hour", Int64.Type}, {"Minutes", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type2", "Custom", each if [Item.2.2] = "pm" then ([Hour]*60)+720+[Minutes] else ([Hour]*60)+[Minutes]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Custom", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type3", let latest = List.Max(#"Changed Type3"[Item.1]) in each [Item.1] = latest),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Custom", Order.Descending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",1)
in
    #"Kept First Rows"

Input

Output... Take out the unnecessary columns.

Edit: I overlooked the question and filter the latest row alone instead of latest for each day. Remove the steps from "#Filtered Rows" and create an index column based on Descending Custom but starts from 1 for each day. Then filter for value 1. 

Refer to this post on how to create the custom index column

https://community.powerbi.com/t5/Desktop/index-of-repeating-numbers/td-p/231888

If it helps, mark it as a solution

Kudos are nice too

Hi jenzushsu 

VasTg's contribution is valuable.

Based on his suggestion, i modified the code as below, please replace my excel sheet path with yours and check if it helps.

let
    FullFilePath = "C:\Users\maggiel\Desktop\case\2\test folder\test file.xlsx",
    Source = Excel.Workbook(File.Contents(FullFilePath)),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Item", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Item.1", "Item.2"}),
    #"split by_" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Item.1", type date}, {"Item.2", type text}}),
    #"split by am/pm"=Table.SplitColumn(#"split by_", "Item.2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Item.2.1", "Item.2.2"}),
    #"Added Custom" = Table.AddColumn(#"split by am/pm", "hour", each if Text.Length([Item.2.1]) =3 then Text.Start([Item.2.1],1) else if Text.Length([Item.2.1]) =4 then Text.Start([Item.2.1],2) else [Item.2.1]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "minute", each if Text.Length([Item.2.1]) > 2 then Text.End([Item.2.1],2) else 00),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom1", {{"minute", type text}}, "en-US"),{"hour", "minute"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"hh:mm"),
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"hh:mm", type duration}}),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"hh:mm", type text}}, "en-US"),{"hh:mm", "Item.2.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"time"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns1",{{"time", type time}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Item.2.1"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Item.1"}, {{"last time", each List.Max([time]), type time}, {"all", each _, type table [Name=text, Data=table, Item.1=date, time=time, Kind=text, Hidden=logical]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Name", "Data", "Item.1", "time", "Kind", "Hidden"}, {"all.Name", "all.Data", "all.Item.1", "all.time", "all.Kind", "all.Hidden"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded all", "filter", each if [last time] = [all.time] then 1 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each [filter] <> null and [filter] <> "")
in
    #"Filtered Rows"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Try inserting these two lines in your code.

    MaxSheet = List.Max(List.Transform(FolderTable[Item], each DateTime.From(Text.Replace(_,"_"," ")))),
    #"Filtered Rows" = Table.SelectRows(FolderTable, each DateTime.From(Text.Replace([Item],"_"," ")) = MaxSheet)

Hi jenzushsu