I need to append few excel files into Power Query. But overall there is one cell ("Custom1" Row=6) which should be assign as "Bla". How to get around with this using power query ? many thanks in advance.

ps: ("Custom1" Row=6) has a different text value for each excel file.

I need to append few excel files into Power Query. But overall there is one cell ("Custom1" Row=6) which should be assign as "Bla". How to get around with this using power query ? many thanks in advance.

ps: ("Custom1" Row=6) has a different text value for each excel file.

• powerbi
• powerquery

1 Answer 1

• Add Index column
• Use Table.ReplaceValue to replace the cell in the appropriate column/row
  • Note I used Index=5 as the column as I chose to add zero-based Index column.
• Remove the Index column

#"Added Index" = Table.AddIndexColumn(#"Added Custom1", "Index", 0, 1, Int64.Type),
    
    #"Replace with Bla" = Table.ReplaceValue(
        #"Added Index",
        each [Index],
        null,
        (x,y,z)as nullable text=> if y=5 then "Bla" else x,
        {"Custom1"}),
    
    #"Removed Columns" = Table.RemoveColumns(#"Replace with Bla",{"Index"})