Forum Discussion
Data Load error from API
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.
Also to add the data is loading on Power Query as tables just fine but when I load it I get the error.
This is the MCODE
let
url = "https://api.omegasoftware.ca/Integration/ExportSales",
body = "from_date=2025-01-01&to_date=2025-01-12&branch_custid=543769",
headers = [
#"Content-Type" = "application/x-www-form-urlencoded",
#"Key" = "****"
],
response = Web.Contents(url, [
Headers = headers,
Content = Text.ToBinary(body)
]),
jsonResponse = Json.Document(response)
in
jsonResponse
// the response i get is attached, when i click data i get 4 tables and when i click each it contains all the records each record being field value pairs.
Hi,BI11 .Thank you for your reply.
It looks like you currently need to convert the Record into a corresponding Table.
Like this.
let records = { [id = 1, name = "Alice", age = 30, email = "[email protected]"], [id = 2, name = "Bob", age = 25, email = "[email protected]"], [id = 3, name = "Charlie", age = 35, email = "[email protected]"], [id = 4, name = "David", age = 28, email = "[email protected]"], [id = 5, name = "Eve", age = 32, email = "[email protected]"] }, // add the step Table.FromRecords table = Table.FromRecords(records) in TableI hope the links below will help you.
URL:
List.Transform - PowerQuery M | Microsoft Learn
Record.ToTable - PowerQuery M | Microsoft Learn
Record functions - PowerQuery M | Microsoft Learn
List >Record >Table
let listsOfRecords = { { [id = 1, name = "Alice", age = 30, email = "[email protected]"], [id = 2, name = "Bob", age = 25, email = "[email protected]"] }, { [id = 3, name = "Charlie", age = 35, email = "[email protected]"], [id = 4, name = "David", age = 28, email = "[email protected]"] }, { [id = 5, name = "Eve", age = 32, email = "[email protected]"], [id = 6, name = "Frank", age = 29, email = "[email protected]"] } }, combinedRecords = List.Combine(listsOfRecords), table = Table.FromRecords(combinedRecords) in tableI 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.when I click show as table it displays as a table but when i load it toi create visuals I get an error data not available
Hi,BI11 .Thank you for your reply.
Can you provide a screenshot of the steps you took, the problem is that the data has been successfully displayed in the table model, when you try to drag the fields into visuals do you get an error, please give me a screenshot of the corresponding error, and if you can please provide the corresponding pbix file.
URL:How to Get Your Question Answered Quickly - Microsoft Fabric Community