Forum Discussion
Fill down first cell after Null/blank
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!
- one of my options is to write a conditional formula that: fill down starting from cell contains room till null cell
- 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
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
5 Replies
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 ResultHi 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.
Thx again! it works!
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
