Copying binary files from REST API in Fabric Data Factory pipeline

[rvp_ordix]

I am currently trying to build a Fabric Data Factory Pipeline that exports a Paginated Report as an Excel File via calling the requisite PowerBI APIs. For some reason, the Copy Activity doesn't seem to be able to handle copying that file to my lakehouse (into the Files folder) and throws an error due to the binary data. 

This is especially annoying since the Azure Data Factory can handle binary data from a REST endpoint, but I sadly need other Fabric components in later steps. 

Using Dataflows or Notebooks directly to call the endpoint where the file is returned is currently unfeasible for varying reasons. 

As a very awkward workaround, I have exported the report as a CSV, caught the plain text via Web Activity and passed it as parameter to a notebook where it is then converted into a DataFrame and then to the required Excel File. 

Is there perhaps a certain configuration that'd allow the Copy Activity to do this? Am I missing another, better way?

Edit:
For clarification, here is a more thorough description of what I attempted.

I used Web Activities to call the Export To File - REST API (with {"format": "XLSX"}, meaning a binary file) and then the Get Export To File Status - REST API to poll the process. Once the export succeeded, I intended to use a Copy Activity with a Web v2 source connection to the Get File Of Export To File - REST API and my Lakehouse in the same workspace as the sink. This failed however.

When configuring the sink connection to write a binary file, it throws the error that the source must be binary as well, for which the Web v2 connection apparently does not count even if it carries a binary file. Setting the sink to write i.e. a delimited text file doesn't work either, since it can't parse the binary data into a csv file. 

[ToddChitt]

Ah, OK. Thank you for the clarification. I understand your intended process, but unfortunately do not have any further input on this. Whish I could help, sorry.

[ToddChitt]

Wait, what? You are CREATING an Excel file by calling the Power BI API on a Paginated Report from inside a Copy Activity?

A Copy Activity should have no problem copying a file to a lakehouse. What is the Source? A WEB connection? Can you explain in further detail the layout and tasks?

[rvp_ordix]

I used Web Activities to call the Export To File - REST API (with "format": "XLSX", meaning a binary file) and then the Get Export To File Status - REST API. Once the export succeeded, I intended to use a Copy Activity with a Web v2 source connection to the Get File Of Export To File - REST API and my Lakehouse in the same workspace as the sink. This failed however.

When configuring the sink connection to write a binary file, it throws the error that the source must be binary as well, for which the Web v2 connection apparently does not count even if it carries a binary file. Setting the sink to write i.e. a delimited text file fails as well, since it can't parse the binary data into a csv file.