Forum Discussion

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

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)

Environment	ODBC	Database	Schema	Active
TEST	myDSN	myDB	myTEST	Y
LIVE	myDSN	myDB	myLIVE	Y

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

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"))

#"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

Need Help

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.

9 Replies

Anonymous
Not applicable
6 years ago
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
- music43
  Advocate II
  6 years ago
  Hi
  
  Thanks for the suggestion.
  
  I will follow that up.
SteveCampbell
Memorable Member
6 years ago
Just guessing here -but can you try to tick "Skip connection test"
- music43
  Advocate II
  6 years ago
  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.
Anonymous
Not applicable
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
  Not applicable
  6 years ago
  Hi T2,
  Did you get a response with a satisfying answer? I now run into the same issue
  - Anonymous
    Not applicable
    6 years ago
    Alas, SnoekC, I 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-Contents/m-p/1040271/highlight/true#M13950 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!
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

MatiasVizzari

Helper I

6 years ago

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"

Forum Discussion

Scheduled Refresh - Query contains unsupported function. Odbc.DataSource

9 Replies

Related content

Scheduled pipelines failing

Schedule refresh

SAP ODBC.DataSource SupportsDerivedTable

Schedule refresh

Azure Fabric Datawarehouse Unsupported DataType