Need help with dynamically adding new columns to the table

[abhishek_2593]

Hi Team,

I have an excel source with multiple rows and columns and in each quarter a new column is added to the source table in excel that has the details of some %s for each rows.

However, i am unable to dynamically add these columns to the tables whenver a new column is added in the sources and refreshed.

I tried matrix unpivot method but in this method i am unable to calculate and add few measures to the matrix visual
For Ex - % change from this quarter to the previous, YoY change etc.,
How can i overcome this challenge?

Here are the screenshots of the tables for your reference.

Data source (excel)

Intended and current visual i have created

Thanks in advance.

[v-tejrama]

Hi @abhishek_2593,

Has your issue been resolved?If the response provided by @rohit1991 addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you for your understanding!

[v-tejrama]

Hi @abhishek_2593 

Just wanted to check if you had the opportunity to review the suggestion provided @rohit1991 .

Thank you.

[rohit1991 · Accepted solution]

Hi @abhishek_2593 ,

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.

[danextian]

Hi @abhishek_2593 

Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.