Fill values to the right in Power Query and Power BI

While Power Query provides a convenient method to fill values from selected columns up- and downwards, there is no native option to do this to the right or left. So this article provides a custom function to do so.

Table.FillRight in Power Query


let func =
(SourceTable as table, FillColumns, optional FillLeft) =>
let
Mode = if FillLeft = null then Table.FillDown else Table.FillUp,
#"Added Custom" = Table.AddColumn(
SourceTable,
"Custom",
each Table.FromRows(
{
Function.Invoke(
Mode,
{
Table.FromColumns(
{Record.FieldValues(Record.SelectFields(_, FillColumns))}
),
{"Column1"}
}
)[Column1]
},
FillColumns
)
),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom", FillColumns),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", FillColumns),
#"Reordered Columns" = Table.ReorderColumns(
#"Expanded Custom",
Table.ColumnNames(SourceTable)
)
in
#"Reordered Columns" ,
documentation = [
Documentation.Name = " Table.FillRight.pq ",
Documentation.Description = " Returns a table from the <code>SourceTable</code> specified where the value of a previous cell is propagated to the null-valued cells right from the <code>FillColumns</code> specified. ",
Documentation.LongDescription = " Returns a table from the <code>SourceTable</code> specified where the value of a previous cell is propagated to the null-valued cells right from the <code>FillColumns</code> specified. Optional <code>third parameter</code> fills to the left instead. ",
Documentation.Category = " Table ",
Documentation.Source = " www.TheBIcountant.com – hhttps://wp.me/p6lgsG-2t1 ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann ",
Documentation.Examples = {[Description = " ",
Code = " let
SourceTable = #table( type table [Column1 = Text.Type, H1 = Text.Type, H2 = Text.Type, H3 = Text.Type],
// Column1| H1| H2| H3|
{//—————|———|—————–|——————|
{ ""BalanceSheet"", ""Assets"", null, null },
{ ""BalanceSheet"", null, ""Current Assets"", null },
{ ""BalanceSheet"", null, null, ""Current Asset 1"" } } ) ,
FillColumns = {""H1"", ""H2"", ""H3""},
FunctionCall = fnSampleFunction(SourceTable, FillColumns)
in
FunctionCall ",
Result = " #table( type table [Column1 = Text.Type, H1 = Any.Type, H2 = Any.Type, H3 = Any.Type],
// Column1| H1| H2| H3|
{//—————|———|—————–|——————|
{ ""BalanceSheet"", ""Assets"", ""Assets"", ""Assets"" },
{ ""BalanceSheet"", null, ""Current Assets"", ""Current Assets"" },
{ ""BalanceSheet"", null, null, ""Current Asset 1"" } } )
"]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

Usage to fill values to the right (or left) in Power Query

The first function argument takes the table you want to apply the function on. The second argument is the list of column names that shall be filled up into empty values to the right. In the example in the function documentation, this is:  {“H1”, “H2”, “H3”}. The curly brackets define a list object in Power Query and its list elements must be put in quotes if they shall represent strings. So here the columns H1, H2 and H3 are included.
An optional 3rd argument can be used to fill to the left instead. You can fill in any value there, so once it is used, the fill will work to the left instead.

How does it work under the hood?

First the function will add a custom column to the existing table where it selects the values of the selected fill-columns from the current record/row (see row 14 in the function above):
Record.SelectFields(_, FillColumns)

Then it will fetch the values from these selected column with the Record.FieldValues-function that has been wrapped around the selection (still row 14):
{Record.FieldValues(Record.SelectFields(_, FillColumns))}

This will return a list of values. Now the target is to bring this into a shape where I can use the existing function Table.FillDown or Table.FillUp to fill in empty values. Therefore I transform this list into a table. This happens in row 13 with the Table.FromColumns-function.

This returns a table with one column named “Column1”. On it, we can now do the fill-operations. The direction on which to fill is determined by the 3rd function argument. In there you can change the default right-fill to fill down instead. The logic for this is handled in row 4. There the variable “Mode” will return the selected function. Then in row 10 the function Function.Invoke can conditionally execute the Mode-function. Rows 11 – 17 provide the arguments for it. As a result, I get a one-column table with all values filled in.

Now I only have to tip this by 90° to bring the rows into columns and restore the original column names. Therefore I use the Table.FromRows function in row 8. It allows me to determine the column names in one go (row 20).

Then I remove the original columns (row 23), expand my newly created columns (row 24) and reorder the columns to its original shape (row 25).

Enjoy and stay queryious 😉

Comments (4) Write a comment

  1. Pingback: Filling Values to the Right (or Left) in Power Query – Curated SQL

  2. Hi,
    really interesting and useful. Unfortunatly i’m not able to make it run.
    Problably it’s my fault.
    Best regards
    Sergio Piva

    Reply

  3. I just wanted to thank you, this has helped me build a solution that has been nothing more than a pipe dream for the people in my department for years!

    Reply

Leave a Reply