Earliest and latest timestamp

[radick201]

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.

[DataInsights · Accepted solution]

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

Result:

[radick201]

This works perfectly, thank you!