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

     

     

     

PowerBIFin's avatar
PowerBIFin
Advocate I
4 years ago

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.

 

 

PowerBIFin's avatar
PowerBIFin
Advocate I
4 years ago

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