A New column based on a rage of values in rows of a column

[NickDSL]

Hello,

I have a column of data I'd like to split into 3 categories based on values on another column.

To hopefully explain a bit better I have Column1, Row 1 is "Holiday Hours per FTE" and Row 46 is "Spread" Id like a new column to have all values between Rows 1-46 to say "Hours" based on the content of Column 1 as described. I cant use an index column for this because row counts are not consistent amongst files but content of Column 1 is consistent thus wanting to add this "conditional column" based on these values.

Further if that is understood I want the range after that being the row after where "Spread" occurs to run to where the row before "TOTAL FTEs" occurs. So for this example I would want rows 47-83 (row 84 contains "TOTAL FTEs" so I'd want the row before and row 47 is the row after where "Hours" occurs in Column1). I would want this range to output "Wages" in the new column.

All rows after this previous rule should say "FTEs" in the new column. 

If anyone can help with this and how to integrate it or give any insight if its even possible I would greatly appreciate it.

[jennratten · Accepted solution]

Hello, are you looking to achieve something like this?  If this is not what you are looking for, please provide an example of the inteded result. 

If so, you can do it with this script.  It will work regardless of the number of rows or varying rows.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sjPyUxJrFTwyC8tKlYoSC1ScAtxVYrViVbCRgQXFKUmpuCURhAh/iGOPiCjisFcBWxkLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Index = Table.AddIndexColumn ( Source, "Index", 1, 1, Int64.Type ),
    rHolidayHours = List.PositionOf ( Index[Column1], "Holiday Hours per FTE" ) + 1, 
    rHoursStart = rHolidayHours + 1,
    rSpread = List.PositionOf ( Index[Column1], "Spread" ) + 1,
    rWagesStart = rSpread + 1,
    rTotal = List.PositionOf ( Index[Column1], "TOTAL FTEs" ) + 1,
    rFTEStart = rTotal + 1,
    NewColumn = Table.AddColumn ( Index, "NewColumn", each 
        if [Index] <= rHolidayHours then "" else 
        if [Index] < rSpread then "Hours" else 
        if [Index] < rWagesStart then "" else
        if [Index] < rTotal then "Wages" else
        if [Index] < rFTEStart then "" else
        "FTEs", type text
          ),
    #"Removed Columns" = Table.RemoveColumns(NewColumn,{"Index"})

in
    #"Removed Columns"

[NickDSL]

Perfect thank you!

[jennratten]

You're welcome!