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

[jenzushsu]

I have a situation where I need to only use the latest sheets based on the date/time indicated as the sheet name. How can I dynamically filter for the latest sheet of each day?

[v-juanli-msft]

Hi @jenzushsu 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[Anonymous]

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)

[v-juanli-msft]

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.

[VasTg]

@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