Introduction
At first I thought I was going insane but a recent phone call from a certain Senior Program Manager at Microsoft confirmed that I was not crazy, the “copy” behavior for queries seems to have changed and not being aware of this change can cause real issues. I am not sure when this behavior changed, I went back through the “What’s New” announcements all the way back to December 2020 and could not find any mention of it. In any event, I will endeavor to keep this article from becoming a rant and explore this new behavior’s pros, cons and work-a-rounds but no promises.
The Setup
Let’s say you have three parameters, one data source query and another query that references the data source query and navigates to a particular table. Your three parameters specify the SQL server name, SQL instance and SQL database. The data source query simply has a Source statement that uses the three parameters to connect to the server. The other query, Accounts in this case, references the data source query and simply navigates to a table. The three parameters are in a Parameters query group, the data source query is in a Sources query group and the Accounts query is in the Other Queries query group:
The Behavior
Let’s say you now want to add another table to your data model, the Products table. So you figure, “I’ll just copy and paste the Accounts query and switch the Navigation step”. With the old behavior, you would have ended up with a single new query called Accounts (2) that still referenced the same data source query, SQL Server Source.
With the new behavior, you get this train wreck:
Now, even outside of this being totally and completely unexpected behavior after working with Power BI Desktop since it was released, I have a few complaints. First, I can’t imagine ever wanting copying a query to work like this…ever. I just don’t see the use case. If someone has one, please comment. Second, it copied my parameters and data source queries but didn’t even put them in the right groups where they belong. Third, now to get what I was trying to achieve, I have to blast the three duplicate parameters, duplicate source query and then hand edit my Accounts (2) query to refer the original source query.
What to Do
OK, OK, I sort of promised not to turn this into a rant. So, what to do with this new found bit of knowledge? Well, let’s first start with a work-a-round. The only way to achieve what I was originally going for is to do the following:
- Click on my Accounts query
- Click on the Home tab
- Click on Advanced Editor
- Click in the Advanced Editor code area
- Ctrl-A
- Ctrl-C
- Click the Cancel button
- Click New Source > Blank Query
- Click Advanced Editor
- Click in the Advanced Editor code area
- Ctrl-A
- Ctrl-V
- Click the Done button
So, that operation instead of:
- Click Accounts
- Ctrl-C
- Ctrl-V
Now, one could argue that this is all a good thing. I wouldn’t argue that, but someone, somewhere might conceivably argue that this provides more and better functionality than before because the ability to copy a query and all underlying queries at the same time didn’t exist before. And since you can still get a copy of a query using the joyous 13 step process described above, overall functionality has been added and not taken away. Again, I wouldn’t argue this, but I will simply point out that perhaps this argument exists and you can judge for yourself whether it is an intelligent argument or not.
What I Suggest
As you might have picked up on, I am not a big fan of this change. Especially since it just sort of got sprung on people. Now, this is not a DAX Time Intelligence level of hatred, I just find it all annoying and useless. Now, I have long since given up on the delusion that my opinion matters on pretty much any topic but if I may be so bold, I would like to make a few suggestions to “fix” this:
- Put it back the way it was, nobody wants Copy to work this way
- Perhaps an Option in Options and settings to set whether copy works the old way or the new way
- Create two copy options, Copy and “Copy All” with Copy being the old way and Copy All being the new way. Provide an Option in Options and settings to default Ctrl-C to one or the other
Mere suggestions on how things might be improved versus the current state of affairs.
Conclusion
Whereas copy and paste in every other piece of known software on the planet is a time saving feature, the Power Query Editor has managed to make the Copy feature utterly useless because it creates more work rather than less work. Feel free to disagree and set me straight in the Comments. Or, if you agree with me, please vote for my Idea here: Microsoft Idea · Power Query Editor Copy behavior (powerbi.com). Also, please, please, please correct me if I am wrong and it has always worked this weird way. Although, even if it has, I still say it needs to be changed.
Be the first to comment