Passing Odata.feed https string to a PowerQuery from Excel

Question

I am using PowerQuery in Excel and accessing information via Odata. The odata http link I use is specific to a project run in a software that then allows access to the data via Odata (Odata.Feed("https://odata3.xxxxxxxxx....)). Each link then has 10 plus tables that I parse in Powerquery and then pass to excel as a table.

Each scenario run of my project from the software creates a new Odata link. Updating my tables in excel then involves changing the https: link by going to the "source" of every query (>10).

Is there a way to point to an excel cell that has the http string as the source? This would allow me to paste my desired http string into a cell and after hitting refresh on my tables, they would all use that query string? i.e. I only change 1 link instead of in every query. Something like. =Odata.Feed(Sheet1!A1,null,[Implementation="2.0"]

Alternatively, is there something I'm missing that would make this workflow easier and reduce the number of places I have to update the data feed link?

Answer 1

Hi @Jonathan

I'm not really clear re. your end to end workflow but the following should address your requirement (=Odata.Feed(Sheet1!A1,null,[Implementation="2.0"])

Query paramater(s) in Excel

See Pass value as query parameter

And as you're going to have 2 data sources (Excel & Odata) you'll have to deal with 2 challenges: PQ Firewall & Data Privacy...

PQ Firewall

Assuming you only have 1 parameter to pass to your query and named a cell in Excel "OdataSourcePath"

Your query (let's call it myParameter) to get the corresponding value will look like:

let  
    Source = Table.FirstValue(  
        Excel.CurrentWorkbook(){[Name="OdataSourcePath"]}[Content]  
    )  
in  
    Source  

Then, if in another query you do something like:

Source = OData.Feed(myParameter, null, [Implementation="2.0"])  

Option1 - Setup query OdataSource with code:

let  
    OdataPath = Table.FirstValue(  
        Excel.CurrentWorkbook(){[Name="OdataSourcePath"]}[Content]  
    ),  
    Source = OData.Feed(OdataPath, null, [Implementation="2.0"])  
in  
    Source  

and in the other query:

let  
    Source = OdataSource,  
    NextStep = ...  
in  
    NextStep  

Option2 - Everyting in the same query:

let  
    OdataPath = Table.FirstValue(  
        Excel.CurrentWorkbook(){[Name="OdataSourcePath"]}[Content]  
    ),  
    Source = OData.Feed(OdataPath, null, [Implementation="2.0"]),  
    NextStep = ...  
in  
    NextStep  

Data Privacy

The privacy settings of the 2 data sources must be "compatible", i.e.:

More info

Firewall and Data Privacy in Chris Webb's video
Corresponding sample workbook avail. here