Fill down first cell after Null/blank

[onefousanflee]

Hello! I'm trying to use Power Query to get data from our non-traditional data source. Here's one problem I'm facing:

They updated the data in a different way:

and I need to transform data in this way:

I plan to duplicate the column, then use fill down method, but that only fills down the last person till the Null cell like this:

do you think there is a way that maybe I can add a customize-column that showing their room number to each person? Any help will be appreciated!

1. one of my options is to write a conditional formula that: fill down starting from cell contains room till null cell
2. duplicate the column, then select only cell with names(but I dont know how to write this formula, because name list is huge), then replace to Null, then use fill function replace the rest

[bolfri · Accepted solution]

Step 1. Add Index column

Step 2. Add marker column to find Room names

Step 3. Add new column: Room

Step 4. Fill it down

Step 5. Leave only few columns with filter on marker = false and ColumnValue <> ""

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDNCoNADITfZc/bQ3+eQNsiFUUU9CAeggYtTTfFn8L69LVxaS9elpmE+TJsWaqU+bnbq0qXqoC+0TeROUxtB0aH4nwCO7LRsbgzG34vu0Kc13P9cKn/I9SDyISJvmBfXMz0w3pApFORAfMLex25sNXXLeBxRbSTReOCIeHd6IvoCIalZCY6A1N3ODtODoRmnpDArbfop7UU9u3SxFvniAO6SxkQDO5SAiOS+44AbLPUyVVVfQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColumnValue = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColumnValue", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Marker" = Table.AddColumn(#"Added Index", "Marker", each let
currentIndex = [Index], currentValue = [ColumnValue],
previousValue = List.First(Table.SelectRows(#"Added Index", each [Index] = currentIndex - 1)[ColumnValue]),
marker = if (previousValue = "" or previousValue = null) and currentValue <> previousValue then true else false
in marker),
    #"Added Room" = Table.AddColumn(#"Added Marker", "Room", each if [Marker] then [ColumnValue] else null),
    #"Filled Down Room" = Table.FillDown(#"Added Room",{"Room"}),
    Result = Table.SelectColumns(Table.SelectRows(#"Filled Down Room",each [Marker] = false and [ColumnValue] <> ""),{"Room","ColumnValue"})
in
    Result

[onefousanflee]

Thx again! it works!

[onefousanflee]

Hi @bolfri ! thx for your help! and I have a question regarding of your steps:

do you think it will be just easier to have a conditional column that if the cell's length is greater than XX(XX here means the "room-1"'s length), then it will be null, then I can use fill down function

 or

conditional column that if other column contains "room-" will remain the same, else will be replaced as nul ?

I am trying to apply your method still! THX AGAIN

[bolfri]

If you're SURE that Room name is always with same length or same regex then yes - you can just do it. My solutions works also for Room name like "Very beautiful room" or something like that. 😄 With Room name logic it would be easier to solve this, but I didn't know if names in you sample data are the names that you are using in your real data model.

[onefousanflee]

this is the data incase:

Room-1

Ward,J

Vaughan,K

Clayton,N

Donovan,W

Brock,J

Room-2

Pollard,C

Nolan,K

Ball,R

Hooper,M

Roy,F

Room-3

Nguyen,R

Klein,E

Mason,S

Sanchez,F

Valenzuela,S

Room-4

Berger,B

Reese,R

Salas,E

Patel,N

Hayden,V