Filtering Large Datasets from Dynamic CRM to Power BI

[AFRATH24]

Hi Team,

I am getting an error when connecting Dynamic CRM to Power BI due to the large data size. so could you please advise to filter the data before importing it to Power BI.

• When i load data with Direct query source mode it's connecting, i used below query

          Get data ---> Blank query ---> Advanced Editor ---> paste below query
         

 Source = CommonDataService.Database("xxxxxxx.crm6.dynamics.com", [CreateNavigationProperties=false]),
          dbo_opportunity = Source{[Schema="dbo",Item="opportunity"]}[Data]
           in
           dbo_opportunity

• I need to filter based on 'Estimated Close Date' greater than or equal to January 1, 2022, and filter a few required columns from the CRM, such as Opportunity ID, Owner, Account, Topic, Pipeline, Total Revenue, Opportunity Type, etc. Could someone please provide a filter query and advise me on how to make it easy?

Please provide your feedback; it is greatly appreciated.

Thanks,

Afrath

[aduguid · Accepted solution]

I'd try this 

let
    Source = CommonDataService.Database("xxxxxxx.crm6.dynamics.com", [CreateNavigationProperties=false]),
    dbo_opportunity = Source{[Schema="dbo",Item="opportunity"]}[Data],
    FilteredRows = Table.SelectRows(dbo_opportunity, each [EstimatedCloseDate] >= #date(2022, 1, 1)),
    SelectColumns = Table.SelectColumns(FilteredRows, {"OpportunityID", "Owner", "Account", "Topic", "Pipeline", "TotalRevenue", "OpportunityType"}),
    RenamedColumns = Table.RenameColumns(SelectColumns, {"OpportunityID", "Opportunity ID", "Owner", "Account", "Topic", "Pipeline", "TotalRevenue", "OpportunityType"})
in
    RenamedColumns