refresh data

2 Topics

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" Rick
Solved
Anonymous
5 years ago Place Service
1.5KViews
0likes
3Comments
PowerBI Refresh Error
PowerBI App + Azure SQL Database(Serverless, Gen5, 2Vcores) with autopause after 1 hour. PowerBI fetches the data from Azure SQL Database using a SQL view with under 15M records everyday. This refresh takes about 10 minutes to refresh in PowerBI desktop. When I refresh it manually on PowerBi web, It takes under 5 min. But When I am trying to schedule the dataset refresh, it throws an error. ------------------------------------- Last refresh failed: Tue Jan 12 2021 13:44:23 GMT+0530 (India Standard Time) There was an error when processing the data in the dataset.Hide details Processing error: Microsoft SQL: Server provided routing information, but timeout already expired. Cluster URI: WABI-UK-SOUTH-B-PRIMARY-redirect.analysis.windows.net Activity ID: 55091bfd-ce22-4ac6-b38b-7c199de0d392 Request ID: a3480078-1a6f-4afd-adc1-3f71cb734436 Time: 2021-01-12 08:14:23Z ---------------------------------------------------------------------- Can this be because of the serverless setup of the Azure Sql db with an autopause feature after 1 hour. Kindly help!!!
Anonymous
5 years ago Place Service
1.6KViews
0likes
2Comments