Can't refresh data from public API even it passes the Data Source Credentials.

[Anonymous]

Hi,

I'm getting producer price index data from a public API.

It works fine on power bi desktop and it passes the Data Source Credentials test on the server(See picture below). 

However, when I tried to click the "Refresh now" on the server. I got the error below.

Underlying error code: -2147467259
Underlying error message: [Unable to combine data] Section1/PPI DATA1-25/Removed Other Columns references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.MashupSecurityException.DataSources: [{"kind":"Web","path":"https://api.bls.gov/publicAPI/v1/timeseries/data/"}]
Microsoft.Data.Mashup.MashupSecurityException.Reason: PrivacyError
Cluster URI: WABI-US-NORTH-CENTRAL-redirect.analysis.windows.net
Activity ID: 0d87b62e-a8b6-432a-aba6-4296ac92b817
Request ID: 2fa3c846-3e08-c54e-6443-9bd021e1d61d
Time: 2021-03-11 18:37:12Z

Here is my M query. Please help make it work. Thank you.

let
url = "https://api.bls.gov/publicAPI/v1/",
startyear = Number.ToText(Date.Year(DateTime.LocalNow())-1),
endyear = Number.ToText(Date.Year(DateTime.LocalNow())),
data = "{
""seriesid"": [""WPU071102"",""WPUID632""],
""startyear"": """ & startyear & """,
""endyear"" : """ & endyear & """
}",
Source = Json.Document(Web.Contents(url,
[Headers=[#"Content-type"="application/json"],
Content = Text.ToBinary(data),
RelativePath = "timeseries/data/"
])),
Results = Source[Results],
#"Converted to Table" = Record.ToTable(Results),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"seriesID", "data"}, {"Value.seriesID", "Value.data"})
in
#"Expanded Value1"

Rick

[aj1973 · Accepted solution]

Hi @Anonymous 

I tried your Query and published it to the service. The refresh is working just fine

How did you configure the source credentials?

[Anonymous]

Hi Amine,

Thank you for your reply. I thought the problem was the API, but it turned out to be that my M query was wrong.

Actually, I'm using a query to get a list of seriesid from an Excel file instead of using [""WPU071102"",""WPUID632""] in my M query. I was able to solve the problem by combining two queries and changed the privacy level to "public" for both data sources.

[aj1973]

You are welcome

kindly Accept this thread as the solution. More people will benefit from it