Azure Data Factory creates .CSV that's incompatible with Power Query

Catríona 0
Viewed 43

I have a pipeline that creates a dataset from a stored procedure on Azure SQL Server.

I want to then manipulate it in a power query step within the factory, but it fails to load in the power query editor with this error.

enter image description here

It opens up the JSON file (to correct it, I assume) but I can't see anything wrong with it.

If I download the extract from blob and upload it again as a .csv then it works fine.

The only difference I can find is that if I upload a blob direct to storage then the file information for the blob looks like this:

enter image description here

If I just let ADF create the .csv in blob storage the file info looks like this:

enter image description here

So my assumption is that somewhere in the process in ADF that creates the .csv file it's getting something wrong, and the Power Query module can't recognise it as a valid file.

All the other parts of the pipeline (Data Flows, other datasets) recognise it fine, and the 'preview data' brings it up correctly. It's just PQ that won't read it.

Any thooughts? TIA

azure-data-factory-2
1 Answers

I reproduced the same. When data is copied from SQL database to BLOB as csv file, Power query is unable to read. Also, Power query doesn't support json file. But when I tried to download the csv file and reupload, it worked. Below are the steps to overcome this issue.

  • When I tried to upload the file in Blob and create the dataset for that file in power query, Schema is imported from connection/Store. It forces us to import schema either from connection/store or from sample file. There is no option as none here.schema imported in power dataset
  • When data is copied from sql db to Azure blob, and dataset which uses the blob storage didn't have schema imported by default. schema not imported
  • Once imported the schema, power query activity ran successfully.

Output before importing schema in dataset

image before imporing dataset

After importing schema in dataset image after imporing schema

Aswin0
Related