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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 😉
Pingback: Filling Values to the Right (or Left) in Power Query – Curated SQL
Hi,
really interesting and useful. Unfortunatly i’m not able to make it run.
Problably it’s my fault.
Best regards
Sergio Piva
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!
You are a life saver thank you for this solution 🙂