Forum Discussion
Data Load error from API
- Anonymous1 year ago
Hi,BI11 .Thank you for your reply.
In fact I followed up by creating a test case to completely simulate the problem you are experiencing.Forgive me for not being very good at power query, but I made this simple example by searching the web and following the tutorials, according to the screenshot you can see that my result here corresponds to yours
This is my test code: (complete and correct M code)let // API base URL baseUrl = "https://data.gov.uk/api", // POST request data, including query parameters postData = Json.FromValue([ x = 235.7, y = 41.53, q = "cows", rows = "20" ]), // Request headers headers = [#"Content-Type" = "application/json"], // Send POST request response = Web.Contents( baseUrl, [ RelativePath = "3/action/package_search", Headers = headers, Content = postData ] ), // Parse JSON response jsonResponse = Json.Document(response), // Extract data from the results list resultData = jsonResponse[result], resultsList = resultData[results], // Get all field names from the records allFieldNames = List.Distinct(List.Combine(List.Transform(resultsList, each Record.FieldNames(_)))), // Convert results list to table resultsTable = Table.FromList(resultsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), // Expand all fields in the records expandedTable = Table.ExpandRecordColumn(resultsTable, "Column1", allFieldNames) in expandedTableHere is the M code that I reproduced your question.
error M code// Parse JSON response jsonResponse = Json.Document(response), // Extract items list from the result record resultData = jsonResponse[result], itemsList = resultData[items] //Here's the issue. in itemsListI've also uploaded the pbix I used for testing, and links to articles that were helpful to me, so I hope that helps.
URL:
Web.Contents - PowerQuery M | Microsoft Learn
Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code
The site can be accessed directlyThe final result:(Data from the web)
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,BI11 .I am glad to help you.
“record data is not available” appears when loading data in Power Query , there are various reasons why such a problem occurs.
We hope the following suggestions will help you
1. Check that the format of the data you are trying to get through the API is the same as the format expected by Power BI.
2. Check the data privacy settings
3. Check the documentation of the API you are using to see if there are rate limits or specific query parameters that need to be added. Some APIs may have rate limitations that prevent some data from being fetched.
This also includes issues such as token authentication.
I recommend that you use the Web.Contents function.
The Web.Contents function allows you to specify details about the URL and HTTP request, including headers and query parameters, which can be useful for processing API requests
URL:
Web.Contents - PowerQuery M | Microsoft Learn
Chris Webb's BI Blog: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI
If the problem is still not solved after you try the suggestions above, please provide the necessary screenshots (without sensitive data) and the corresponding M code.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.