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.
Index
columnTable.ReplaceValue
to replace the cell in the appropriate column/row
Index=5
as the column as I chose to add zero-based 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"})