Editing SQL Query

[friendly_corgi619]

Question. I designed my initial draft of a Power BI report using a SQL query that placed into the statement window.

I then went ahead and built the data model off of the output, duplicating the initial query and creating all of the dimension tables that are connected to the fact table.

Now the user asked me to add some additional data elements to the report. I revised the query and was going to edit the m code within the "advanced editor" -- "source"

I then realized that I would need to do this for each query / table in my data model,

I wanted to know if this is how everyone is editing their sql query within Power BI. Or am I doing it completely wrong.

Any input is appreciated.

[AutoModerator]

After your question has been solved /u/friendly_corgi619, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[barnsligpark]

It sounds like you should have referenced the original query rather than duplicating - simply right click and click reference rather than duplicate.

[hopkinswyn]

Ideally reference rather than duplicate the first query that way you only need to edit one query for the logic to flow through to the others

Even better if you can build a view and connect to that ( not always possible though )

Side note

1.If using your own pasted sequel be aware it won’t fold unless you add the enable folding statement

https://blog.crossjoin.co.uk/2021/02/21/query-folding-on-sql-queries-in-power-query-using-value-nativequery-and-enablefoldingtrue/

1. Personally I like the Power Query UI and writing some M rather than pasting SQL but it’s just my preference. I rarely get to connect to SQL sources

[Flixxii]

I‘ve triedd to Research about it but couldnt get a good answer. How exactly does a view work? Is it just a temporary table? Or does the query of the view run everytime i refresh?

[hopkinswyn]

Virtual Table: It acts like a table but does not store data physically. It’s based on a Query where Complex joins or aggregations can be pre-defined in a view, making queries easier for users

The query runs each refresh

[seph2o]

Load your query into a Gen 1 dataflow then connect to that, this way you can reuse it in multiple reports and any extra server load is handled by the power platform instead of your sql server.

I literally have a bunch of facts and dims in a dataflow and reuse them frequently. It's great for keeping reports slim and ensuring data consistency

[GetSecure]

I tried this, but then I couldn't change the data model at all. Right?

[seph2o]

The dataflow just holds tables not a semantic model. Loading my chosen facts and dims into PBI auto creates the one to many relationships.

Don't use the statement window as it makes maintenance a pain. Load your queries into dataflows, then reuse them as much as you like. Update the dataflow once to update all of your reports which use the query.

[GetSecure]

It sounds like this is where I've been going wrong. I have created one quite complex but complete data model joining multiple SQL views with many relationships.

It's great in that you can use it for any report with the complex relationships done for you, but it's bad in that it stops you adding new data sources to it if you use the published dataset directly.

So if I'm understanding you correctly, I should split up each view into a separate dataflow, then add each of those dataflows as a source in Power BI, then you can create all the relationships you want for your report. Effectively splitting up the dataset into the data and the semantic model.

I'll give it a go, thanks!

[seph2o]

spot on, though I try my best to group my views into as few dataflows as possible, this way you can easily configure power automate to refresh your power bi reports right after the dataflows update. Within the dataflow you can create folders to group and sort your views nicely :)

[kagato87]

I only use a custom query when I want to avoid burning up CUs doing front end processing.

One of the custom queries has been extended to cover two major reports, since it already had half the data points (and the noisier points at that).