Best way to speed up a report by limiting data? Filtering dates on a pattern?

[Chrono9084]

My goal is to limit the data that PowerBI is working with to help my report load faster and work better.

The Data

My data currently comes in the form of a spreadsheet emailed daily from our server.  It looks like this:

#	Software Name	Version	Last User	Machine Name	Last Sync
1	AutoCAD Civil 3D 2011 64 Bit Object Enabler on Autodesk Revit Architecture 2011 x64 - Language Neutral	1477
2	AutoCAD Civil 3D 2011 64 Bit Object Enabler on Autodesk Revit Structure 2011 x64 - Language Neutral	1477
3	Autodesk 360 Structural Analysis plug-in for Autodesk Revit 2013	2013.0.0.337
4	Autodesk Advance Steel 2015.1 Extension for Autodesk Revit	15.2.0.0
5	Autodesk Advance Steel 2019 Extension for Autodesk Revit	19.1.106.0	That Guy	WINSURBJP	08/10/2021 06:58:19
6	Autodesk Advance Steel 2019 Extension for Autodesk Revit	19.1.106.0	Some User 2	LHD57P13	08/09/2021 22:34:04
7	Autodesk Advance Steel 2019 Extension for Autodesk Revit	19.1.106.0	Some User	LFBDFP73	08/09/2021 16:56:01

Because the file name is the same every day, I have Power Automate copying these files from my email every day and putting them on sharepoint while also renaming them with the current date such that the filename looks like this: 20200810NameOfFile.xls (that is YYYYMMDD date format).

The final step is having PowerBI join these files using a Query where it grabs the entire folder, filters out files that do not apply, creates a new column and applies the files date to each line from that file (this is required as the last sync date has nothing to do with the date of the report).

My Goal

I would like the query to do two things:

1. Keep the last month of data (all days).
2. Filter all remaining data and keep one day from each week (basically reducing older historical data by 86%).

Thanks in advance!

[v-yiruan-msft · Accepted solution]

Hi @Chrono9084 ，

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Add a custom column to get the dates which filter the conditions(dates from last month and one day from each week), otherwise it return null

Note: I just keep the dates which the day of date is on Monday and dates from last month, you can adjust the condition base on your scenario....

2. Filter the new column with the dates are not null

The full applied codes as below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZXbboJAEIZfZeJ1hT1w9A5PPcRYozW9MF6gbpWGQsPB6Nt3Fk1T2NYmFmsg2U3Y+b9h5h+YzRq0cdPw8izueF3oBNsgBN4FRigFy4B2kMHj4lUsM+hF/iIUCcQRyOMrkcJYbPG5lyw3QYZH8kQcAncY2YSBH61zfy1gKPIs8UPkUMO2cTne85tZg/2VPsmS/Dw0P6KlFrfIp5Ifghf54T4NUngP83UziOAlTipcpEkBuWgEL84r8sYXeW+19aOlQIQQoQw1NQq9XSaiNIi/UZf5mhqTwmVR85So+5ukq1GNEqsQfdr4Gdzme9w+3w8n03H7YYR7R6dEZ4Rh/VumUzCt2piT+E3ANMU2yrYP7rqmPSqq6OjuAcpYixsF1a6fKpn9drc/sktMim9qFUznWqNg68yqJuNeZTIwE7uaCSUXmxRkOQqOnmr9GZODEKXVlNXmrx8mydY5Uai8fldXZwm5VOEa13M2VWtvXsvaakOsS1pb7cPJr9p51mYKxLm8tRlXqO5/WJsZVS4j9XPLPwqkmiXq/AM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"#" = _t, #"Software Name" = _t, Version = _t, #"Last User" = _t, #"Machine Name" = _t, #"Last Sync" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"#", Int64.Type}, {"Software Name", type text}, {"Version", type text}, {"Last User", type text}, {"Machine Name", type text}, {"Last Sync", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Max date", each if Date.Year([Last Sync])
        = Date.Year(List.Max(#"Changed Type"[Last Sync])) and Date.Month([Last Sync])
        = Date.Month(List.Max(#"Changed Type"[Last Sync]))
      then
        [Last Sync]
      else if Date.DayOfWeek([Last Sync]) = 1 then
        [Last Sync]
      else
        null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Max date] <> null))
in
    #"Filtered Rows"

Best Regards