How to change data source for Report from sql view to sql function with parameter

[SerenityNow]

Our reports are connected to sql views but we now need to filter everything and so I have sql functions that accept a parameter.   I've tried going into Advanced Editor to change the source of the tables but can't get it to work.

How can I change the tables to pull from sql functions instead of sql views without having to redo the entire dataset?

[Anonymous]

Hi @SerenityNow 

When you connect to sql connector,  you can use the advanced options that you can enter the sql query.

You can also refer to the follwing link about it.

Import data from a database using native database query - Power Query | Microsoft Learn

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SerenityNow]

Correct me if I am wrong, but that is only for new reports.  I want to be able to change existing reports.

[Anonymous]

Hi @SerenityNow 

In advanced editor, you can change the source code to the following,

Source = Sql.Database("xx"(Servername), "xxx"(datebasename), [Query="SELECT * FROM xxx(tablename)"])

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SerenityNow · Accepted solution]

My reports come from a view.  So currently, the advanced editor looks like this:

let
Source = Sql.Database("tcp:mydb.database.windows.net,1433", "mydb"),
dbo_rbv_UserPresence = Source{[Schema="dbo",Item="rbv_UserPresence"]}[Data]
in
dbo_rbv_UserPresence

If I change just the source line to what you suggest:

Source = Sql.Database("tcp:mydb.database.windows.net,1433", "mydb", [Query="SELECT * FROM pbi_UserPresence('new')"]),

i get this error:

Expression.Error: The key didn't match any rows in the table.
Details:
    Key=
        Schema=dbo
        Item=rbv_UserPresence
    Table=[Table]

I am not familiar with the syntax for Advanced Editor but it must be that Source is getting set twice?

If I import the sql function as a new table and look at the advanced editor I get this:

let
    Source = Sql.Database("tcp:mydb.database.windows.net", "mydb"),
    dbo_pbi_UserPresence = Source{[Schema="dbo",Item="pbi_UserPresence"]}[Data],
    #"Invoked Functiondbo_pbi_UserPresence1" = dbo_pbi_UserPresence("new")
in
    #"Invoked Functiondbo_pbi_UserPresence1"

So I have tried editing the existing reports to match that and it worked on a simple report.  On reports where I have other steps in the advanced editor (renaming columns for example) then I get the same error about the key not matching rows.

[Update]

I just got it to work by using.

let
    Source = Sql.Database("tcp:mydb.database.windows.net", "caymus"),
    dbo_pbi_Application = Source{[Schema="dbo",Item="pbi_Application"]}[Data],
    #"Invoked Functiondbo_pbi_Application1" = dbo_pbi_Application("new")
in
    #"Invoked Functiondbo_pbi_Application1"

Sometimes when I copy it in I get the error about the key.  I think extra spaces or something for some reason are causing an issue because if I delete any other spacing after pasting (even though the syntax checked) it then works.

Thanks for helping.