How to add dynamic columns to existing table

[ShashankPala]

I have build report which is working fine in the local desktop powerbi app. Now after scheduling refresh its failing with an error "

Failure details: The last refresh attempt failed because of an internal service error. This is usually a transient issue. If you try again later and still see this message, contact support.

The '<oii>1/25/2025</oii>' column does not exist in the rowset. Table: gzzu2apppdl002 (2).

"
I have dates as columns in my report which change dynamically everyday as it fetches latest 30 days data. how to automatically add the new columns to the report everyday and bypass the issue of report looking for the deleted date column that wipes off after refresh, meaning dates out of latest 30 days will be not loaded into the report. Please suggest a resolution.

[MattiaFratello]

Hi @ShashankPala, you could unpivot your columns in Power Query.

Instead of keeping dates as column headers, transform your data into a row-based structure so that new dates automatically appear as rows instead of new columns.

1. Go to Power Query Editor 
2. Select all date columns
3. Click Transform → Unpivot Columns

If you are using a DB you could also change the column names within your query dinamically

[TomOs]

The issue happens because Power BI expects a specific column that no longer exists in your dataset. Since your report dynamically pulls the last 30 days of data, old date columns disappear, causing the refresh to fail.

The best way to fix this is to restructure your data so dates aren’t column headers. Instead, unpivot the table so that each row has a Date column. This way, Power BI doesn’t rely on specific column names and dynamically adapts as new dates appear. If restructuring isn’t an option, modify your Power Query M code to use Table.UnpivotOtherColumns, allowing Power BI to handle dynamic columns better.

[ShashankPala]

How to achieve this ? "if restructuring isn’t an option, modify your Power Query M code to use Table.UnpivotOtherColumns, allowing Power BI to handle dynamic columns better."