Forum Discussion

music43's avatar
music43
Icon for Advocate II rankAdvocate II
6 years ago
Solved

Scheduled Refresh - Query contains unsupported function. Odbc.DataSource

I am fairly new to Power BI and I am trying to understand if I have come across a bug or if the issue is good old fashioned user error! My Pbix file is setup and working in PBI Desktop but when publi...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I'm working a case with Microsoft Support that deals with this very issue.

    Here is what you're likely running into: some functions are ONLY supported in the Power BI service if the first parameter (i.e., URL) is hardcoded. If you use a variable or an expression it is not supported.

     

    This is what I was told on 2/10/2020:

    I have an update from the product team and they identified the issue.

    let
        Source = (CitPmoProjectSiteUrl as text, ListType as text) => let
            Source = SharePoint.Tables(CitPmoProjectSiteUrl, [ApiVersion = 15]),
            Issues = Source{[Title=ListType]}[Items]
        in
            Issues,
    in
        Source

    The highlighted one is not  a valid connection string but it points to a variable which is a valid connection string. Now Sharepoint.Tables looks for a valid connection string instead of a variable  which is why  it says  unsupported function .

     

    I have found this restriction applies to, at least, SharePoint.Tables(), OData.Feed(), and, apparently, the call you're using as well.

     

    I will be speaking again with them about this on Monday, 3/2. If I receive helpful information I'll share it here.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

(subscription_id as text) as table=>
let
// Get an access token using the Client Credentials 
AccessToken = Json.Document(Web.Contents("TOKEN_URL",
[
Headers=[Accept="application/json", ContentType="application/x-www-form-urlencoded"],
Content=Text.ToBinary(
"grant_type=client_credentials&
client_id=XXX&
client_secret=XXX&
scope=XXX"
)
]))[access_token],
body = "{
""startDate"":""2010-01-01T00:00:00.000Z"",
""endDate"":""2030-12-01T00:00:00.000Z"",
""pageSize"":""100""
}",
BuildQueryString = Uri.BuildQueryString(Parsed_JSON),
// Call the target REST API, passing the access token as evidence of authorization
Data = Json.Document(Web.Contents("URL/{subscriptionId}?subscriptionId=XXX",
[
Headers=[Accept="application/json",
#"Authorization"="Bearer " & AccessToken, #"Ocp-Apim-Subscription-Key"= "XXX",
#"Content-Type"="application/x-www-form-urlencoded"],
Content = Text.ToBinary(BuildQueryString),
Query=[subscriptionId=subscription_id]
]
))
in
Data

 

N.B: Don't forget to set the privacy level to Organisational in you data source settings otherwise refresh will fail from Power BI Service