Rows between text to new column in power query

Hi eng_123 

add a new column

if List.Contains({"HR", "IT", "Sales"}, [#"Section - Employee"]) then [#"Section - Employee"] else null

then fill down

Stéphane 

This shows one method:

Paste the code into the Advanced Editor and explore the Applied Steps to understand

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ghSitWJVvJNLKoEM7wyc3LAjOD8gozMVDDTMwRMhcOkvDJzcyGqg/LTU4sgyhNzUoshsvkZeWBGSH4umPZJLAIZHgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Section - Employee" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Section - Employee", type text}}),
    #"Add new column" = Table.AddColumn(#"Changed Type","Section", each if List.Contains({"HR","IT","Sales"},[#"Section - Employee"])
            then [#"Section - Employee"] else null, type nullable text),
    #"Fill Down" = Table.FillDown(#"Add new column",{"Section"}),
    #"Null Matching" = Table.ReplaceValue(
        #"Fill Down",
        each [#"Section - Employee"],
        null,
        (x,y,z)as nullable text=> if List.Contains({"HR","IT","Sales"},y) then z else x,
        {"Section"})
in
    #"Null Matching"

 Results

Easy to solve

Add a new column by the next formula, to reach the next image

if List.Contains({"HR","Sales","IT"},[#"Section - Employee"]) then [#"Section - Employee"] else null

then select the column Custom and from Transform tab pick Fill Down to reach 

the hole formula is

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ghSitWJVvJNLKoEM7wyc3LAjOD8gozMVDDTMwRMhcOkvDJzcyGqg/LTU4sgyhNzUoshsvkZeWBGSH4umPZJLAIZHgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Section - Employee" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if List.Contains({"HR","Sales","IT"},[#"Section - Employee"]) then [#"Section - Employee"] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"})
in
    #"Filled Down"