AzureStorage.Blobs

In this article, we will take a closer look at the M code behind the Power Query M function AzureStorage.Blobs and explore how it can be used to connect to and manipulate data stored in Azure Blob Storage.

Understanding Azure Blob Storage

Before we dive into the M code behind the AzureStorage.Blobs function, let’s first understand what Azure Blob Storage is and how it works.

Azure Blob Storage is a cloud-based object storage solution that allows users to store and access large amounts of unstructured data, such as text or binary data. It offers three types of blobs: block blobs, append blobs, and page blobs, each designed for a specific use case.

Block blobs are optimized for streaming and storing large files, such as videos or images. Append blobs are ideal for scenarios where data needs to be appended to an existing file, such as log files. Page blobs are used for virtual machine disks and offer random read/write access to data.

Azure Blob Storage allows users to create containers to organize their data and offers various access tiers, including hot, cool, and archive, each designed for a specific use case. It also provides a range of security features, such as encryption at rest and in transit, role-based access control, and firewall and virtual network integration.

Using the AzureStorage.Blobs Function in Power Query

Now that we have a basic understanding of Azure Blob Storage, let’s explore how we can use the AzureStorage.Blobs function in Power Query to connect to and manipulate data stored in Azure Blob Storage.

The AzureStorage.Blobs function is part of the Power Query M language and allows users to connect to and retrieve data from Azure Blob Storage. To use the function, we need to provide the function with the following parameters:

– Account name: the name of the Azure Storage account

– Container name: the name of the container where the data is stored

– Folder path: the path to the folder where the data is stored

– Recursive: a boolean value that indicates whether to retrieve data from subfolders as well

Once we have provided the function with these parameters, we can use it to retrieve data from Azure Blob Storage in Power Query.

Examples of Using AzureStorage.Blobs

Let’s take a look at some examples of how we can use the AzureStorage.Blobs function in Power Query.

Example 1: Retrieving Data from a Single Blob

Suppose we have a single CSV file stored in a container called “data” in our Azure Storage account, and we want to retrieve it using Power Query. We can use the following M code:

In this example, we provided the AzureStorage.Blobs function with the account name “mystorageaccount”, the container name “data”, the file path “myfile.csv”, and set the parameter Recursive to false, indicating that we only want to retrieve data from the root folder. We then used the Csv.Document function to import the data as a CSV file.

Example 2: Retrieving Data from Multiple Blobs

Suppose we have multiple CSV files stored in different subfolders in our Azure Storage account, and we want to retrieve all of them using Power Query. We can use the following M code:

In this example, we provided the AzureStorage.Blobs function with the account name “mystorageaccount”, the container name “data”, an empty folder path, and set the parameter Recursive to true, indicating that we want to retrieve data from all subfolders as well. We then used the Table.SelectRows function to filter the retrieved data to only include CSV files, and the Table.AddColumn function to invoke a custom function that transforms each CSV file. Finally, we used various Table functions to remove unnecessary columns and expand the table column to display the transformed data.

In conclusion, the AzureStorage.Blobs function in Power Query allows users to connect to and retrieve data from Azure Blob Storage, a powerful cloud-based object storage solution that enables users to store and access large amounts of unstructured data. By providing the function with the necessary parameters, users can retrieve data from both the root folder and subfolders, filter the data to include only specific file types, and transform the data according to their needs.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us