Combining sheets from many excel files with different sheet names, transform then combine

Hi Bizzay ,

What kind of error is returned? Is there a value for [Column5]{3}'s in each table?
What about adding a try like this to catch errors?

    TransformDDR = (t as table) as table =>
        let
            AddedDDRDate = Table.AddColumn(t, "DDRDate", each try t[Column5]{3} otherwise null),
            Result = AddedDDRDate
        in
            Result,
    TransformationFunction = Table.TransformColumns(#"Removed Errors", {"Data", each TransformDDR(_)})

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group