Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Editing parameters in Power BI service

Hello   I have a pbix file which uses parameters and passes the parameters through to a SQL query to pull off transactional data between 2 date periods.  The queries are in direct query mode and al...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous 

 

Thanks for your reply.  The parameters are set to text and are used to pass dates through an SQL query.  The set up is as per attached image.  The SQL query is nested within M query for the table:

 

let

StartDate = StartDate,
EndDate = EndDate,

Source = Sql.Database("xxxxx\data", "transactional",
[Query = "SELECT * from AVGdayrate A

where (A.Date > '" &StartDate& "' AND a.date < '" &EndDate& "')",


CommandTimeout=#duration(0, 0, 10, 0), HierarchicalNavigation=true, MultiSubnetFailover=true])
in
Source

 

The query is in direct query mode as the datasource is very large and cannot be used in import mode

 

I have also enabled load on the Parameters within Power Query - is this what you mean by invoking parameters?

 

Thanks again for your help

 

Jo.

 

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous ,

I found you are using same steps name to receive parameters, it may cause the conflict when power bi use parameters.
I'd like to suggest you use other name to define receive parameters or direct use it in t-sql query.

 

let
Source = Sql.Database("xxxxx\data", "transactional",
[Query = "SELECT * from AVGdayrate A

where (A.Date > '" &StartDate& "' AND a.date < '" &EndDate& "')",

CommandTimeout=#duration(0, 0, 10, 0), HierarchicalNavigation=true, MultiSubnetFailover=true])
in
Source

Regards,

 

Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous  I've tried your suggestion but I'm still getting the same error message.  Do you have any further suggestions?

     

    Thanks

     

    Jo.