Refresh Power BI dataset connected to Scopus API database

[MHadi]

I prepared a scientometric dashboard using Power BI which is connected directly to Scopus database by calling API keys. On my Power Bi desktop it can refresh data without any error but after publishing the dashboard to power bi website it can not be refreshed and returns a credential error:

Last refresh failed: Wed Nov 27 2019 12:32:39 GMT+0330 (Iran Standard Time) There was an error when processing the data in the dataset.Hide details Message: The credentials provided for the Web source are invalid. (Source at https://api.elsevier.com/content/search/scopus.) Table: API-Scopus-All. Cluster URI: WABI-EAST-ASIA-A-PRIMARY-redirect.analysis.windows.net Activity ID: 7edc8fb9-5513-465d-a35b-70cc5629d0d0 Request ID: 2edb255e-20fe-d1db-6b7d-2cf1b6681fc5 Time: 2019-11-27 09:02:39Z

following code is my query in Power BI. Moreover my credential on desktop is "Basic" with "User name"= my apikey

I only deleted my apikey from code. Anyone wants to reproduce results, should replace his/her Scopus apikey  (https://dev.elsevier.com/) with APIKEY. 

I appreciate any help for solving the credential issue. very thanks

let
    Source =  1000, //the total value from a total rows api?
    Starts = List.Generate(()=>0, each _ < Source, each _ + 25),
    #"Converted to Table" = Table.FromList(Starts, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document(Web.Contents(
    "https://api.elsevier.com/", 
    [
        RelativePath="content/search/scopus/",
        Query=
        [
         view="complete",
                 count="25",
                 query="AFFIL ( {Environmental Research Center}  OR  {Institute for Environmental Research} )  AND  AFFIL ( {Tehran University of Medical Sciences}  OR  {Tehran University of Medical Science} ) AND  AFFIL ( {Netherlands})",
                 apiKey="APIKEY",
                 limit="40",
                 start=""&[Column1]

        ]
    ]

))),
#"Added Custom"

[Anonymous]

Hi @MHadi ,

Have you checked the limitation list of that API? If your scenario does not meet the requirement, it will block the request you send from power query tables.

API key settings 

In addition, have you tried to add custom steps to check and get the last API key from rest API? (it will prevent API key expired issues)

Regards,
Xiaoxin Sheng

[MHadi]

Dear Xiaoxin Sheng

Thanks so much for your answer to my question, but your recommendation can not resolve my issue. I should clarify more the problem.

I have two following queries which both of them works well and refereshed without any error in desktop Power BI. The first query also can be refereshed well on Power BI web but the second query could not referesh on web and returns credential error. The first query returns only 25 records, but because I had to return all of my records (809 records) I changed it by making a relative path using recommendation in here. I think using a relative path in second query which is my problem case, may prevent credeintial data to be detected. But I have no solution for this. ( in the queries just APIKEY should be replaced with your APIkey, you can register for api key here ). Thanks again for any help

First query:

let
source=Json.Document(Web.Contents("https://api.elsevier.com/content/search/scopus?&view=complete&query=AFFIL ({Environmental Research Center} OR {Institute for Environmental Research} )  AND  AFFIL({Tehran University of Medical Sciences} OR {Tehran University of Medical Science} )&apiKey=APIKEY"))
in
source

 Second query:

let
source=Json.Document(Web.Contents(
	"https://api.elsevier.com/", 
	[
		RelativePath="content/search/scopus/",
		Query=
		[
		 view="complete",
                 query="AFFIL ( {Environmental Research Center}  OR  {Institute for Environmental Research} )  AND  AFFIL ( {Tehran University of Medical Sciences}  OR  {Tehran University of Medical Science} )",
                 apiKey="APIKEY"           
         	]
	]
))
in
source

[Anonymous]

HI @MHadi ,

Maybe you can contact @ImkeF to get some suggestions about power query web connector with rest API.

Regards,

Xiaoxin Sheng

[ImkeF]

Hi @MHadi  

the first query is not working for me. This is the errror I'm getting in the browser:

Could you please check the code? (I've created my own API key)

[MHadi]

Hi Dear  @ImkeF 

Thanks for your answer, I checked it. It works for me with no problem. I think  your case may be due two possible things:

- You may not pasted your APIkey in username box in credential Basic setting.

- Your system may not subscribed to Scopus. Our university has purchased Scopus subscription.

Thanks again.

[ImkeF]

Hi @MHadi 

please check out this article for alternative syntax: http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/ You might have to play around with it a bit.

[MHadi]

Hi @ImkeF 

Thanks for refereing me to the blog address,

but I myself previously followed the recommendation in that blog as you can see in my query 2 at above which I used a relative address for Scopus but finaly it returns a credential error on web refreshing trial. I also tried followig query using header but this one also could not resove the refereshing issue on web.

By the way I think I tried all ways but ineed I am bigneer in PBI and my mind is confused completely for this problem. Thanks anyway...

Query 3

let
    Source =  25, //the total value from a total rows api?
    Starts = List.Generate(()=>0, each _ < Source, each _ + 25),
    #"Converted to Table" = Table.FromList(Starts, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document(Web.Contents("https://api.elsevier.com/",
          
    [RelativePath="content/search/scopus", Headers = [#"X-ELS-APIKey"="APIKEY" ,#"Accept"="application/json"], 
    Query=[query="AFFIL ( {Environmental Research Center}  OR  {Institute for Environmental Research} )  AND  AFFIL ( {Tehran University of Medical Sciences}  OR  {Tehran University of Medical Science} )",
        view="complete",
        limit="40",
        start="&[Column1]",
        count="25"]])))
in
#"Added Custom"