Automation from a folder.

Below is how you can fix.

The #"Expanded Table Columns1" step refers to your sample file.

You can replace that reference to a reference to the field created earlier representing your XLS file.

I commented my changes. You may have to adapt to your own situation....

let
    Source = Folder.Files("C:\ y"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "2024")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    // --> This is causing the error: #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    // Replace with
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Removed Other Columns1"{0}[Transform File])),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Name", type text}, {"Data", type any}, {"Item", type text}, {"Kind", type text}, {"Hidden", type logical}})
in
    #"Changed Type"