Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
NickDSL
Helper I
Helper I

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

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.

 

 

1 ACCEPTED SOLUTION
jennratten
Super User
Super User

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. 

 

jennratten_0-1693429307347.png

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"

 

View solution in original post

3 REPLIES 3
jennratten
Super User
Super User

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. 

 

jennratten_0-1693429307347.png

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"

 

Perfect thank you!

You're welcome!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors