Forum Discussion
Is this possible to do as a direct query?
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!!
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
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