Power BI Dataflow: Passing Parameter in SQL Statement

[PowerBIFin]

I have the following script which works in the Power BI Desktop but not in the Power BI Dataflow.( Error: We could not evaluate this query due to invalid or missing credentials. )

The referenced table (SharepointTable) in the Code is an existing Dataflow entity where the credentials have been setup properly and returning the proper value. If I replaced the Code below with hardcoded value (Code='Abc', 'Def'), it works.

I need the Code to be dynamic and an output from another table. Any ideas how I can resolve this error?

let
  Code = Table.FirstValue(Table.SelectColumns(#"SharepointTable", {"PCode"})),
  Source = Sql.Database("server", "db", [Query = "select * from [Fact].[Table] where PCode IN ("& Code & ")"])
in
  Source

Thank you!

[PowerBIFin · Accepted solution]

I finally made it work after going through the Global and Project Options (Dataflow > Options). 

I have to enable "Allow Combining  data from multiple sources." and "Always allow parameterization in data source and transformation dialogs".

 

[v-janeyg-msft]

Hello, @PowerBIFin 

If it can run on the desktop without errors, the code should be no problem. Please check if the credentials of all data sources have been added.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If problem still persists, pls let me know.

Best Regards,
Community Support Team _ Janey

[PowerBIFin]

Is there a way to find which credential it's looking for?

This line refers to another entity where it's returning the expected value so I assume the Credential for this Entity has been setup properly.

Code = Table.FirstValue(Table.SelectColumns(#"SharepointTable", {"PCode"})),

 For this line of code, I also have another entity with exactly the same query but the 'where clause' is hardcoded and returns the correct data. So I assume the credential for this database is also setup correctly in the dataflow. 

Source = Sql.Database("server", "db", [Query = "select * from [Fact].[Table] where PCode IN ('"& Code & "')"])

 Is there any other credential I should be checking?

Thanks for all your reply.

[v-janeyg-msft]

Hello @PowerBIFin 

Have you checked dataset/dataflow setting in service? 

If there is a problem with the credentials, there will be a prompt. And you need to update it.

Like this:

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[PowerBIFin]

The Dataflow Setting has no error at all, all access are setup including the DB Server.

This is the error I get when i run a parametized query where the parameter is result of another entity. I can successfully connect when I click connect from this window (refer to screenshot) however it keeps going back to the same credential error after the window disappear.

 

 

If I define the parameter as a hardcoded value (e.g. Code = "'ABC'") in the  parametized query (simplified sample: Select * from xxx where code = [Code]), it works that's why it baffles me why it's erroring on credential.

I don't know if it's relevant but the entity where I get the Parameter Value from is sourced from a PowerBI Dataflow table. I used that value to define the parameter in the Query sourced from SQL Database.

[v-janeyg-msft]

Hi, @PowerBIFin 

I am not good at sql, But 'code' should return a value without any symbols. You can try:

let
Code = Table.FirstValue(Table.SelectColumns(#"SharepointTable", {"PCode"})),
Source = Sql.Database("server", "db", [Query = "select * from [Fact].[Table] where PCode IN Code"])
//Source = Sql.Database("server", "db", [Query = "select * from [Fact].[Table] where PCode IN 'Code' "])
in
Source

If it still doesn't work. I need to do more investigation.

1. For the data source of the data obtained in sql, you need to modify the authentication method to public or none.

2.

Please go back to the desktop, check the data source settings, and see if there is a reminder that refresh is not supported. It may not support refreshing in the service.

3.If you don't know where the problem is, you can separate the data sources and test separately whether the refresh can be successful.

Ps:

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[PowerBIFin]

Just to re-emphasize what I mean above:

This code works:

let
Code = "'ABC'",
Source = Sql.Database("server", "db", [Query = "select * from [Fact].[Table] where PCode IN ('"& Code & "')"])
in
Source

This code doesn't work even though the Code returns the same value ('ABC'):

let
Code = Table.FirstValue(Table.SelectColumns(#"SharepointTable", {"PCode"})),
Source = Sql.Database("server", "db", [Query = "select * from [Fact].[Table] where PCode IN ('"& Code & "')"])
in
Source

[lbendlin]

I think you are missing some quotes.

let
  Code = Table.FirstValue(Table.SelectColumns(#"SharepointTable", {"PCode"})),
  Source = Sql.Database("server", "db", [Query = "select * from [Fact].[Table] where PCode IN ('"& Code & "')"])
in
  Source