Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
music43
Advocate II
Advocate II

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
T2
Helper II
Helper II

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
MatiasVizzari
Helper I
Helper I

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"

 

Anonymous
Not applicable

(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

T2
Helper II
Helper II

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

Hi T2,

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

 

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!

SteveCampbell
Memorable Member
Memorable Member

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  



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.

v-shex-msft
Community Support
Community Support

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 as solution to help other members find it more quickly.

Hi

 

Thanks for the suggestion.

 

I will follow that up.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors