Forum Discussion
Dataflow Gateway brings wrong table
I found the reason.
It's not possible to do it. It's a limitation of dataflow. Period.
"Dataflows using gateway data sources do not support multiple credentials for the same data source"
https://docs.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-features-limitations
Anonymous, I know the document, but it doesn't work this way. Thank you any way.
The second link you sent actually explains why I can't use PostgreSQL without a gateway. All the workarounds involve exporting postgre data to other sources, like excel or MySQL, so we still having the fact that we need gateway to connect with postgre.
The first link with documentation seems good, altought my gateway management page seems to work slightly different from how I guess it should work.
In this documentation it says: "Make sure you map the correct data source definition to your data source. As the above screenshot illustrates, gateway admins can create multiple definitions on a single gateway connecting to the same data source, each with different credentials".
I simply can't do that. Let's say I have a single database with two different credentials, A and B, each one for a different table. If set up the dataflow and the gateway to go trought A, it works fine. Then I set up B, works fine either, but A breaks, because the A dataflow now want to use the gateway mapping trought B. It seems to change the data source settings automatically to the newer connection with the same "address", but then it can't fetch data due to credentials issue. If I select "maps to A", seems to change, but if I refresh the page, it map back to B again. I can't change that. I can add as much credentials as I want for the same database on my gateway, but only one credential may connect properly, others break (empty tables or credential issues).
If I try to fix A trought dataflow source step, I put the server and DB info, but I can't put user and password (it have the ones from the gateway), it goes all the way trough data, and end by feching B data for my A dataflow. (Yeah it gave me some trouble once I pushed this data into dashboards, the columns match perfectly).
If I'm not missing something really obvious, looks like a bug, but I'm using the free version, so I have no access to support.
Thank you again
You have precisely described my issue.
I'm experiencing this with on-prem SQL Server. I have two different SQL logins with separate access privileges to different tables.
I want to create two dataflows that use specific logins on the same server and database. I cannot select a login to use with a particular dataflow. When I enter credentials for one of them the other breaks and throws "The key didn't match any rows in the table". It makes sense because it has changed the previous setting and now it cannot see the table.
Creating different data source in gateway configuration didn't help either. It looks like you can select credentials in gateway config but it has no effect.
I'm thinking about using server alias or IP to differentiate the server definition in gateway configuration, which seems to be an overkill.
Please update, if you have found a solution.
Thanks,
I found the reason.
It's not possible to do it. It's a limitation of dataflow. Period.
"Dataflows using gateway data sources do not support multiple credentials for the same data source"
https://docs.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-features-limitations