How to change the value of one field in a column

[TimTeska]

Hello Experts,

I want to be able to change the value of row 6 in [Custom] column to say "Period".  The reason for this is because the next steps I make will be to 1) remove the top 5 rows and then 2) "Use First Row As Headers".

The reason why it's important to change the value on row five to "Period" so the column header becomes "Period" is because this data gets refreshed by replacing the source Excel file with the next quarter.  So if I simply just remove the top 5 rows, and then "Use First Row As Headers" and change the column name from "2023_Close_Q2...." to "Period" I get an error when I  replace the source Excel file with the next quarter and refresh with Q3's data because it's looking for "2023_Close_Q2...." as the column name to change it to "Period".

**The [Custom] column is created using the value from [Column1]{3}, Therefore, this value changes with every new quarter that the source data report is run.  see below.

Any help would be great appreciated - Thank you!!

[collinsg]

Good day TimTeska,

Below I have pasted a custom function which takes a table and replaces the value in a given record and column. In your script you could add the step 

= fn(#"Added Custom",5,"Custom","Period") 

This would replace the value in row 6 and column "Custom" with the value "Period". (Row 6 is record 5 because records are indexed from zero). To use the function copy the code below and paste it into Power Query as a query. Name the query "fn" and then add the line above to your script.

Hope this helps

(tbl as table, recordIndex as number, columnName as text, newValue as text) as table =>
let
    replaceRecord = Table.ReplaceRows(
        tbl,
        recordIndex,
        1,
        {
            Record.FromList(
                List.ReplaceRange(
                    Record.ToList(tbl{recordIndex}),
                    List.PositionOf(Table.ColumnNames(tbl), columnName),
                    1,
                    {newValue}
                ),
                Table.ColumnNames(tbl)
            )
        }
    )
in
    replaceRecord

[TimTeska · Accepted solution]

I found the answer from another post.

Create a conditional column that would look at the column next to [Custom] for a header value (in this case it was "Effective Tax Rate", if so ="Period" otherwise [Custom]..