Need help with dynamically adding new columns to the table

Hi Anonymous ,

This is a common scenario when dealing with Excel data that keeps evolving over time with new columns added quarterly. The challenge here is that when columns are added horizontally (i.e., new months or quarters), Power BI doesn’t automatically treat them as additional rows unless some transformation is done in Power Query. You were on the right track with the unpivot method — that's generally the best way to handle dynamic column additions, especially for time series data.

Here’s a suggestion to make this more flexible: In Power Query, unpivot all the date-based columns (e.g., months/quarters), turning them into a single column with dates and another with the values. This way, when new columns are added in Excel and you refresh the query, they’ll automatically appear as new rows. Then, in Power BI, you can build a matrix or table visual that dynamically grows with time.

To handle the % change or YoY calculations you mentioned, use measures instead of calculated columns. Once your data is in a long format (after unpivot), you can write DAX to compare the current period with the previous one using functions like CALCULATE, PREVIOUSQUARTER, or DATEADD. This way, your matrix will remain dynamic, and your measures will still work as new columns (i.e., dates) are added in Excel.