cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jenzushsu
Microsoft
Microsoft

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

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?

 

Capture.PNG

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

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
mcybulski
Solution Specialist
Solution Specialist

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
Community Support
Community Support

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.

Capture4.JPGCapture5.JPG

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
Memorable Member
Memorable Member

@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"

 

InputInput

Output... Take out the unnecessary columns.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

Connect on LinkedIn

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors