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