Forum Discussion

music43's avatar
music43
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 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.

MatiasVizzari's avatar
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"