AzureStorage.Tables

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function AzureStorage.Tables

What is Azure Storage Tables?

Azure Storage Tables is a NoSQL key-value storage service that allows users to store and query large amounts of structured data in the cloud. It provides a scalable and cost-effective solution for storing data, and it is widely used in various applications, including IoT, e-commerce, and finance.

With Azure Storage Tables, you can store data in a table format consisting of rows and columns. Each table can have up to billions of rows, and each row can have up to 252 properties. The data in each property can be of different data types, including string, binary, integer, Boolean, and datetime.

What is Power Query M?

Power Query M is a functional programming language that enables users to perform data transformations and manipulations in Power BI, Excel, and other applications. It uses a combination of functions, operators, and expressions to transform data into a desired format. One of the key benefits of Power Query M is that it is highly scalable and can handle large amounts of data.

The AzureStorage.Tables Function

Here is an example of how to use the AzureStorage.Tables function to retrieve data from an Azure Storage Table:

let

accountName = “myaccount”,

tableName = “mytable”,

source = AzureStorage.Tables(accountName, tableName)

in

source

Basic Querying

Once you have connected to an Azure Storage Table using the AzureStorage.Tables function, you can use various functions in Power Query M to manipulate and transform the data. For example, you can use the Table.SelectRows function to filter rows based on a given condition, or the Table.TransformColumns function to transform the values in a specific column.

Here is an example of how to use the Table.SelectRows function to filter rows in an Azure Storage Table:

let

accountName = "myaccount",

tableName = "mytable",

source = AzureStorage.Tables(accountName, tableName),

filteredRows = Table.SelectRows(source, each [Column1] > 100)

in

filteredRows

Advanced Querying

In addition to basic querying, you can also use advanced Power Query M functions to perform more complex data transformations and manipulations. For example, you can use the Table.Combine function to combine multiple tables into a single table, or the Table.Pivot function to pivot the values in a table based on a specific column.

Here is an example of how to use the Table.Combine function to combine multiple Azure Storage Tables:

let

accountName = “myaccount”,

tableNames = {“mytable1”, “mytable2”, “mytable3”},

tables = List.Transform(tableNames, each AzureStorage.Tables(accountName, _)),

combinedTable = Table.Combine(tables)

in

combinedTable

In conclusion, the AzureStorage.Tables function in Power Query M is a powerful tool that allows users to connect to and retrieve data from Azure Storage Tables. By using various Power Query M functions, you can manipulate and transform the data to extract valuable insights and improve decision-making. Whether you are working with large datasets or performing complex data transformations, Power Query M and Azure Storage Tables can help you achieve your goals in a cost-effective and scalable manner.

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

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)