Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
webbj
Helper I
Helper I

Multiple Web Query

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.

6 REPLIES 6
webbj
Helper I
Helper I

@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. 

webbj_2-1693318971883.png

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

webbj_1-1693318363412.png

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. 

 

 

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.

@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

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.

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. 

webbj_3-1693473197307.png

 

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

webbj_2-1693472915028.png

 

 

 

Martin_D
Super User
Super User

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

github.pnglinkedin.png

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.