New Columns with Specific Row Values from other Columns in Multiple Excel Files

Changes you make to the 'Transform Sample File' query automatically get duplicated in the 'Transform File' function, which is what gets used in the main combination query ('VVO2' in your case). That said, is there a reason not to add the transformations to 'VVO2' after the binaries are combined?

Regardless of where you add the transformations, it's a little difficult to achieve your desired results because we need additional information to determine which text counts as valid fruit or which numbers count as valid years to put into their own respective columns. Is there info in another column that you can check to determine whether a given row in Column3 is of an applicable category?

If not, you may have to just hard-code in what values should be applicable. Something like the below could work:

let
    Source = Excel.Workbook(Parameter1, null, true),
    Sheet = Source{[Name="Sheet1"]}[Data],

    ValidFruit = {"Apple", "Melon", "Strawberry"},
    AddFruit = 
        Table.AddColumn(
            Sheet, 
            "Fruit", 
            each if List.Contains( ValidFruit, [Column3] ) then [Column3] else null, 
            type text 
        ),

    ValidYears = {2023, 2024, 2025},
    AddYear = 
        Table.AddColumn(
            AddFruit, 
            "Year", 
            each if List.Contains( ValidYears, [Column3] ) then [Column3] else null, 
            Int64.Type 
        )

in

    AddYear

Output:

As an aside, in your latest snip, it looks like "Melon" is in your header, so you may want to consider an additional step somewhere that demotes headers (function: Table.DemoteHeaders / button: 'Use Headers as First Row')

Hi, you do not need Transform File in general, but it is easier to manage just transform file for beginners. If you could upload at least 2 files (delete sensitive data in advance), we can try to help you.

Charcho, try to replace whole your Transform Sample File code with this one:

let
    Source = Excel.Workbook(Parameter1, null, true),
    Sheet = Source{[Name="Sheet1"]}[Data],
    F = (tbl as table)=>
        let col3 = Table.ColumnNames(tbl){2},
            Ad_FruitCol = Table.AddColumn(tbl, "FruitColumn", each if col3 = "Column3" then tbl{0}[Column3] else col3, type text),
            Ad_YearColumn = Table.AddColumn(Ad_FruitCol, "YearColumn", each Int64.From(if col3 = "Column3" then tbl{2}[Column3] else Record.Field(tbl{1}, col3)), Int64.Type)
        in  Ad_YearColumn,
    Ad_FruitAndYearCols = F(Sheet)
in
    Ad_FruitAndYearCols

Sorry, but you should think of all possible scenarios in advance and provide comprehensive sample data with expected result. I have provided 3 different codes, as per your requirements, which you have changed over time