Filling in the Blanks

[LWBB]

Hi, 

I have a few excel tables that contains data that's been extracted from a source. Sometimes that excel data has BLANKS. Is it possible to fill in those blanks with data that has been 'looked up' from else where?

For example Can the first blank in Column Section Descr look up S1_ from Section column and then fill in the blank with the description? its the same for the next 2 columsn Location and Location Descr.

I don't mind doing a Lookup for the whole column rather than individual cells.

Thank you

[LWBB]

Thank you both. I did it slightly different to get the results I needed but I did use the Merge column. Thanks again. 

[AlienSx]

Hello, @LWBB if I understood your message correctly, you want each unique combination of Section and Location to have the same (non blank) descriptions. You can do that by grouping data by Section and Location while don't aggregate any other columns but use "All Rows" option. Then apply Table.FillUp and Table.FillDown to Section Descr and Location Descr columns of your tables. Smth like this:

let
    Source = your_table,
    g_columns = {"Section", "Location"}, 
    descr = {"Section Descr", "Location Descr"},
    e_columns = List.RemoveItems(Table.ColumnNames(Source), g_columns),
    g = Table.Group(
        Source, g_columns,
        {"groups", each 
            Table.FillDown(
                Table.FillUp(_, descr),
                descr)}
    ),
    expand = Table.ExpandTableColumn(g, "groups", e_columns)
in
    expand

[AnkitKukreja · Accepted solution]

Hi @LWBB 

You can use Merge query and achieve this. Merge Queries.