Rows between text to new column in power query

[eng_123]

Hi All i could like to create a new custom column in power query whenever dynamic rows for employee names between Section - Employee HR, IT and Sales

1. employee information between HR and IT then new Column row should be HR. 

2. employee information between IT and Sales then new Column row should be IT.

3 or else Sales

Create 1,2 and 3 in new column example

Section - Employee	New Column Needs to be created
HR
Mary	HR
Jill	HR
Sophie	HR
IT
Will	IT
Jimmy	IT
Roger	IT
Sales
John	Sales
Tom	Sales
Larry	Sales

Thank you

[slorin · Accepted solution]

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 

[ronrsnfld · Accepted solution]

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

[eng_123]

Thanks All. Much appriciate it for the solution.

[Omid_Motamedise · Accepted solution]

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"

[ronrsnfld · Accepted solution]

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

[slorin · Accepted solution]

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