Is this possible to do as a direct query?

[User456]

I have been asked to create a report that will query a set of journals created on a server in the past year. It will need to pull the names of the journals from one table, using their EndMessageUTC's to find the ones from the past year, then run a query on those tables to pull out the necessary data. The only permissions I have are to connect to the server and view the databases. 

I can perform the query fine outside of Power BI, but my boss wants me to do it as a direct query in Power BI and I keep running into problems, mainly trying to design the query without CTEs. Before I waste more time, is this even possible as a direct query?

I'm fairly new to Power BI, so I definitely may be missing something obvious. 

[v-sathmakuri · Accepted solution]

Hi @User456 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Omid_Motamedise  and @nathancwatkins  for the prompt response.

The best practice is to create a view or stored procedure that merges the journal tables for you filtered on the date range and using the view in the power bi to view the data.

If database views aren't available, you can use Power Query in Import mode to first query the table listing journal names using their 'EndMessageUTC' dates, filter it to journals from the past year, and then dynamically import data from each corresponding journal table by combining them. This approach leverages Power Querys dynamic data source capabilities to pull and merge data from multiple tables, but it requires Import mode since it’s not supported in DirectQuery.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you!!

[Omid_Motamedise]

Depending to the case as CTEs  and some advanced SQL syntax are not supported in Power BI’s native query builder in DirectQuery mode, and Power BI does not support dynamic querying across multiple tables based on metadata, such as dynamically querying journal tables using names from another table.

[nathancwatkins]

Can't you just write the CTE expressions as custom SQL and then do your other transforms? You know, right in your custom SQL box? I use LEAD OVER/PARTITION BY all the time to make indexes and offsets and do duration math using the prior row values.

--Nate

[User456]

Sorry, I probably should have just left out the "mostly CTEs". While that has been an issue, I've been able to find a workaround most of the time. That's just the start of the issues I've had, though. Based off of the other responses, I think the answer is that it is not really possible as a direct query without at least having access to create a view on the database. 

Next time I'll wait until my brain isn't mush from hours of failed queries haha