Forum Discussion

LWBB's avatar
LWBB
New Member
2 years ago
Solved

Filling in the Blanks

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

 

Need Help

3 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    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

     

     

  • LWBB's avatar
    LWBB
    New Member
    2 years ago

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