Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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! Go to 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?
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
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?
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
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.
You are welcome
kindly Accept this thread as the solution. More people will benefit from it
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.