Refresh report that uses custom functions on Power BI Service?

user_1444097 0
Viewed 587

I am trying to figure out a workaround for refreshing dataset with Custom functions over Power BI service. My query looks like the following:

Data source for _op_kiekis

let
    Source = Loginai_File,
    #"Invoked Custom Function" = Table.AddColumn(Source, "Q_Sybase_2_Op_Kiekis", each Q_Sybase_2_Op_Kiekis([Source], [IP], [PORT], [DB_name]))

in
    #"Invoked Custom Function"

Data source for _eil_sk

let
    Source = Loginai_File,
    #"Invoked Custom Function" = Table.AddColumn(Source, "Q_Sybase_1_Eil_Sk_2", each Q_Sybase_1_Eil_Sk_2([Source], [IP], [PORT], [DB_name]))
in
    #"Invoked Custom Function"

Loginai_File

let
    Source = Excel.Workbook(File.Contents(PathToLoginai & Loginai_File_Name), null, true),
    dbs = Source{[Name="Sheet1"]}[Data],

in
    dbs

Q_Sybase_2_Op_Kiekis

(Name, strSource, Ip , Port, dBase ) =>

let

    Source = Sybase.Database(Ip & ":" & Number.ToText(Port), dBase, [Query="select [_].[Count] from [DBA].[dbs] [_] where [_].[DID_DAT] >= '" & sFilterDate & "' order by [DID_DAT]"])
in
    Source

Q_Sybase_1_Eil_Sk_2

(Name, strSource, Ip , Port, dBase ) =>

let

    Source = Sybase.Database(Ip & ":" & Number.ToText(Port), dBase, [Query="select [_].[Count] from [DBA].[dbs2] [_] where [_].[DID_DAT] >= '" & sFilterDate & "' order by [DID_DAT]"])
in
    Source

This works fine on Power BI desktop. However, I am getting following error on Power BI service: enter image description here

powerbi-datasource m powerbi-desktop powerbi powerquery
1 Answers

You need a SQL Server wrapper: PowerBI doesnt play nicely with Sybase but you have a work around that will fully support refreshes where Sybase is actually the back-end datastore:

  1. Setup SQL Server and add it to your Datagateway
  2. On your SQL Server add a linked Server Connection to your Sybase Servers from your new SQL Server. https://www.sqlservercentral.com/articles/connect-to-sybase-with-a-linked-server
  3. In PowerBI connect as a SQL Server data source and access the linked servers you setup on your SQL Server to get to the Sybase data.
user_23443940
Related