public api
1 Topic
Can't refresh data from public API even it passes the Data Source Credentials.
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" RickSolved1.5KViews0likes3Comments