Forum Discussion

PowerBIFin's avatar
PowerBIFin
Advocate I
4 years ago
Solved

Power BI Dataflow: Passing Parameter in SQL Statement

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!

 
Dataflow
Need Help
  • PowerBIFin's avatar
    PowerBIFin
    4 years ago

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

     

     

     

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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
  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    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's avatar
      PowerBIFin
      Advocate I
      4 years ago

      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's avatar
        v-janeyg-msft
        Community Support
        4 years ago

        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's avatar
    PowerBIFin
    Advocate I
    4 years ago

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