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.
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
Table
I 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
Hello,
Thank you for the response. Sorry I cannot attach the pbix file but basically it is displaying the data on Power Query as a regular table but getting this error when I load. The steps i followed is enter the code in advanced editor and expand the data, records etc as shown in the last screen shots.Hi,BI11 ,thank you for your reply.
Do you have the same field format for the table data inside each record?You need to check if the data source is targeting the existence of that 'data' column and the data type is correct.
I hope the link below helps you:
URL:
How to Fix Power BI Column Not Found Errors
Solved: Error: "The field 'XXXX' of the record wasn't foun... - Microsoft Fabric Community
The problem you are having is that power query has come out with good data, but when you apply and close power query, there is a problem with the data loading?
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.