Table.FromList

Understanding the Table.FromList function

The Table.FromList function is a handy tool for creating tables in Power Query. Essentially, it takes a list of values and returns a table where each value is its own row in the table. For example, suppose you have a list of names:

Using the Table.FromList function, you can create a table where each name is its own row:

This can be incredibly useful for a variety of data transformation tasks. But how does the Table.FromList function actually work? Let’s take a look at the M code behind the function to find out.

Breaking down the M code

When you use the Table.FromList function in Power Query, the M code that gets generated looks like this:

This may look like a lot of code at first, but it’s actually fairly straightforward. Let’s break down what each line of code does:

Line 1: Define the source list

This line of code defines the source list that the table will be created from. In the example we looked at earlier, the source list was `{“John”, “Mary”, “David”, “Sarah”}`.

Line 2: Convert the list to a table

This line of code is where the magic happens. The Table.FromList function is called, with the source list as the first argument. The other arguments specify how the list should be converted to a table. Let’s look at each argument in turn:

– `Splitter.SplitByNothing()`: This argument specifies that the list should not be split into multiple columns. In other words, each value in the list should be its own row in the table.

– `null`: This argument specifies that the column name for the table should be the default name, which is “Column1”.

– `null`: This argument specifies that the data type for the table should be the default data type, which is “any”.

– `ExtraValues.Error`: This argument specifies that if there are any extra values in the list that can’t be converted to a table row, an error should be returned.

Line 3: Output the table

This line of code simply outputs the table that was created in line 2.

Using the Table.FromList function

Now that you have a better understanding of how the Table.FromList function works, let’s look at some examples of how you can use it.

Example 1: Creating a table from a list of numbers

Suppose you have a list of numbers:

You can use the Table.FromList function to create a table where each number is its own row:

Example 2: Creating a table from a list of records

Suppose you have a list of records, where each record represents a person:

You can use the Table.FromList function to create a table where each record is its own row:

Example 3: Creating a table from a list of lists

Suppose you have a list of lists, where each inner list represents a row in the table:

You can use the Table.FromList function to create a table from this list:

The Table.FromList function is a powerful tool in Power Query that allows you to create tables from lists of values. By understanding the M code behind this function, you can gain a deeper understanding of how it works and how you can use it to your advantage. Whether you’re working with simple lists of numbers or more complex lists of records, the Table.FromList function can help you transform your data in powerful ways.

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

Upcoming Courses

Contact Us