SQL query with temporal tables fails when load in powerbi but not in Transformdata

[dobregon]

Hi,

I have a simple SQL query that creates some temporal tables (#temporal) and when i paste the query in the powerquery it runs quickly and without problems but when i click on close and apply, then it gives me an error.

The query is something like
-- Get something
IF OBJECT_ID('tempdb..#table') IS NOT NULL DROP TABLE #table
SELECT
bla
,bla
,bla
INTO #table
FROM dbo.othertable

and this is the error

if i remove the IF bla bla, then the error is that it doesnt like the INTO.

the weird thing is that in the transform data i can see the table as i want with all values and everthing is correct but when i close&apply the system fails and i dont understand. can someone help me on this?

Regards

[dobregon · Accepted solution]

there are 2 possible solutions, becase DAX doesnt admit to use create temporaly tables, etc.

1. Create a view in the database that do everthing

2. create a query that doesnt use temporally tables.

i have used the second option ahd this works for me. in case someone has the same problem in the future, maybe the best solution if you can create views or SPs in your database, is to create one

[v-kelly-msft]

Hi  @dobregon ,

Based on my understanding,it is not only "apply" between power query and power bi, but more options will be created.

Such as check relationships,when you apply from power query,power bi will automatically check the relationships.

Also the syntax between the 2 is different.

Better check the error notification and modify it to the one which power bi can recognize.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[dobregon]

the strange thing is that in powerquery is working properly but then close&apply it fails. i know that it is a query complex but... should work

[AlexisOlson]

I'm not sure if this is the problem but doing drops and inserts in a Power BI query is generally not a good idea. You typically only want read-only access to your database from Power BI.

[dobregon]

this is rgith, but they are temporaly tables to do insertrs, etc