Forum Discussion
Fill down first cell after Null/blank
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
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
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
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.