Eve Data via Power BI

[CelestialSwine]

So, I’ve been following this tutorial here https://wiki.zansha.space/index.php/SSO_Authentication_in_Microsoft_Power_Query

I’ve gotten everything so far, except for when I go into step for and try to use my authentication code on swagger I get a response from the server:

{
“error”: “Market access denied”
}

My application is setup for market data, I’m pretty sure, and I followed all the steps on this tutorial to get the values I needed to enter in order to get the access code, which I now have.

But when trying it out in swagger, I get the error… Not sure what’s wrong?

Steps to troubleshoot anyone?

[Blacksmoke16]

You can only access the market to structures you can dock in. This would indicate that you cannot dock there.

[CelestialSwine]

Is there a way to get back like, all structures I have access in? Or Is there a way so that I can do a lookup to say, yknow, check Jita 4-4 or whatever else.

Sorry Edit:

At least I think I may have answered part of my question: https://www.adam4eve.eu/info_stations.php

Edit x2: Turns out 60003760 returns the same error and according to this site that is Jita 4-4

[Blacksmoke16]

The structures endpoint you’re probably using is only for citadels. For normal stations you would have to fetch the entire region and filter to that specific location via https://esi.evetech.net/ui/#/Market/get_markets_region_id_orders.

[CelestialSwine]

So, I didnt try your endpoint yet, but I have been trying the tranquility trading tower and I got a different result pinging this structure,

It seems to work… Now to get that data in power BI

[CelestialSwine]

Any experts want to conference call on this?

Okay, this is as far as I got, trying to figure out how to turn this into a meaningful table:

[Blacksmoke16]

There is a difference between an npc station and a player owned structure. The structures endpoint requires you are able to dock there in order to get the market orders in that structure. The regions endpoint returns all orders in the region, excluding sell orders of structures within that region. However it does show ranged buy orders from structures. So its trivial to get a full list of a region’s market without pulling the full region plus knowing the ids of the structures to also fetch.

[CelestialSwine]

I think my major issues at this point are making power bi do what I want it to do, while the tutorial I am following does give the basic Idea, and Power BI’s mechanics are similar to excel, some things just aren’t quite matching up, or I’m bad at following directions. I’ve gotten my connection settled, authorized, was able to pull data from an endpoint, all those basics are covered.

Now I’m trying to add a parameter to my endpoint call so I don’t have to manually edit it every 20 minutes.

[Steve_Ronuken]

Need excel API guide may be of some interest

[Olmani_Kuha]

Do you try to paginate through pages if so,

i was trying to do same thing on market data and here is my m script hope it helps

but power query not a good fit for api request u should think to use python or any other language to fetch data to an db then you can connect to this database with ease

let
    GetJson = (Url) =>
        let 
            RawData = Web.Contents(Url),
            Json    = Json.Document(RawData)
        in  Json,
    
    region_id= "10000002",
    api_url = "https://esi.evetech.net/latest/markets/" & region_id & "/orders/?page=",

    Source = List.Generate(()=> [ i = 2, temp = GetJson(api_url & Number.ToText(1))] ,
             each List.Count([temp]) > 0,
             each [i = [i] + 1,
                   temp = GetJson(api_url & Number.ToText([i]))] ,
             each [temp]),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"duration", "is_buy_order", "issued", "location_id", "min_volume", "order_id", "price", "range", "system_id", "type_id", "volume_remain", "volume_total"}, {"duration", "is_buy_order", "issued", "location_id", "min_volume", "order_id", "price", "range", "system_id", "type_id", "volume_remain", "volume_total"})
   in
    #"Expanded Column2"

[Steve_Ronuken]

I do very little with excel and powerbi. I do pretty much everything in python these days.

(xlwings may be of interest too )

I just find these kinds of problems interesting. (biggest problem is the inability to get response headers.)