Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Mahdi Hadi]

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 with APIKEY. Moreover access to Scopus database should be provided.

I appreciate any help for solving the credential issue. 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]

        ]
    ]

))),

    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"search-results"}, {"Custom.search-results"}),
    #"Expanded Custom.search-results" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom.search-results", {"opensearch:totalResults", "opensearch:startIndex", "opensearch:itemsPerPage", "opensearch:Query", "link", "entry"}, {"Custom.search-results.opensearch:totalResults", "Custom.search-results.opensearch:startIndex", "Custom.search-results.opensearch:itemsPerPage", "Custom.search-results.opensearch:Query", "Custom.search-results.link", "Custom.search-results.entry"}),
    #"Expanded Custom.search-results.opensearch:Query" = Table.ExpandRecordColumn(#"Expanded Custom.search-results", "Custom.search-results.opensearch:Query", {"@role", "@searchTerms", "@startPage"}, {"Custom.search-results.opensearch:Query.@role", "Custom.search-results.opensearch:Query.@searchTerms", "Custom.search-results.opensearch:Query.@startPage"}),
    #"Expanded Custom.search-results.link" = Table.ExpandListColumn(#"Expanded Custom.search-results.opensearch:Query", "Custom.search-results.link"),
    #"Expanded Custom.search-results.link1" = Table.ExpandRecordColumn(#"Expanded Custom.search-results.link", "Custom.search-results.link", {"@_fa", "@ref", "@href", "@type"}, {"Custom.search-results.link.@_fa", "Custom.search-results.link.@ref", "Custom.search-results.link.@href", "Custom.search-results.link.@type"}),
    #"Expanded Custom.search-results.entry" = Table.ExpandListColumn(#"Expanded Custom.search-results.link1", "Custom.search-results.entry"),
    #"Expanded Custom.search-results.entry1" = Table.ExpandRecordColumn(#"Expanded Custom.search-results.entry", "Custom.search-results.entry", {"@_fa", "link", "prism:url", "dc:identifier", "eid", "dc:title", "dc:creator", "prism:publicationName", "prism:issn", "prism:eIssn", "prism:volume", "prism:pageRange", "prism:coverDate", "prism:coverDisplayDate", "prism:doi", "pii", "dc:description", "citedby-count", "affiliation", "prism:aggregationType", "subtype", "subtypeDescription", "author-count", "author", "authkeywords", "article-number", "source-id", "fund-acr", "fund-no", "fund-sponsor", "openaccess", "openaccessFlag"}, {"Custom.search-results.entry.@_fa", "Custom.search-results.entry.link", "Custom.search-results.entry.prism:url", "Custom.search-results.entry.dc:identifier", "Custom.search-results.entry.eid", "Custom.search-results.entry.dc:title", "Custom.search-results.entry.dc:creator", "Custom.search-results.entry.prism:publicationName", "Custom.search-results.entry.prism:issn", "Custom.search-results.entry.prism:eIssn", "Custom.search-results.entry.prism:volume", "Custom.search-results.entry.prism:pageRange", "Custom.search-results.entry.prism:coverDate", "Custom.search-results.entry.prism:coverDisplayDate", "Custom.search-results.entry.prism:doi", "Custom.search-results.entry.pii", "Custom.search-results.entry.dc:description", "Custom.search-results.entry.citedby-count", "Custom.search-results.entry.affiliation", "Custom.search-results.entry.prism:aggregationType", "Custom.search-results.entry.subtype", "Custom.search-results.entry.subtypeDescription", "Custom.search-results.entry.author-count", "Custom.search-results.entry.author", "Custom.search-results.entry.authkeywords", "Custom.search-results.entry.article-number", "Custom.search-results.entry.source-id", "Custom.search-results.entry.fund-acr", "Custom.search-results.entry.fund-no", "Custom.search-results.entry.fund-sponsor", "Custom.search-results.entry.openaccess", "Custom.search-results.entry.openaccessFlag"}),
    #"Expanded Custom.search-results.entry.link" = Table.ExpandListColumn(#"Expanded Custom.search-results.entry1", "Custom.search-results.entry.link"),
    #"Expanded Custom.search-results.entry.link1" = Table.ExpandRecordColumn(#"Expanded Custom.search-results.entry.link", "Custom.search-results.entry.link", {"@_fa", "@ref", "@href"}, {"Custom.search-results.entry.link.@_fa", "Custom.search-results.entry.link.@ref", "Custom.search-results.entry.link.@href"}),
    #"Expanded Custom.search-results.entry.affiliation" = Table.ExpandListColumn(#"Expanded Custom.search-results.entry.link1", "Custom.search-results.entry.affiliation"),
    #"Expanded Custom.search-results.entry.affiliation1" = Table.ExpandRecordColumn(#"Expanded Custom.search-results.entry.affiliation", "Custom.search-results.entry.affiliation", {"@_fa", "affiliation-url", "afid", "affilname", "affiliation-city", "affiliation-country"}, {"Custom.search-results.entry.affiliation.@_fa", "Custom.search-results.entry.affiliation.affiliation-url", "Custom.search-results.entry.affiliation.afid", "Custom.search-results.entry.affiliation.affilname", "Custom.search-results.entry.affiliation.affiliation-city", "Custom.search-results.entry.affiliation.affiliation-country"}),
    #"Expanded Custom.search-results.entry.author-count" = Table.ExpandRecordColumn(#"Expanded Custom.search-results.entry.affiliation1", "Custom.search-results.entry.author-count", {"@limit", "@total", "$"}, {"Custom.search-results.entry.author-count.@limit", "Custom.search-results.entry.author-count.@total", "Custom.search-results.entry.author-count.$"}),
    #"Expanded Custom.search-results.entry.author" = Table.ExpandListColumn(#"Expanded Custom.search-results.entry.author-count", "Custom.search-results.entry.author"),
    #"Expanded Custom.search-results.entry.author1" = Table.ExpandRecordColumn(#"Expanded Custom.search-results.entry.author", "Custom.search-results.entry.author", {"@_fa", "@seq", "author-url", "authid", "authname", "surname", "given-name", "initials", "afid"}, {"Custom.search-results.entry.author.@_fa", "Custom.search-results.entry.author.@seq", "Custom.search-results.entry.author.author-url", "Custom.search-results.entry.author.authid", "Custom.search-results.entry.author.authname", "Custom.search-results.entry.author.surname", "Custom.search-results.entry.author.given-name", "Custom.search-results.entry.author.initials", "Custom.search-results.entry.author.afid"}),
    #"Expanded Custom.search-results.entry.author.afid" = Table.ExpandListColumn(#"Expanded Custom.search-results.entry.author1", "Custom.search-results.entry.author.afid"),
    #"Expanded Custom.search-results.entry.author.afid1" = Table.ExpandRecordColumn(#"Expanded Custom.search-results.entry.author.afid", "Custom.search-results.entry.author.afid", {"@_fa", "$"}, {"Custom.search-results.entry.author.afid.@_fa", "Custom.search-results.entry.author.afid.$"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.search-results.entry.author.afid1",{"Column1", "Custom.search-results.opensearch:startIndex", "Custom.search-results.opensearch:itemsPerPage", "Custom.search-results.opensearch:Query.@role", "Custom.search-results.opensearch:Query.@searchTerms", "Custom.search-results.opensearch:Query.@startPage", "Custom.search-results.link.@_fa", "Custom.search-results.link.@type", "Custom.search-results.entry.@_fa", "Custom.search-results.entry.link.@_fa", "Custom.search-results.entry.link.@ref", "Custom.search-results.entry.link.@href", "Custom.search-results.entry.prism:issn", "Custom.search-results.entry.prism:eIssn", "Custom.search-results.entry.prism:volume", "Custom.search-results.entry.prism:pageRange", "Custom.search-results.entry.dc:description", "Custom.search-results.entry.affiliation.@_fa", "Custom.search-results.entry.author-count.@limit", "Custom.search-results.entry.author.@_fa", "Custom.search-results.entry.author.afid.@_fa", "Custom.search-results.entry.article-number", "Custom.search-results.entry.source-id", "Custom.search-results.link.@href"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom.search-results.entry.citedby-count", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom.search-results.entry.prism:doi", "DOI"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "URL", each "https://doi.org/"&[DOI]),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom1", "Custom.search-results.entry.prism:coverDate", "Custom.search-results.entry.prism:coverDate - Copy"),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Custom.search-results.entry.prism:coverDate - Copy", "Date"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Date", type date}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type2",{{"Custom.search-results.entry.prism:coverDate", "Cover date"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Cover date", type date}})
in
    #"Changed Type3"?

——————————
Mahdi Hadi
Lecturer
——————————