Filldown Query with conditions

[tomshaw83]

Hi all,

I am looking to define a test period in power query, as displayed in the column below. I currently have a column indicating when a test starts/stops - but I want to somehow be able to mark all the data inbetween so I can groupby test_period and user (essentially i want to recreate the test_period column somehow)

I have looked at filldown/fillup using mutliple columns with limited success so far.

Any ides would be appreciated, 

Thanks
Tom

User	Value	Start_Stop	Test_period
A	3
A	6	START	test
A	7		test
A	5		test
A	9	STOP	test
A	5
A	6
B	4
B	5	START	test
B	2		test
B	8		test
B	2		test
B	4	STOP	test
B	6

[AlexisOlson · Accepted solution]

I'd suggest adding a Custom helper column to duplicate Start_Stop which you'll use Fill Down on.

Then you can define your Test_period column as

if [Custom] = "START" or ([Custom] = "STOP" and [Start_Stop] = "STOP") then "test" else null

Here's what the resulting table looks like:

Here is the full M code you can paste into your Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIGYgWlWB0IzwyIg0Mcg0LgIuYo8qYoPEuwav8AHNJmcJ4TkGWCwjNFsQgkYoQib4HCM8IwCW6tE8KiWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Value = _t, Start_Stop = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User", type text}, {"Value", Int64.Type}, {"Start_Stop", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Length([Start_Stop]) > 1 then [Start_Stop] else null, type text),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Test_period", each if [Custom] = "START" or [Custom] = "STOP" and [Start_Stop] = "STOP" then "test" else null, type text)
in
    #"Added Custom1"

[tomshaw83]

Thanks again

[jennratten]

What should happen to the rows that have blank values in the start_stop and test period columns that are outside of start/stop periods?