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.
