Bit of help to identifying a dynamic data source

[mike_asplin]

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 and filtering in power Query.

I want to try and get this model refreshing on a schedule, but getting the dynamic source error. Is this the culprit? If it is am I stuck with a choice of having this type of filtering on import or having it refresh in service? Is it possible to have both or is the fact it leans on a parameter "Load Date" a problem.

let

       Url = "https://chxxxxd.api.crm11.dynamics.com/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"),
    
    FullUrl = Url & "?$select=" & SelectColumns & "&$filter=starttime ge " & FilterDateText,
    
    Source = OData.Feed(FullUrl, null, [
        Implementation="2.0",
        ODataVersion = 4,
        OmitValues = ODataOmitValues.Nulls
    ]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"starttime", type datetime}, {"endtime", type datetime}}),

Everything else is either an excel spreadsheet import or a full import of the dynamics table like this

let
    Source = Cds.Entities("https://chaltd.api.crm11.dynamics.com", [ReorderColumns=true, UseFormattedValue=true]),
    entities = Source{[Group="entities"]}[Data],
    accounts = entities{[EntitySetName="accounts"]}[Data],

Thanks any advice

[v-karpurapud]

Hi @mike_asplin 

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to @lbendlin and @vojtechsima   for those inputs on this thread.
Could you let us know if the suggested solution resolved your issue? Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Regards,

Microsoft Fabric Community Support Team.

[vojtechsima]

hey, @mike_asplin ,

The LoadDate isn't really the issue.

I would say the first OData query will fail similarly to WebContents when you don't use RelativePath. I assume you tried to use a query parameter for the $select,$filter, but got an error that queries can't use the dollar sign.

Expression.Error: OData.Feed custom query options cannot start with '$'.

Generally, anything after the host in the URL will probably trigger a Dynamic Source issue, like this part:

api/data/v9.1/bookableresourcebookings

I would suggest rewriting this into Web.Contents request with RelativePath, this way it's gonna fix the dynamic source, and you can native still use the Query Parameters.

Explicitly for dynamics, I use this kind of function, could be polished, but good enough:

let
  getDynamicsData = (includeIC as logical, tbl as text, fields as list, optional filters as nullable text) =>
  let
    default = [#"cross-company"=Text.From(includeIC)],
    selectExpr = Text.Combine(List.Transform(fields, Text.Trim), ","),
    selectQuery = if List.IsEmpty(fields) then [] else [ #"$select" = selectExpr ],

    filterQuery =
      if filters <> null and Text.Trim(filters) <> "" then
        [ #"$filter" = filters ]
      else
        [],

    queryRecord = Record.Combine({ default & selectQuery, filterQuery }),

    completePath = tbl & "?" & Uri.BuildQueryString(queryRecord)
  in
    completePath
in
  getDynamicsData

Then I call it like this to get the Path:

= getDynamicsDataPathOnly(false,"ProductionOrderHeaders", columns)

where columns is a list of columns like {"ProductionOrder", "dataAreaId"};

Additionally to pass filter query, you can also use it like this:

= getDynamicsDataPathOnly(false,"ProductionOrderHeaders", columns, " ProductionOrderNumber eq 'ABC'")

And then I call it like this:

= Json.Document( Web.Contents( host, [RelativePath=path] ) )

The authentication method is the native Organization Account window, and that part works flawlessly.

In case you have stuff that needs to be returned with multiple pages, I use a paginator like that:

= List.Generate(
        ()=> [
            request = Json.Document( Web.Contents( host, [RelativePath=path] ) ),
            next = request[#"@odata.nextLink"]?,
            index = 1
        ],
        each [next] <> null or [index]=1,
        each [
            request = Json.Document( Web.Contents( host, [RelativePath=Text.AfterDelimiter(next, host)] ) ),
            next = [request]?[#"@odata.nextLink"]?,
            index = [index]+1
        ],
        each [request]?[value]?
    )

btw host is a parameter "https://xx.xx.eu.dynamics.com/data/", yours would be slightly different

I have this method on many semantic models and have no issues, try it and lemme know.

[mike_asplin]

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 · Accepted solution]

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]

Worked it out need

= Table.FromRecords(Source[value])

[mike_asplin]

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]

@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]

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 

[vojtechsima]

Hey, @mike_asplin ,

it will be a 'Web' connection, there you can authenticate with Organization Account / OAuth2 in Service.

OData indeed should not appear there.

[mike_asplin]

Brilliant let me try that. Thanks again. 

[lbendlin]

Where does #"Load Date"  come from?

[mike_asplin]

Its a parameter

[lbendlin]

That sounds odd. In any case - try with a hard coded date first.

You may want to watch this video too... https://www.youtube.com/watch?v=RbqCA6srkmM