Record.FromTable

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Record.FromTable

Understanding the Record.FromTable Function

The Record.FromTable function is useful for creating a record from a table in Power Query. The function takes a table as an argument and returns a record that includes all the column names and their respective values. The function works by creating a new record for each row in the table and combining the column names and values into a single record.

Here’s an example of how the Record.FromTable function works:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WSkksKkwrVAgA”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“Column1”, Int64.Type}}),

#”Grouped Rows” = Table.Group(#”Changed Type”, {“Column1”}, {{“Count”, each Table.RowCount(_), type number}}),

#”Converted to Table” = Table.FromList(#”Grouped Rows”[Count], Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#”Renamed Columns” = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “Count”}}),

#”Added Custom” = Record.FromTable(#”Renamed Columns”)

in

#”Added Custom”

In this example, we start by creating a table from some sample data. We then group the rows in the table by the values in the "Column1" column and count the number of rows in each group. We then convert the resulting table into a list and create a new column called "Count". Finally, we use the Record.FromTable function to create a record from the table.

The resulting record will include all the column names and their respective values, which in this case will be the values from the "Count" column.

The M Code Behind Record.FromTable

To understand how the Record.FromTable function works, we need to take a closer look at the M code behind the function.

Here's the M code for the Record.FromTable function:

( table as table ) =>

let

cols = Table.ColumnNames(table),

vals = Table.ToRows(table),

res = Record.FromList(vals{0}, cols)

in

res

The function takes a table as an argument and returns a record. The code first extracts the column names from the table using the Table.ColumnNames function. It then uses the Table.ToRows function to convert the table into a list of rows, where each row is a list of values. The function then creates a new record by calling the Record.FromList function and passing in the first row of the list as the values and the column names as the keys.

Using Record.FromTable in Power Query

Now that we understand how the Record.FromTable function works, let's look at some examples of how to use it in Power Query.

Example 1: Creating a Record from a Table

Suppose we have a table that contains some data about cars, including the make, model, and year of each car. We can use the Record.FromTable function to create a record that includes all the column names and their respective values:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WclTSUTIwN0lVjCgB”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Make = _t, Model = _t, Year = _t]),

#”Added Custom” = Record.FromTable(Source)

in

#”Added Custom”

The resulting record will include all the column names and their respective values.

Example 2: Using Record.FromTable in a Custom Function

We can also use the Record.FromTable function in a custom function to create a record from a table. Here's an example:

let

GetRecordFromTable = (table) =>

let

cols = Table.ColumnNames(table),

vals = Table.ToRows(table),

res = Record.FromList(vals{0}, cols)

in

res,

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WclTSUTIwN0lVjCgB”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Make = _t, Model = _t, Year = _t]),

#”Added Custom” = GetRecordFromTable(Source)

in

#”Added Custom”

In this example, we define a custom function called "GetRecordFromTable" that takes a table as an argument and returns a record. We then call the function on a sample table containing data about cars and create a record from the table using the Record.FromTable function.

The Record.FromTable function is a useful tool for creating a record from a table in Power Query. By understanding the M code behind the function, we can create custom functions and use the function in a wide variety of situations. Whether you're working with large datasets or need to transform data from multiple sources, the Record.FromTable function is a powerful tool that can help you get the job done.

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)