Query partitioned parquet files in Azure Data Lake Storage Gen2

[aubilla]

Hey 👋

I've got a Container in an ADLSGen2 with a folder that contains a partitioned uncompressed series of Parquet files. Now it has just the one but it will have many more in the future.

I'm trying to use this in PowerBI but for some reason I can't reference the entire folder. This means I need to select the part I want to use. I don't want this and I'd like to reference the whole folder from PowerBI Desktop so I can work with all the parts.

If I click on the Binary I get only that one part. I've tried with the ADLSGen2 and the Parquet connector without luck. How is this supposed to be done? Or is it not supported?

[Anonymous]

Hi, @aubilla 

You mentioned that you cannot connect to your Azure Data Lake Storage Gen2 folder for some reason. I found that there is a feature in the Azure Data Lake Storage Gen2 connector that provides the ability to connect to folders:

Did you get an error when using this option or is this feature not available in your environment? If it doesn't work in Power BI desktop, I think another way is to consider using data flow or data factory in Power BI Service. Use these tools to connect to your Azure Data Lake Storage Gen2. Then you can use the data extracted by these tools in Power BI desktop, which can also achieve your goal.

data flow:

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aubilla]

Thanks for the suggestion. I have the data in my Data Lake, as seen below.

Yet it's not showing in PowerBI Desktop using the folder view

The file system view works and it looks like this

But when I select Combine & Load I get an error

[Anonymous · Accepted solution]

Hi, @aubilla 

I found that not all of your folders are parquet files, which will cause your merge operation to fail.
To verify that parquet files support merging when connecting, I use the following example parquet file:

It can be found that when the folder is full of parquet files, it is possible to merge, if there are other types of problems, you will encounter problems when merging.

So, as a workaround, you can categorize your different types of files and put them in different folders. If you don't want categorization, then you'll need to create a corresponding query.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.