Multiple Web Query

[webbj]

hello all, I was wondering if anyone can tell me how I can use multiple web credentials for seperate queries/tables. I need to call multiple of these, the only difference is the reportid and the credentials used. 

https://api.testdomain.com/api/reportextracts/?reportId=$reportId&startDate=$startDate&endDate=$endD...

So once the first one is complete, password entered saved, data loaded successfully I then attempt to load the second. Upon the second attempt it'll error a generic 400 code. The resolution is to change the credentials saved which then breaks the previous query as the password is no longer correct.

I was asked to post this here as I had posted in the wrong section! (sorry!)

is there a way to add multiple credentials / differentiate between web links.

I hope that this makes sense, if anyone needs more information please let me know.

[webbj]

@Martin_D Thankyou for responding to my query! Apologies for not responding sooner I have been away on holiday with no signal. 

I've put in a request for option 1 but not sure that they will provide such an account. So whilst I wait I am trying to get option 2 to work. (didn't know about dataflows)

I've created a dataflow for one of the queries. the data appears in the online power query editor. 

however when i connect to it in powerbi desktop no data appears

I believe that this is caused by the 'Not folding' symbol next to expanded results but I'm not sure what I've done wrong. Any help would be much appreciated, if you need more information please say so. 

[Martin_D]

You can ignore the not folding symbol. That's not the problem.

You need to explicitly refresh the dataflow at least once to see data in Power BI Desktop. The data in the editor is only preview. As long as you see data in the last step of the query ("Removed columns"), save & close the editor and then refresh the dataflow, and when the refresh is done, you should also get the same data into Power BI Desktop.

[webbj]

@Martin_D thanks, that worked. As I add further workflows I can no longer refresh the older ones without changing the password is this what you meant by "The main disadvantage is the refresh orchestration" i.e.

setup dataflow

refresh

add to powerbi desktop

then to refresh to get more data going forward

set correct password

refresh

repeat per workflow that needs data refresh

[Martin_D]

What I meant is that for each query that you create in a dataflow, when entering the connection setting you choose "new connection". After you have created the first query, the editor will by default suggest to use the existing connection for your second query, but other than in Power BI Desktop, now you can choose to create a new connection with different login credentials. That's what you need for connecting to these URLs with different logins.

If you have access to the connections administration in your Power BI tenant then you can also create the separate connections with the different identities there upfront. The advantage is that you can give nice names to the connections in the connections adminsitration and set the scope of the connection explicitly to an URL including the query parameters.

[webbj]

Hi Martin, so i've been trying to get this to work but I'm not sure what I am doing wrong. If you have the time could you write a dummies guide for me?

Yesterday I was creating a workflow per query. But after reading your message I tried to add more queries/tables to a single workflow selecting create new connection.

The queries are as following, each requiring a different username/password to access

1 = https://api.testdomain.com/api/reportextracts/?reportId=922&startDate=2023.01.01&endDate=2023.08.01

2 = https://api.testdomain.com/api/reportextracts/?reportId=966&startDate=2023.01.01&endDate=2023.08.01

But when a new table is added the old table stops working. 

and when I look at Manage connections I can only see one connection 

[Martin_D]

Hi @webbj ,

Querying the same source with different identites from the same dataset is indeed a challenge in Power BI, and different filter parameters are not accepted by Power BI to distinguish sources. Here are three options I can think about that are practical:

1. Set up one identity on the source that is allowed to get all the data you need and use filters instead of indentities and priviliges to distinguish subsets of data if needed. This is the solution I'd recommend.
2. Create Power BI Dataflows per identity and consume the dataflows in your dataset resp. in Power BI Desktop. The main disadvantage is the refresh orchestration if you want to do frequent refreshes, but it's doable.
3. Use ODBC driver(s) and create ODBC sources per identity. E.g. CData has ODBC drivers for REST APIs or OData APIs. The ODBC drivers even support Direct Query mode. Disadvantage is that you need to set up a machine or VM running the ODBC drivers and a Power BI Gateway Standard Mode. This machine could also be a Azure VM.

BR

Martin