cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 published to the PBI service I get an error when trying to configure the scheduled refresh.

ErrorMsg.png

 

I have setup a parameter (called: pEnvironment value from list: Live, Test) to toggle between my live and development environments.

I have a table holding my environment data (example)

EnvironmentODBCDatabaseSchemaActive
TESTmyDSNmyDBmyTESTY
LIVEmyDSNmyDBmyLIVEY

…and filter it as follows:

= Table.SelectRows(Source, each ([Environment] = pEnvironment))

I then reference this table 3 times and finishing by drilling down to the value for ODBC, DB and Schema

let

    Source = Environment,

    #"Removed Other Columns" = Table.SelectColumns(Source,{"ODBC"}),

    ODBC = #"Removed Other Columns"{0}[ODBC]

in

    ODBC

I am then able to feed these values into the query as follows.

let

    Source = Odbc.DataSource("dsn=" & GetODBC, [HierarchicalNavigation=true, CommandTimeout =#duration(0,1,0,0), ConnectionTimeout=#duration(0,1,0,0)]),

    MY_Database = Source{[Name= GetDB,Kind="Database"]}[Data],

    Schema = MY_Database{[Name= GetSchema  ,Kind="Schema"]}[Data],

    #"Filtered Rows: Select only views" = Table.SelectRows(Schema, each ([Kind] = "View"))

in

    #"Filtered Rows: Select only views"

 

In this way I can change a single parameter and the refresh works, swapping between test and live data.

 

The issue is in the service.

 

I can get around the issue replacing the drilled table values with parameters but I then have multiple values to swap over. My solution feels like it should work as the desktop successfully reloads the data each time without error.

 

Many thanks

Oli

1 ACCEPTED SOLUTION

Accepted Solutions
Advocate I
Advocate I

Re: Scheduled Refresh - Query contains unsupported function. Odbc.DataSource

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.

View solution in original post

9 REPLIES 9
Highlighted
Community Support
Community Support

Re: Scheduled Refresh - Query contains unsupported function. Odbc.DataSource

HI @music43,

AFAIK, power query contains some of the preview functions. (I think they are collections of custom functions that package of specific operations steps)
Since these functions do not release as common connectors/functions so that you will be faced with 'not recognize' or 'not support' issues when you invoke them on power bi service side.
You can consider contact with power query team to increase the priority of release preview functions.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Scheduled Refresh - Query contains unsupported function. Odbc.DataSource

Hi

 

Thanks for the suggestion.

 

I will follow that up.

 

 

Highlighted
Super User I
Super User I

Re: Scheduled Refresh - Query contains unsupported function. Odbc.DataSource

Just guessing here -but can you try to tick "Skip connection test" 



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



Highlighted
Frequent Visitor

Re: Scheduled Refresh - Query contains unsupported function. Odbc.DataSource

Hi Steve

 

Is that setting in the service?

(Edit)

Found it. Ticking that hasn't helped. I also tried updating the GateWay to the latest version 3000.21.18 (Dec 2019) but still no joy.

 

Thanks for the suggestion though.

Advocate I
Advocate I

Re: Scheduled Refresh - Query contains unsupported function. Odbc.DataSource

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.

View solution in original post

Highlighted
Frequent Visitor

Re: Scheduled Refresh - Query contains unsupported function. Odbc.DataSource

Hi T2,

Did you get a response with a satisfying answer? I now run into the same issue

 

Highlighted
Advocate I
Advocate I

Re: Scheduled Refresh - Query contains unsupported function. Odbc.DataSource

Alas, SnoekCI did not, no. However, just a few minutes ago, this popped up: https://community.powerbi.com/t5/Report-Server/Query-contains-unsupported-function-Function-name-Web...  It may be just what you need.

Alas, this approach is not supported in the SharePoint functions I'm using because the root URL for each site is different, which is why I generate it dynamically in the Power BI Desktop Report.

Good luck!

Highlighted
New Member

Re: Scheduled Refresh - Query contains unsupported function. Odbc.DataSource

(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

Highlighted
Helper I
Helper I

Re: Scheduled Refresh - Query contains unsupported function. Odbc.DataSource

Good morning, I am having the same problem but when connecting to some DBF tables from different databases concatenated by a GetData, I understand that I would have to encode the DataSource in some way so that the power bi service recognizes it, but I do not understand how do it, any kind of help would be appreciated, I share the code of GetData and the one of the resulting table, thank you very much

(Nlocal)=>
let
    Origen = Excel.Workbook(File.Contents("C:\Users\administrador.ESTANCIAS\Documents\Power BI Desktop\Sucursales.xlsx"), null, true),
    Sheet1_Sheet = Origen{[Item="Sheet1",Kind="Sheet"]}[Data],
    LOCAL = Sheet1_Sheet{Nlocal}[Column9],
    

    Source = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\LINCEV3\"&LOCAL&"\DBF\;extended properties=dBASE IV",
    [Query="select CLCOD, CLTPO, CLNOM, CLDIR, CLLOC, CLCP, CLCUIT, CLVCODPAG, CLFECHA, CLFING, CLEMAIL from [cli.dbf]"])
in
    Source
let
    Source = Excel.Workbook (File.Contents ("C: \ Users \ administrator.STANCES \ Documents \ Power BI Desktop \ Branches.xlsx"), null, true),
    Sheet1_Sheet = Origin {[Item = "Sheet1", Kind = "Sheet"]} [Data],
    # "Promoted Headers1" = Table.PromoteHeaders (Sheet1_Sheet, [PromoteAllScalars = true]),
    # "Type changed" = Table.TransformColumnTypes (# "Promoted headers1", {{"LOCATION", type text}, {"GROUPING", type any}, {"LOCAL_NAME", type any}, {"LOCAL_NAME_TOTAL", type any}, {"COD_LOCAL", Int64.Type}, {"CONCEPT", type text}, {"SSS", type text}, {"TYPE", type text}, {"LOCAL", type text}, { "Active", Int64.Type}, {"Order", Int64.Type}}),
    # "Custom added" = Table.AddColumn (# "Type changed", "Custom", each GetDataCli ([COD_LOCAL])),
    # "Other columns removed" = Table.SelectColumns (# "Custom added", {"COD_LOCAL", "Custom"}),
    # "Custom expanded" = Table.ExpandTableColumn (# "Other columns removed", "Custom", {"CLCOD", "CLTPO", "CLNOM", "CLDIR", "CLLOC", "CLCP", "CLCUIT" , "CLVCODPAG", "CLFECHA", "CLFING", "CLEMAIL"}, {"CLCOD", "CLTPO", "CLNOM", "CLDIR", "CLLOC", "CLCUIT", "CLVCODPAG" , "CLFECHA", "CLFING", "CLEMAIL"}),
    # "Duplicates removed" = Table.Distinct (# "Custom expanded", {"CLCOD"})
in
    # "Duplicates removed"

 

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors