Forum Discussion

AFRATH24's avatar
AFRATH24
Regular Visitor
2 years ago
Solved

Filtering Large Datasets from Dynamic CRM to Power BI

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

Need Help
Tutorial Requests
  • aduguid's avatar
    aduguid
    2 years ago

    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

1 Reply

  • aduguid's avatar
    aduguid
    Memorable Member
    2 years ago

    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