Data Load error from API

[BI11]

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 ? 

[BI11]

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. 

[Anonymous]

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 = "alice@example.com"],
        [id = 2, name = "Bob", age = 25, email = "bob@example.com"],
        [id = 3, name = "Charlie", age = 35, email = "charlie@example.com"],
        [id = 4, name = "David", age = 28, email = "david@example.com"],
        [id = 5, name = "Eve", age = 32, email = "eve@example.com"]
    },


    // 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 = "alice@example.com"],
            [id = 2, name = "Bob", age = 25, email = "bob@example.com"]
        },
        {
            [id = 3, name = "Charlie", age = 35, email = "charlie@example.com"],
            [id = 4, name = "David", age = 28, email = "david@example.com"]
        },
        {
            [id = 5, name = "Eve", age = 32, email = "eve@example.com"],
            [id = 6, name = "Frank", age = 29, email = "frank@example.com"]
        }
    },
    
    combinedRecords = List.Combine(listsOfRecords),
    
    table = Table.FromRecords(combinedRecords)
in
    table

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.

[BI11]

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

[Anonymous]

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

[BI11]

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.

[Anonymous]

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.

[BI11]

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

[Anonymous · Accepted solution]

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 An...

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.

[Anonymous]

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.