cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
radick201
Regular Visitor

Earliest and latest timestamp

I have a query that pulls excel files from a sharepoint link where all files named with the format yy.MM.dd.hhmm.xlsx

for each day there can range from 0 to 100+ files for each day for example:

22.05.23.0600.xlsx

22.05.23.0714.xlsx

22.05.23.1239.xlsx

22.05.23.1601.xlsx

22.05.24.0500.xlsx

22.05.24.1239.xlsx

22.05.24.1601.xlsx

22.05.25.1600.xlsx

I was able to pull a query which converts the file name into a date time format but I need to compile the records of each day's first of the day and latest of each day.  If there is only 1 record for the day it should only pull once

So for above the results should pull records for

22.05.23.0600.xlsx

22.05.23.1601.xlsx

22.05.24.0500.xlsx

22.05.24.1601.xlsx

22.05.25.1600.xlsx

I am pretty sure I need to create a DAX formula using calculate and filters but not sure how the approach should be.

Any help is appreciated.

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@radick201,

 

This sounds like a task better suited for Power Query. The solution below imports only the first and last file for each day. The concept is to use the Group By function in Power Query to get the Min and Max time for each date, and then flag those files for import (excluding the other files). The M code below would be extended to include the steps to import the selected files.

 

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjLSMzDVMzLWMzAzMNCryCmuUIrVQRY2NzTBImxoZGyJTdjMwBBD2ARIYjHbBLshJtgNMQUJwwyJBQA=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Filename = _t]
  ),
  ChangeType = Table.TransformColumnTypes(Source, {{"Filename", type text}}),
  AddFilenameParts = Table.AddColumn(ChangeType, "FilenameParts", each [Filename]),
  ReplaceValue = Table.ReplaceValue(
    AddFilenameParts,
    ".xlsx",
    "",
    Replacer.ReplaceText,
    {"FilenameParts"}
  ),
  SplitColumn = Table.SplitColumn(
    ReplaceValue,
    "FilenameParts",
    Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true),
    {"FilenameParts.1", "FilenameParts.2"}
  ),
  ChangedType2 = Table.TransformColumnTypes(
    SplitColumn,
    {{"FilenameParts.1", type text}, {"FilenameParts.2", Int64.Type}}
  ),
  RenameColumns = Table.RenameColumns(
    ChangedType2,
    {{"FilenameParts.1", "FilenameDate"}, {"FilenameParts.2", "FilenameTime"}}
  ),
  GroupRows = Table.Group(
    RenameColumns,
    {"FilenameDate"},
    {
      {"MinTime", each List.Min([FilenameTime]), type nullable number},
      {"MaxTime", each List.Max([FilenameTime]), type nullable number},
      {
        "AllRows",
        each _,
        type table [
          Filename = nullable text,
          FilenameDate = nullable text,
          FilenameTime = nullable number
        ]
      }
    }
  ),
  ExpandAllRows = Table.ExpandTableColumn(
    GroupRows,
    "AllRows",
    {"Filename", "FilenameTime"},
    {"Filename", "FilenameTime"}
  ),
  AddIsMinOrMaxTime = Table.AddColumn(
    ExpandAllRows,
    "IsMinOrMaxTime",
    each if [FilenameTime] = [MinTime] or [FilenameTime] = [MaxTime] then 1 else 0
  ),
  FilterRows = Table.SelectRows(AddIsMinOrMaxTime, each ([IsMinOrMaxTime] = 1))
in
  FilterRows

 

Screenshot of the Group By screen:

 

DataInsights_0-1653328365057.png

 

Result:

 

DataInsights_1-1653328378822.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
DataInsights
Super User
Super User

@radick201,

 

This sounds like a task better suited for Power Query. The solution below imports only the first and last file for each day. The concept is to use the Group By function in Power Query to get the Min and Max time for each date, and then flag those files for import (excluding the other files). The M code below would be extended to include the steps to import the selected files.

 

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjLSMzDVMzLWMzAzMNCryCmuUIrVQRY2NzTBImxoZGyJTdjMwBBD2ARIYjHbBLshJtgNMQUJwwyJBQA=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Filename = _t]
  ),
  ChangeType = Table.TransformColumnTypes(Source, {{"Filename", type text}}),
  AddFilenameParts = Table.AddColumn(ChangeType, "FilenameParts", each [Filename]),
  ReplaceValue = Table.ReplaceValue(
    AddFilenameParts,
    ".xlsx",
    "",
    Replacer.ReplaceText,
    {"FilenameParts"}
  ),
  SplitColumn = Table.SplitColumn(
    ReplaceValue,
    "FilenameParts",
    Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true),
    {"FilenameParts.1", "FilenameParts.2"}
  ),
  ChangedType2 = Table.TransformColumnTypes(
    SplitColumn,
    {{"FilenameParts.1", type text}, {"FilenameParts.2", Int64.Type}}
  ),
  RenameColumns = Table.RenameColumns(
    ChangedType2,
    {{"FilenameParts.1", "FilenameDate"}, {"FilenameParts.2", "FilenameTime"}}
  ),
  GroupRows = Table.Group(
    RenameColumns,
    {"FilenameDate"},
    {
      {"MinTime", each List.Min([FilenameTime]), type nullable number},
      {"MaxTime", each List.Max([FilenameTime]), type nullable number},
      {
        "AllRows",
        each _,
        type table [
          Filename = nullable text,
          FilenameDate = nullable text,
          FilenameTime = nullable number
        ]
      }
    }
  ),
  ExpandAllRows = Table.ExpandTableColumn(
    GroupRows,
    "AllRows",
    {"Filename", "FilenameTime"},
    {"Filename", "FilenameTime"}
  ),
  AddIsMinOrMaxTime = Table.AddColumn(
    ExpandAllRows,
    "IsMinOrMaxTime",
    each if [FilenameTime] = [MinTime] or [FilenameTime] = [MaxTime] then 1 else 0
  ),
  FilterRows = Table.SelectRows(AddIsMinOrMaxTime, each ([IsMinOrMaxTime] = 1))
in
  FilterRows

 

Screenshot of the Group By screen:

 

DataInsights_0-1653328365057.png

 

Result:

 

DataInsights_1-1653328378822.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




This works perfectly, thank you!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors