Forum Discussion

MHadi's avatar
MHadi
Frequent Visitor
6 years ago

Refresh Power BI dataset connected to Scopus API database

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"

 

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      MHadi
      Frequent Visitor
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        HI MHadi ,

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

        Regards,

        Xiaoxin Sheng