Bit of help to identifying a dynamic data source

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.

Where does #"Load Date"  come from?

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.