Irregular Extract Files & Time Intelligence

Power Query
4

Okay so interacting with the data model itself is probably not that intuitive…but let’s see how far we can take it. First the source FolderContents query

  • Add a Date column Date.From([Extract Date]), type date
  • Create a ListFileDates query from a blank query with this M code
    List.Sort( List.Distinct(FolderContents[Date]), Order.Descending )
  • Create a new Parameter SelectDate via “Manage Parameters” on the ribon
    Type = Date, Suggested Values = Query, Query = ListFileDates
  • Create a LastDate query from a blank query with this M code
    List.FirstN(ListFileDates, 1)
  • Create a new Parameter SelectLastDate via “Manage Parameters” on the ribon
    Type = Date, Suggested Values = Query, Query = LastDate

.
The Today query equals the most recent file, that’s no problem.

  1. Duplicate the FolderContents query
  2. Sort the Extract Date column in Decending order
  3. Only Keep the top row
  4. Extract the Binairy content
  5. Perform all other transformations

.
The second query aka Last Year equals the most recent file from one year ago, that’s no problem.

  1. Duplicate the FolderContents query
  2. Sort the Extract Date column in Decending order
  3. Place a Filter on [Date] <= Date.AddYears(SelectLastDate, -1)
  4. Only Keep the top row
  5. Extract the Binairy content
  6. Perform all other transformations

.
The third query aka User Selection would basically follow the same pattern as the Last Year query with a few exeptions:
The Parameter value has to be selected. And here’s the catch Parameters are hard to interact with for ‘users’. If they have acces to the PBIX file and view it from within Power BI Desktop they can change the Parameter by going to the Home tab, Edit Queries and select Edit Parameters.

However if they only have acces to the report from within the Power BI Service, the only way to change a Parameter would be by going to that reports Workspace, Dataset (select the elipsis …), choose Schedule Refresh and there you’ll find Parameters…

I believe your Parameter must be either type Text or Decimal to be able to be changed so a duplicate SelectDate Parameter has to be created, let’s call it SelectDateTypeText and when you call that Parameter just wrap it in Date.From(SelectDateTypeText) to turn it back into a Type date …

Is this what you had in mind?

3 Likes