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.
Hello,
Yes Mr.Jian that is the error, not column not found it says the field 'data' is not found which i think its referreing to the attached first step of the JSON Response.
Please asssit in this.
Best Regards
B1
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
expandedTable
Here 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
itemsList
I'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 directly
The 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.