Passing Odata.feed https string to a PowerQuery from Excel

Jonathan 21 Reputation points
2021-02-23T17:23:56.31+00:00

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?

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
37,740 questions
0 comments No comments
{count} votes

Accepted answer
  1. Lz._ 8,991 Reputation points
    2021-02-24T10:26:00.493+00:00

    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"
    71480-excelnamedcell.png

    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"])  
    

    71562-firewallerror.png

    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.:
    71536-privacylevels.png

    More info

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

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful