Here is "Our Take" on the situation.
This is a classic case of a silent architecture change breaking a workflow that was never designed for the long haul. The user's token is expiring every few hours because Power BI semantic models have tightened their authentication lifecycle, and their Excel workbook is still trying to authenticate the old way, like a keycard that works on the first swipe but gets denied on the second. The real issue isn't the login prompt itself; it's that the prompt keeps reappearing even when the credentials are correct. That tells us the token is being invalidated server-side, and the connection isn't being refreshed silently.
For anyone relying on Power Query to pull data from a shared semantic model into Excel, this is a warning. What worked "perfectly fine for a long time" was a fragile equilibrium, not a robust solution. The AnalysisServices.Database function with a custom query was likely using Windows authentication or an organizational account that assumed persistent session tokens. Recent updates to Power BI service, whether a tenant policy change, a conditional access rule, or a security patch, have likely shortened the token's default lifespan. The result is a manual re-authentication loop that kills productivity and defeats the purpose of automation.
The practical fix is to stop treating Excel as a persistent data warehouse client. Instead, consider using a Power BI live connection that respects the same token lifecycle but handles re-authentication in the background. If that's not an option, the user should check whether their organization has enabled "token lifetime policies" in Azure AD, or if the Power BI admin has set a session timeout that conflicts with the workbook's refresh schedule. The simplest workaround today is to schedule a VBA macro that triggers a refresh on a timer shorter than the token expiration window, but that's a bandage, not a cure.
The deeper takeaway is that legacy spreadsheet habits are colliding with modern cloud security. This user's problem isn't a bug; it's a design tension between Excel's open-ended refresh model and Power BI's zero-trust authentication. The correct response is to accept that persistent connections to live models require a different architecture, one that either uses a gateway, a service principal, or a dedicated dataflow. Until that shift happens, every "perfectly fine" Excel connection is one token rotation away from breaking again.