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.