Forum Discussion
Data Load error from API
I have a POS system Omega I use for my restaraunt. I am trying to connect to their API via Power BI. I send a post request through advanced editor and it fetches me the data storing 4 tables and records in each. When I load this I get an error "record data is not available" How do I fix this ?
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.
9 Replies
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.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
