Table.FillUp

In this article, we will explore the M code behind the Table.FillUp function, how it works, and how it can be used in Power Query.

Understanding the Table.FillUp Function

The Table.FillUp function is a transformation function in Power Query that is used to fill null or blank values in a table with the value that appears in the previous non-null cell. It is similar to the Excel function “Fill Down” but can be used on any table in Power Query.

The syntax for the Table.FillUp function is as follows:

`table` – the table to be filled up

`columns` – the list of columns to be filled up. If not specified, all columns in the table will be filled up.

`orderCriteria` – the order in which the table is sorted. If not specified, the table will be sorted by the first column.

How the Table.FillUp Function Works

The Table.FillUp function works by filling null or blank values in a table with the value that appears in the previous non-null cell. It does this by iterating over each row in the table and filling up the null values in each column based on the value in the previous non-null cell.

For example, consider the following table:

| ID | Name | Age | Gender |

|—-:|:——–|—-:|:——-|

| 1 | John | 30 | Male |

| 2 | | 35 | Male |

| 3 | Jane | | Female |

| 4 | | | Male |

Applying the Table.FillUp function to this table would result in the following table:

| ID | Name | Age | Gender |

|—-:|:——–|—-:|:——-|

| 1 | John | 30 | Male |

| 2 | John | 35 | Male |

| 3 | Jane | 35 | Female |

| 4 | Jane | 35 | Male |

As you can see, the null values in the “Name” column are filled up with the value that appears in the previous non-null cell. The same applies to the null values in the “Age” column.

Using the Table.FillUp Function in Power Query

The Table.FillUp function can be used in Power Query to transform data in various ways. Some of the common use cases for the Table.FillUp function include:

1. Filling up null values in a column

One of the most common use cases for the Table.FillUp function is to fill up null values in a column with the value that appears in the previous non-null cell. This is useful when working with data that has missing values.

To fill up null values in a column using the Table.FillUp function, follow these steps:

1. Load the data into Power Query.

2. Select the column that you want to fill up.

3. Click on the “Transform” tab in the Power Query Editor.

4. Click on the “Fill” dropdown list and select “Down”.

5. The null values in the selected column will be filled up with the value that appears in the previous non-null cell.

2. Filling up null values in multiple columns

The Table.FillUp function can also be used to fill up null values in multiple columns at once. This is useful when working with large datasets that have multiple columns with missing values.

To fill up null values in multiple columns using the Table.FillUp function, follow these steps:

1. Load the data into Power Query.

2. Select the columns that you want to fill up.

3. Click on the “Transform” tab in the Power Query Editor.

4. Click on the “Fill Up” button.

5. The null values in the selected columns will be filled up with the value that appears in the previous non-null cell.

3. Filling up null values in a specific order

The Table.FillUp function can also be used to fill up null values in a specific order. This is useful when working with data that has a specific order or sequence.

To fill up null values in a specific order using the Table.FillUp function, follow these steps:

1. Load the data into Power Query.

2. Sort the data by the column that you want to fill up in a specific order.

3. Select the column that you want to fill up.

4. Click on the “Transform” tab in the Power Query Editor.

5. Click on the “Fill” dropdown list and select “Up” or “Down”.

6. The null values in the selected column will be filled up in the specific order that you specified.

The Table.FillUp function is a powerful transformation function in Power Query that is used to fill null or blank values in a table with the value that appears in the previous non-null cell. It can be used in a variety of ways to transform and cleanse data in Power Query. By understanding the M code behind the Table.FillUp function and how it works, you can take your data transformation and cleansing skills to the next level.

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

Upcoming Courses

Contact Us