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".
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
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.
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 _ JaneyThe 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.