Trouble with Request Body in Copy Data data flow

[DevinL]

I'm connecting to a REST API through a POST request and trying to pass some parameters (filters) in the request body:

{
	"size": 100,
	"page": 1,
	"search": {
"date_limits": [
		    {
		        "field": "datetime",
		        "from": "2024-06-25 00:00:00",
		        "to": "2024-07-01 00:00:00"
		    }
		]
        	}
}

When I use this code in Postman (body -> raw), I'm able to pull back the correct filtered results.  When I put this same code into Microsoft Fabric in a Copy Data activity (Request body), it doesn't return the filtered results.

I do not get an error, I just get the wrong unfiltered results. Also, I have to specify the size and page in the relative URL whereas in postman I can just include it in the body.

Any help is greatly appreciated!

[Anonymous]

HI @DevinL,

What REST API are you test? Can you please share some more detailed informations about these?

In additon, I'd like to suggest you to use trace tools to trace the request and detail contents from 'postman' and 'copy data activity' operations. Then you can compare traced cotnetns to ensure the differences between these requests from two methods.

How to collect a network trace | Microsoft Learn

Regards,

Xiaoxin Sheng

[DevinL]

The REST API is for Sonar. I'm pulling back details for credits/debits/accounts/etc. and using the search feature in the sonar API to pull a specific date range. The purpose of doing it this way is to only bring in the new rows of data for each day rather than an entire replacement each day.

[Anonymous]

Hi @DevinL,

Have you try to put these processing in M queries with web connector and optional parameter and 'relative path'?

let
    //API endpoint
    url = "",
    // request headers
    headers = [#"Content-Type" = "application/json", #"Authorization" = "Bearer your_api_key"],
    //Relative path
    path = "credits/debits/accounts/xxxx",
    // query
    query = "{
    	'size': 100,
        'page': 1,
        'search': {
        'date_limits': [
                    {
                        'field': 'datetime',
                        'from': '2024-06-25 00:00:00',
                        'to': '2024-07-01 00:00:00'
                    }
                ]
        	}
    }",
    // Get the data from the API
    rawData = Web.Contents(url, [
        Headers = headers,
        RelativePath = path,
        Content = Text.ToBinary(query)
    ]),
    // Convert the JSON response into a table
    json = Json.Document(rawData),
    #"Converted to Table" = Record.ToTable(json)
in
    #"Converted to Table"

Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code  
Regards,

Xiaoxin Sheng

[DevinL]

Thanks for the reply.

Getting an error though: DataSource.Error: Web.Contents failed to get contents from 'https://apiaddressishere/search/credits' (422): Unprocessable Entity