Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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".
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 _ 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.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.