Forum Discussion

mike_asplin's avatar
mike_asplin
Helper V
4 months ago
Solved

Bit of help to identifying a dynamic data source

Hi. I have a modle that pulls some of its data form Microsoft Dynmics. Some very helpful people guided me how to create a url that pulled in specific column and dates rather than pulling whole thing ...
Need Help
  • vojtechsima's avatar
    vojtechsima
    4 months ago

    Hey, mike_asplin ,

    basically in your case, you can jsut do this:

    let
    BaseUrl = "https://chxxxxd.api.crm11.dynamics.com",
    Endpoint = "api/data/v9.1/bookableresourcebookings",
    
    SelectColumns = "bookableresourcebookingid,_bookingstatus_value,_resource_value,name,_cha_clientid_value,_owningteam_value,duration,starttime,_cha_carercontactid_value,_msdyn_workorder_value,msdyn_totalcost,endtime,statuscode,msdyn_milestraveled,msdyn_actualarrivaltime",
    
    FilterDateText = DateTime.ToText(#"Load Date", "yyyy-MM-ddTHH:mm:ssZ"),
    
    QueryRecord = [
        #"$select" = SelectColumns,
        #"$filter" = "starttime ge " & FilterDateText
    ],
    
    Source = Json.Document(Web.Contents(
        BaseUrl, 
        [
            RelativePath = Endpoint,
            Query = QueryRecord
        ]
    )),
    
    Data = Source[value]
in
    Data

    The most important part, and what causes the Dynamic Source flag, is the splitting the host and relative path:

    BaseUrl = "https://chxxxxd.api.crm11.dynamics.com",
    Endpoint = "api/data/v9.1/bookableresourcebookings",

mike_asplin's avatar
mike_asplin
Helper V
4 months ago

Hi. That looks amazing but I'll be honest and say my code writing skills are nowhere near good enough to understand what any of it means and how I would apply it in my case!!!!

so

 

is tbl = 

"https://chxxxxd.api.crm11.dynamics.com/api/data/v9.1/bookableresourcebookings"

 or just 

"https://chxxxxd.api.crm11.dynamics.com/api/data/v9.1/

 with 

bookableresourcebookings

replacing "ProductionOrdersHeaders in? 

 

getDynamicsDataPathOnly(false,"ProductionOrderHeaders", columns)

  

What is "cross-company" in this bit referring to?

default = [#"cross-company"=Text.From(includeIC)]

 

much appreciated

vojtechsima's avatar
vojtechsima
Super User
4 months ago

Hey, mike_asplin ,

basically in your case, you can jsut do this:

let
    BaseUrl = "https://chxxxxd.api.crm11.dynamics.com",
    Endpoint = "api/data/v9.1/bookableresourcebookings",
    
    SelectColumns = "bookableresourcebookingid,_bookingstatus_value,_resource_value,name,_cha_clientid_value,_owningteam_value,duration,starttime,_cha_carercontactid_value,_msdyn_workorder_value,msdyn_totalcost,endtime,statuscode,msdyn_milestraveled,msdyn_actualarrivaltime",
    
    FilterDateText = DateTime.ToText(#"Load Date", "yyyy-MM-ddTHH:mm:ssZ"),
    
    QueryRecord = [
        #"$select" = SelectColumns,
        #"$filter" = "starttime ge " & FilterDateText
    ],
    
    Source = Json.Document(Web.Contents(
        BaseUrl, 
        [
            RelativePath = Endpoint,
            Query = QueryRecord
        ]
    )),
    
    Data = Source[value]
in
    Data

The most important part, and what causes the Dynamic Source flag, is the splitting the host and relative path:

BaseUrl = "https://chxxxxd.api.crm11.dynamics.com",
Endpoint = "api/data/v9.1/bookableresourcebookings",

  • mike_asplin's avatar
    mike_asplin
    Helper V
    4 months ago

    Brilliant let me try that. Thanks again. 

  • mike_asplin's avatar
    mike_asplin
    Helper V
    4 months ago

    I got this as an output of the last step? If i click on record i can see the data for 1 record, but how do I convert this into a table with multiple columns please?

     

    • vojtechsima's avatar
      vojtechsima
      Super User
      4 months ago

      mike_asplin  you can click your way through.

       

      Right click the 'LIST':

       

      and click To Table, then you can expand fields and click

      • mike_asplin's avatar
        mike_asplin
        Helper V
        4 months ago

        Sorry last question. i am triyng to get this thing so it will refresh in the service.  Apart formt his query every other query is either  a sharepoint file or a CDS source. I had some OData sources but changed them all the CDS

         

        Whne i try to refresh it online i am getting an OData error, but the OData has disappeared from the cloud conenctions and from the data source credentials.  This query isnt an OData query is it?  just cant work what is causing the issue. Thanks 

  • mike_asplin's avatar
    mike_asplin
    Helper V
    4 months ago

    Worked it out need

     

    = Table.FromRecords(Source[value])