Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
JackFrench
Frequent Visitor

How To Make A Power Query Table Automation Dynamic?

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?

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors