Table.FillDown Function of Power Query

The Table.FillDown returns a table from the table specified after replacing null values in the specified column or columns of the table with the previous cell non-null value in the column.

Syntax

Table.FillDown(table as table, columns as list) as table

Example: We have the following code is power query.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Marks = 568], 
      [CustomerID = 2, Name = null, Marks = 855], 
      [CustomerID = 3, Name = "Alia", Marks = 380], 
      [CustomerID = 4, Name = "Vicky", Marks = 458], 
      [CustomerID = 5, Name = null, Marks = null], 
      [CustomerID = 6, Name = "Meenakshi", Marks = 289], 
      [CustomerID = 7, Name = "Esha", Marks = null], 
      [CustomerID = 8, Name = "null", Marks = 380]
    }
  )
in
  MyTable

The output will be shown in the following image:

Return a table with the null values in column [Marks] filled with the value above them from the table.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Marks = 568], 
      [CustomerID = 2, Name = null, Marks = 855], 
      [CustomerID = 3, Name = "Alia", Marks = 380], 
      [CustomerID = 4, Name = "Vicky", Marks = 458], 
      [CustomerID = 5, Name = null, Marks = null], 
      [CustomerID = 6, Name = "Meenakshi", Marks = 289], 
      [CustomerID = 7, Name = "Esha", Marks = null], 
      [CustomerID = 8, Name = "null", Marks = 380]
    }
  ), 
   #"Return Output" = Table.FillDown(MyTable, {"Marks"})
in
  #"Return Output"

The output will be shown in the following image:

Return a table with the null values in columns [Marks] and [Name] filled with the value above them from the table.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Marks = 568], 
      [CustomerID = 2, Name = null, Marks = 855], 
      [CustomerID = 3, Name = "Alia", Marks = 380], 
      [CustomerID = 4, Name = "Vicky", Marks = 458], 
      [CustomerID = 5, Name = null, Marks = null], 
      [CustomerID = 6, Name = "Meenakshi", Marks = 289], 
      [CustomerID = 7, Name = "Esha", Marks = null], 
      [CustomerID = 8, Name = "null", Marks = 380]
    }
  ), 
   #"Return Output" = Table.FillDown(MyTable, {"Marks", "Name"})
in
  #"Return Output"

The output will be shown in the following image:

Avoid Hard Coding Columns To avoid hard code the column name in the Table.FillUp function, we can use Table.ColumnNames function.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Marks = 568], 
      [CustomerID = 2, Name = null, Marks = 855], 
      [CustomerID = 3, Name = "Alia", Marks = 380], 
      [CustomerID = 4, Name = "Vicky", Marks = 458], 
      [CustomerID = 5, Name = null, Marks = null], 
      [CustomerID = 6, Name = "Meenakshi", Marks = 289], 
      [CustomerID = 7, Name = "Esha", Marks = null], 
      [CustomerID = 8, Name = "null", Marks = 380]
    }
  ), 
  #"Return Output" = Table.FillDown(MyTable, Table.ColumnNames(MyTable))
in
  #"Return Output"

The output of the above code is shown below:

Previous Next