A solution does exist. You can use a stored procedure in a SELECT FROM OPENROWSET Function.

Prerequisites

Note: OPENROWSET is without spaces.

Table Variable

Stored Procedure script can not use the TempDB, because OPENROWSET doesn't support it. A solution exists, but it's not easy and it isn’t a quick win. You can use a table variable within the stored procedure code if you need a temporary table as part of your process.

DECLARE @LOCAL_TABLEVARIABLE TABLE
(column_1 DATATYPE, 
 column_2 DATATYPE, 
 column_N DATATYPE
)

Insert

DECLARE @ListOWeekDays TABLE([DyNumber] INT, [DayAbb] VARCHAR(40) , [WeekName] VARCHAR(40));
 
INSERT INTO @ListOWeekDays
VALUES
(1,'Mon','Monday')  ,
(2,'Tue','Tuesday') ,
(3,'Wed','Wednesday') ,
(4,'Thu','Thursday'),
(5,'Fri','Friday'),
(6,'Sat','Saturday'),
(7,'Sun','Sunday')
;
	
SELECT * FROM @ListOWeekDays;

Update / Delete

DELETE @ListOWeekDays WHERE [DyNumber]=1;

UPDATE @ListOWeekDays SET [WeekName]='Saturday is holiday' WHERE [DyNumber]=6;

Enable Ad Hoc Distributed Queries

On the source SQL Server you must enable Ad-Hoc Distributed Queries

sp_configure 'show advanced options', 1
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
GO

Power BI Custom Query

Windows Authentication

SELECT *
FROM OPENROWSET ( 
	'SQLNCLI',
	'server=<SQLservername>;trusted_connection=yes;',
	'SET FMTONLY OFF; EXEC <dbname>.<schema>.<storedprocedure>;'
);

SQL Authentication

SELECT *
FROM OPENROWSET (
	'SQLNCLI',
	'server=<SQLservername>;Persist Security Info=True;UID=<SQLlogin>;PWD=<yourPassword>',
	'SET FMTONLY OFF; EXEC <dbname>.<schema>.<storedprocedure>;'
);

Microsoft SQL View

CREATE VIEW dbo.PBIView_encrypted With ENCRYPTION AS
SELECT * 
FROM OPENROWSET (
	'SQLNCLI',
	'server=<SQLservername>;Persist Security Info=True;UID=<SQLlogin>;PWD=<yourPassword>',
	'SET FMTONLY OFF; EXEC <dbname>.<schema>.<storedprocedure>;'
);

GO

Source

https://www.kodyaz.com/articles/enable-Ad-Hoc-Distributed-Queries.aspx

https://database.guide/how-to-fix-server-is-not-configured-for-data-access-in-sql-server/

https://community.powerbi.com/t5/Desktop/Can-i-call-Stored-Procedure-with-Direct-Query/m-p/267141

https://www.sqlshack.com/the-table-variable-in-sql-server/