Forum Discussion

stan_w_gifford's avatar
stan_w_gifford
Helper I
4 years ago
Solved

FIlter data on retrieve

Good morning from an extremely wet Sydney Australia.

 

I am developing a report where I would like to retrieve data from Dynamics (Dataverse) based on a smaller table from dataverse.

 

I have a table that has a number of GUID entries which point to Sales orders in CRM. The Guids in the smaller table will have duplicates.

 

I would only like to retrieve from Dataverse those orders that match the GUID's in the smaller table.

 

The goal is to refresh the data much quicker - I would only need to retrieve about 1% of the order data - the order table is huge.

 

Is there any way to construct the retrieve that anyone can advise on?

 

Stan

Need Help

4 Replies

  • stan_w_gifford's avatar
    stan_w_gifford
    Helper I
    4 years ago

    I did find out a partial solution using TDS.

     

    For example if I just want salesorders which were created after 2021-01-01, I found that using the current version of PBI Desktop, the sequence is......

    1. CLick Dataverse

    2. Select environment

    3. Select Salesorder - Then hit transform data

    4. Then select Advanced editor

    Replace EVERYTHING that is presented with something like

     

     

    let Source = CommonDataService.Database("yourenvironment.crm6.dynamics.com"),
    DataverseSQL=Value.NativeQuery(Source,"Select * from salesorder where createdon > '2021-01-01'",null,[EnableFolding=true])
    in
    DataverseSQL

     

    STan

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Stan,

       

      Did you mange to make this work?

       

      when I'm working with dataverse my pipeline is usually Datavers -> PowerBI Dataflow -> PowerBI

       

      Because i noticed that the basice "cleaning" like column selection, and let's say a status filtering, are faster in the dataflow (furthermore, like this we are not querying the dataverse with 100 reports 😄 )

       

      I'm also facing your same issue, that it would be good to have some more effective folding method, and not so headscratching as using the XMLfetch tool to pre-filter the dataverse query.

       

      Unfortunately what you wrote here above, is not working for me (tested on the standard account entity/table)

       ğŸ˜•

       

      Regards,

      Oliver

      • stan_w_gifford's avatar
        stan_w_gifford
        Helper I
        3 years ago

        Hi,

        I didn't go much further on this - I found that using TDS was sufficient for my purposes.

         

        PowerBI Data Flow is a new one on me - sounds like some research may be warranted.

         

        All the best

         

        Stan