Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
We would like to provide our users with a generic dashboard (pbix file) that can be used with their database (all our customer databases run on the same SQL Server but each customer of course has their own, different database login credentials)
We experimented with changing query settings in Power Query part of PBI Desktop. Apart from encountering a few issues there (user credentials weren't accepted although entered correctly) our users are not technical we would like to make this easier e,g, also avoiding that this has to be done for every table queried.
Is there a way to programatically access the power query script (accessible via advanced editor) to change query detail e.g. db name? Are there other ways to query user credentials and database detail on open and apply that to dashboard?
Would really appreciate your feedback on what's the best approach to making the dashboard file available so that users only have to enter their credentials and point to their database in the list (from the ones that their db user has access to) and the dashboard refreshes.
Thanks!
Martin
I have experimented some more and come again across the problem that my credentials are rejected although they are 100% correct. I have created a Power Bi query script with the variable for the database name (blank query with the string for the database as proposed by Chris Webb: http://blog.crossjoin.co.uk/2015/11/09/avoiding-duplication-of-database-connection-information-in-po... and the query for the table:
let Source = Sql.Database("servername",Databasename), dbo_VW_InvoicesAndCredits = Source{[Schema="dbo",Item="VW_InvoicesAndCredits"]}[Data] in dbo_VW_InvoicesAndCredits
("servername" on our end uses the real server name)
After that I am queried for user name and password which I enter correctly but it gets always rejected (on a new query it works perfectly). I am really getting to the end of my patience this seems to be an error in Power BI Desktop???
User | Count |
---|---|
84 | |
82 | |
66 | |
52 | |
48 |
User | Count |
---|---|
100 | |
49 | |
42 | |
39 | |
39 |