Forum Discussion

tomshaw83's avatar
tomshaw83
Icon for Helper I rankHelper I
4 years ago
Solved

Filldown Query with conditions

 

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

 

UserValueStart_StopTest_period
A3  
A6STARTtest
A7 test
A5 test
A9STOPtest
A5  
A6  
B4  
B5STARTtest
B2 test
B8 test
B2 test
B4STOPtest
B6  
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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"

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    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"
  • jennratten's avatar
    jennratten
    Icon for Super User rankSuper User
    4 years ago

    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?