Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.