How To Make A Power Query Table Automation Dynamic?

[JackFrench]

I have created an automation using power query where it'll fetch data from a workbook called "Data". I have manipulated the data to achieve my desired table format. The table's final columns are: Vendor Name, Contract #, and (after pivoting the columns) the Total Amount Outstanding for the years 2019-2024 (6 separate columns). The problem I am running into is that some of my input, "Data", workbooks only have years 2019-2023 while some have years 2020-2024. Since the initial data file I constructed my table around contained the years 2019-2024, any file I load into the table that does not include the same years reports an error message saying: "The column 2024 of the table was not found". I know these sorts of things work great with standardized datasets but in this case, my years will fluctuate by one or two. How can I make my table more dynamic so that regardless of whether the file contains the same years (2019-2024) or less (i.e, 2019-2023, 2020-2024, etc...) it will still read it in and perform my desired formatting without any error messages?

[lbendlin · Accepted solution]

Unpivot your data so your final result (what Power Query hands over to Power BI) will have fixed number of columns with fixed column names.