Last 3 years data restriction in power query for the large dataset

[PrakashPalle]

Hi All,

I have a fact table containing 100 million records and i want restrict the data for last 3 years. How can i restrict the data in power query? I have date column available in the fact table. My data keeps on adding due to limitation in power bi i cannot use the direct query connection and i have used import method. Please help

[123abc]

To restrict data to the last 3 years in Power Query for a large dataset with 100 million records in Power BI, you can follow these steps:

1. Open your Power BI Desktop file.

2. In the Power Query Editor, you can apply a filter to your date column to keep only the records from the last 3 years. Here's how you can do that:

   a. Select the date column in your fact table.

   b. Go to the "Home" tab in the Power Query Editor.

   c. Click on the "Filter Rows" dropdown and select "Date Filters."

   d. Choose "After" and then select the date that is 3 years ago from the current date. You can use a dynamic approach like this to calculate that date:

M Language Scripting:

Date.From(DateTime.LocalNow() - #duration(365*3, 0, 0, 0))

1. e. Click OK. This will filter your data to keep only records that are after the specified date.

2. After applying the filter, click "Close & Apply" to load the filtered data into your Power BI report.

Keep in mind that filtering such a large dataset might still take some time, and you should consider the performance impact of working with a dataset of this size in Power BI, especially in the import mode. Depending on your hardware and the complexity of your data model, you may experience longer refresh times and increased memory usage. It's a good practice to optimize your data model and use appropriate data compression techniques to improve performance in such cases.