Structure of BigQuery stored procedure:

with cte
(
    select ....
)
select cols
from table1
join cte
where col = dateparam
union all
select cols
from table2
join cte
where col = dateparam**

Structure of PowerBI calling the stored procedure:

let
    ProjectId = "your_project",
    DatasetId = "your_dataset",
    SPName = "sp_example",
    InputDate = Date.ToText(DateParam, "yyyy-MM-dd"),
    SQLQuery = "CALL `" & ProjectId & "." & DatasetId & "." & SPName & "`('" & InputDate & "')",
    Source = GoogleBigQuery.Database(ProjectId),
    RunSP = Value.NativeQuery(Source, SQLQuery)
in
    RunSP

I am getting this error:

native queries aren't supported by this value