Forum Discussion
Power BI Dataflow: Passing Parameter in SQL Statement
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
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.
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
SourceThis 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
SourceHi, 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 SourceIf 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