Forum Discussion
PowerBI Desktop to Service Refresh
Hi,
I have an issue where the data I am using for my powerBI report is an excel document in sharepoint.
The problem im having is the users running this sharepoint have two columns with no headers that are the today() function and today() + 30.
When I refresh the data in powerBI desktop it sees when these columns change day to day and throws no error and refreshes the data fine.
The issue im running into is when these reports are published to powerBI service and set to automatically refresh those refreshes fail because the column may change from say 1/1/25 to 1/2/25. I can fix this by going into desktop and deleting these columns in power query and republishing the report, however the next day a new column is added with the new date and the following day the report refresh fails again in service once that date field changes in excel.
The solution in my head is forcing the users running the sharepoint to give those columns a standard header so its the same till the end of time in BI.
However my real question is, if powerbi desktop can handle this column changing names and refresh fine why can powerbi service not handle this?
Any solutions beyond the one I mentioned above are appreciated.
Hi AppleMan -you can ask the users managing the SharePoint Excel file to name the columns with Today and Today + 30 headers. These names should remain static so that Power BI can map to them consistently.
Example headers: Today and Today+30.
If enforcing this change is feasible, it eliminates the problem entirely.If possible, enforce standardized headers in the source file. If not, use Power Query to either ignore or dynamically handle the problematic columns. These solutions will prevent refresh failures and ensure smooth operation in the Power BI Service.
ref links:
Refresh breaks due to COLUMN NAME CHANGES? 3 ways to fix changing column headers in Power BI!
Dynamically remove columns that contain a specific word in the header in Power Query – Curbal
2 Replies
Hi AppleMan -you can ask the users managing the SharePoint Excel file to name the columns with Today and Today + 30 headers. These names should remain static so that Power BI can map to them consistently.
Example headers: Today and Today+30.
If enforcing this change is feasible, it eliminates the problem entirely.If possible, enforce standardized headers in the source file. If not, use Power Query to either ignore or dynamically handle the problematic columns. These solutions will prevent refresh failures and ensure smooth operation in the Power BI Service.
ref links:
Refresh breaks due to COLUMN NAME CHANGES? 3 ways to fix changing column headers in Power BI!
Dynamically remove columns that contain a specific word in the header in Power Query – Curbal
How would you go about ignoring or handling the problematic columns?
What I tried was removing the columns in power query, however once the excel formula changes it to a different column value (ie. 1/1/25 to 1/2/25) then the refresh breaks in service and says it cant find the column value 1/1/25.
