Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]
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
Solved! Go to Solution.
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.
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"
(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
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.
Hi T2,
Did you get a response with a satisfying answer? I now run into the same issue
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... 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!
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.
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
Hi
Thanks for the suggestion.
I will follow that up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.