Fill down on conditions without loosing data from table

[naveen_chander]

I have below table with number upto 8 in "Control" column

i want to filter for 7 and 8 here and then want to use fill down option in column CT but when i do it i lost other rows from the table. Please suggest how data can be added with fill down option in column CT after filtering 7 and 8 and without loosing other rows in table

[m_dekorte · Accepted solution]

Hi @naveen_chander,

You can split your table, let's assume the variable containing your table is called: Source

Filter your table on rows to exclude from the fill operation, if that is a number it looks like this:

= Table.SelectRows( Source, each ([Control] < 7))

Copy the syntax visible inside the formula bar.

Press the fx in front of the formula bar, this creates a manual step.

Select the #"Selected Rows" step that was returned (I will refer to it like this but it could be named differently)

And paste the syntax you had copied, make a small modification:

= Table.SelectRows( Source, each ([Control] = 7 or [Control] = 9 ))

Perform fill operations and so on....

To bring back the rows where Control <7, insert a manual step: I will presume this returns a variable named: Result

Append the #"Selected Rows" table like so:

= Result & #"Selected Rows"

I hope this is helpful

[dufoq3 · Accepted solution]

Hi @naveen_chander, you can use simple condition:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICsizALGcgywzMAsmaw2VNwSwXuKwrkGUJZrnB9brD1YFkTcCsACDLCK4XoQ5hCsSOECDLWCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CT = _t, Control = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Control", Int64.Type}}),
    Ad_NewCT = Table.AddColumn(ChangedType, "New CT", each if List.Contains({7,8}, [Control]) then [CT] else null, type text),
    FilledDownNewCT = Table.FillDown(Ad_NewCT,{"New CT"})
in
    FilledDownNewCT

[m_dekorte · Accepted solution]

Hi @naveen_chander,

You can split your table, let's assume the variable containing your table is called: Source

Filter your table on rows to exclude from the fill operation, if that is a number it looks like this:

= Table.SelectRows( Source, each ([Control] < 7))

Copy the syntax visible inside the formula bar.

Press the fx in front of the formula bar, this creates a manual step.

Select the #"Selected Rows" step that was returned (I will refer to it like this but it could be named differently)

And paste the syntax you had copied, make a small modification:

= Table.SelectRows( Source, each ([Control] = 7 or [Control] = 9 ))

Perform fill operations and so on....

To bring back the rows where Control <7, insert a manual step: I will presume this returns a variable named: Result

Append the #"Selected Rows" table like so:

= Result & #"Selected Rows"

I hope this is helpful