What's the most efficient way to narrow down the data?

[SoDoLabs]

Hi Power BI Community,

I have a dataset that has 1.4 million records; however, initially, I only want to grab records based a single criterion (campaign field is not null).

Below are my steps in the Query Editor:

1. Removed most of the columns, only kept 15 of the columns. 
2. Filtered rows based on the campaign field (not null).
3. Changed "modifiedon" field to Datetime format.
4. Filtered rows based on RangeStart and RangeEnd paramenters (only return records that were modified within a year).
   • One major issue: all 1.4 million records were modified within a year.

I want to use the incremental refresh to only pick up updated data moving forward. Based on my steps above, I'm getting all 1.4 million records from the start (Apply query changes are taking quite a long time.).

Am I doing this right? How can I improve it?

Thank you for the support!

[v-piga-msft]

Hi @SoDoLabs,

What is your data source?

Normally, we could clear data in Query Editor as you did in Query Editor.

In addition, you could could use T-SQL Query to pull the data you want.

Based your description, it seems that you want to use incremental refresh , please note that the feature is for Power BI Premium. 

Best  Regards,

Cherry

[SoDoLabs]

Hi @v-piga-msft,

Thank for replying!

My data source is Dynamics 365 CRM. 

Since the data is from Dynamics 365 CRM directly, T-SQL is not an option, unless I load the data into a on-prem database. 

We do have Power BI Premium; however, the refresh time (w/ incremental refresh) was way to long. I found other posts on the Power BI Comunity that mentioned the same thing, the api connection is very slow.

Any sggestions?

Thanks,

Joe