Hi, I am having 11 parquet files in datalake Now I want to perform some transformations on these files using adf power query. I created a source dataset to the folder containing these parquet files and then used that dataset in adf power query. I was expecting that it will pull all the files but it seems it is only pulling a single file and only 91 rows from the file I want to pull all these files in power query and then i want to perform some transformations like merging all these files into single file, filtering the table and many more. Can anybody help me out in this. This is the first time i am trying to use power query in adf.

Hi @Amar Agnihotri , Thank you for posting query and sharing details. I reproduced your case, it seems Power Query editor shows only first file data in preview. But when you actually run the power query from pipeline then all the transformations are getting effected on all rows from all files and output files getting generated with all files. So, kindly go head and implement your transformations on preview data when you run actually, they effect on all rows from all files because your dataset pointing to folder of all files. Hope this helps. Please let me know if any further queries. ---------------- Please consider hitting Accept Answer button. Accepted answers help community as well.

Can we combine multiple parquet file ( each file having almost 4 M rows ) in adf power query ?

Accepted answer

ShaikMaheer-MSFT 38,326 Reputation points Microsoft Employee

2022-11-24T05:09:24.367+00:00

Hi @Amar Agnihotri ,

Thank you for posting query and sharing details.

I reproduced your case, it seems Power Query editor shows only first file data in preview. But when you actually run the power query from pipeline then all the transformations are getting effected on all rows from all files and output files getting generated with all files.

So, kindly go head and implement your transformations on preview data when you run actually, they effect on all rows from all files because your dataset pointing to folder of all files.

Hope this helps. Please let me know if any further queries.

----------------

Please consider hitting Accept Answer button. Accepted answers help community as well.
Please sign in to rate this answer.

1 person found this answer helpful.
Amar Agnihotri 921 Reputation points

2022-11-26T16:42:09.303+00:00

@ShaikMaheer-MSFT

Thanks for your help. I tried all the steps and here these steps are -

This is my power query --

In this UserQuery is the Base query for which source is the folder containing all the parquet files. I mean these files -

All other queries are the reference queries of UserQuery. Only columns are filtered to create different queries

Now this is my pipeline and power query activity settings

This is my sink dataset for power query activity. I have taken it has parquet because i want to save each query as a parquet file.

I was expecting the output of power query activity as --
Identity.parquet
Billing.parquet
Line Item.parquet
Reservation.parquet
Pricing.parquet
Product.parquet
Resource Tags.parquet
Saving Plans.parquet
Discount.parquet

I was expecting that these queries will get saved as different files in the sink folder. And the data in these files will be having all the data from the combine files of the source folder( As you told me earlier)

Amar Agnihotri 921 Reputation points

2022-11-26T16:47:42.153+00:00

Continued ....

But when i ran my pipeline i got these in sink folder in datalake -

I am not able to understand why my power query is creating all these files.
I am using same sink dataset for all the queries

and my default settings are -

Please tell me what is needed to get the desired output.

I have watched a lot of your you tube videos on adf and these are awesome but i didn't found any video on power query . Can you please suggest a detailed video on adf power query so that i could understand all the concepts of adf power query

Thanks

ShaikMaheer-MSFT 38,326 Reputation points Microsoft Employee

2022-11-28T04:35:31.497+00:00

Hi @Amar Agnihotri ,

All your part files will have data related to all your source files. If you wish to have a single file generated in output folder (Sink folder) then kindly consider using below settings shown in screenshot.

Please consider hitting Accept Answer button. Accepted answers help community as well.

Amar Agnihotri 921 Reputation points

2022-11-28T09:40:07.37+00:00

hi @ShaikMaheer-MSFT
I tried the suggested settings and it created some files but now it is throwing this error

ShaikMaheer-MSFT 38,326 Reputation points Microsoft Employee

2022-11-29T14:03:41.277+00:00

Hi @Amar Agnihotri ,

It seems your data is huge and resulting in out of memory error. Kindly consider process files in batches instead of all files at a time. Or you can give try by increasing crores as well. I think processing files in batches will be good idea.

-----------

Please consider hitting Accept Answer button. Accepted answers help community as well.

Amar Agnihotri 921 Reputation points

2022-11-29T14:19:51.08+00:00

Thanks @ShaikMaheer-MSFT
Can you please suggest some documentation about using batch processing in adf. I want to do this in power query activity?

Amar Agnihotri 921 Reputation points

2022-12-01T16:08:48.947+00:00

Hi @ShaikMaheer-MSFT
I found that dataset is very large due to which now i am trying to do this in mapping dataflows. I believe that dataflow will be much faster comparatively to adf Power query. Thanks for your support.
Sign in to comment