cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dobregon
Post Prodigy
Post Prodigy

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

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

dobregon_0-1633707277048.png

 

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



Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
1 ACCEPTED SOLUTION
dobregon
Post Prodigy
Post Prodigy

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






Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

View solution in original post

5 REPLIES 5
dobregon
Post Prodigy
Post Prodigy

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






Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
v-kelly-msft
Community Support
Community Support

Hi  @dobregon ,

 

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

vkellymsft_0-1633945046577.png

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!

 

 

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



Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
AlexisOlson
Super User
Super User

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.

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



Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors