Table.FillUp Function of Power Query

The Table.FillUp function returns a table from the table specified where the value of the next cell is propagated to the null values cells above in the columns specified.

Syntax

Table.FillUp(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 below 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.FillUp(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 below 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.FillUp(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.FillUp(MyTable, Table.ColumnNames(MyTable))
in
  #"Return Output"

The output of the above code is shown below:

Previous Next