Power Bi Desktop - API Help

[RoskBI]

Hi there!

I was hoping someone could help me with a little project I am working on.

I am trying to pull the information relating opportuntiies from this website into Power Bi Desktop https://www.buyict.gov.au/sp?id=opportunities 

I can see the data seems to pull from an API request but I cannot seem to get it to work in Power Bi Query Editor.  I have tried various request header combinations with no success but would really appreciate someone with more api knowledge to let me know where I'm going wrong.

Thanks.

[danextian]

Hi @RoskBI ,

If it is API, there should be some kind of API documentation available but I can't find that on the website.

[RoskBI]

Thank you @danextianm, maybe I am wrong and it's not an API (I assumed it was becuase the data seems to come from a "POST").

How would you pull the data from the website? I can't seem to get anything useful using the web connector or the create table from example feature.

Thanks

[danextian]

You might be able to do it using the web connector but depending on what data you're trying to pull and the number of pages, the query can be very slow and your connection might even get throttled. The query can get very complex and can involve digging into the underlying html and finding patterns. 

You can refer to this post I wrote but this just a tip of the iceberg - https://community.fabric.microsoft.com/t5/Community-Blog/How-to-Scrape-Multiple-Webpages-that-Don-t-... 

[RoskBI]

Hi @danextian thanks again for your help, but could you just confirm one more thing for me?

so the website does not have any formal API documentation. However the data I want is coming from an API Post (as I can see the data I want in the response)

With the below M Code I have managed to get it somwehat into Power BI but there is no "Page Items" option where the records are. Am I doing something wrong or is this just simply not possible?

let
url = "https://www.buyict.gov.au/api/now/sp/rectangle/bc93ec0a8738d950f973a8e50cbb3598?id=opportunities",
body = "{""Request Method"":""POST"",""Remote Address"":""149.96.93.55:443""}",
Source = Json.Document(Web.Contents(url,[Headers=[#"Accept" = "application/json",
        #"Accept-Language" = "en-AU,en;q=0.9",
        #"Connection" = "keep-alive",
        #"Cookie" = "BIGipServerpool_ictprocurement=f4497cde4a8af54023d638432445178f; glide_user_route=glide.852fe7ed65865a286e3471903d422622; glide_user_activity=U0N2M18xOkxZTmc0am9CV1NtaXFNbnJvOXBRRE1CaHdDSFdnSnhpQ1BlNGlmTjRqd2c9OmNRcEF4NzRPYkN6MGtxb0xhbXlSTlQ2bDRYYVk5RksrN2tkWG1BYTVMRzg9; JSESSIONID=D91061D0BA995A57225B7D74CFBCAD4B",
        #"Referer" = "https://www.buyict.gov.au/sp?id=opportunities",
        #"User-Agent" = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36",
        #"X-Requested-With" = "XMLHttpRequest",
        #"X-Transaction-Source" = "Interface=Web,Interface-Name=SP,Interface-Type=Service Portal,Interface-SysID=8a391964dba04810354e33f43a96199f",
        #"X-Use-Polaris" = "false",
        #"X-UserToken" = "8c871f5d87633110ca450e170cbb35fd255ca8ba09c6fbcc1cd80b0e354b4c42f752fa83",
        #"sec-ch-ua" = "Not_A Brand;v=8, Chromium;v=120, Google Chrome;v=120",
        #"sec-ch-ua-mobile" = "?0",
        #"sec-ch-ua-platform" = "Windows",
        #"x-portal" = "8a391964dba04810354e33f43a96199f"],Content = Text.ToBinary(body)])),
    result = Source[result],
    data = result[data]
in
    data