Skip to content

Instantly share code, notes, and snippets.

@ImkeF
Last active Mar 11, 2022
Embed
What would you like to do?
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.
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))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment