powerbipowerquery

how to change a specific cell in Power Query (M Language)?


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.


Solution

  • #"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"})