Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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.
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.