DataFlows - fails at combining data sources, unlike Excel and PowerBI

[TomTomTom]

A common workflow for in PowerQuery for us is:

* query a clinical database via csv folders and receive a list of organisational codes

* pass those codes to an open web API call to get live and correct details of the organisation

This requires privacy settings to be relaxed in PowerQuery, which is fine because what we are passing to the API is not sensitive in any way.

However, I can't find a way in DataFlows to configure the connection for privacy settings to allow this. I just get stuck in a loop of 'Please specify how to connect'.

Here is an example:

* the csv file passes us the code 14E

* PowerQuery appends it to the string https://directory.spineservices.nhs.uk/ORD/2-0-0/organisations/

* Json.Document(Web.Contents(https://directory.spineservices.nhs.uk/ORD/2-0-0/organisations/14E)) then gets us the data we need.

Works in Excel, works in PBI desktop (and when they are uploaded to the service). Can't get it to work in dataflows though ... am I missing something obvious?

[v-easonf-msft]

Hi, @TomTomTom 

Please refer to this document Troubleshooting dataflow issues: Connection to the data source 

The problem may be caused by incorrect connection details settings.

Try to clear the permissions on the data source then re-validating the credentials. 

You may also need to check the privacy level of the data source under the gateway.

Best Regards,
Community Support Team _ Eason