cancel
Showing results for 
Search instead for 
Did you mean: 
Greg_Deckler

The New "Copy Query" Behavior

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:

Greg_Deckler_0-1626816618438.png

 

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:

Greg_Deckler_1-1626816946729.png

 

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:

  1. Click on my Accounts query
  2. Click on the Home tab
  3. Click on Advanced Editor
  4. Click in the Advanced Editor code area
  5. Ctrl-A
  6. Ctrl-C
  7. Click the Cancel button
  8. Click New Source > Blank Query
  9. Click Advanced Editor
  10. Click in the Advanced Editor code area
  11. Ctrl-A
  12. Ctrl-V
  13. Click the Done button

So, that operation instead of:

  1. Click Accounts
  2. Ctrl-C
  3. 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:

  1. Put it back the way it was, nobody wants Copy to work this way
  2. Perhaps an Option in Options and settings to set whether copy works the old way or the new way
  3. 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.

 

Comments

Hi @Greg_Deckler 

Just curious, why not using query Duplicate option for this task.

Duplicate query Accounts for copying the query with initial parameters.

Then Query Copy/Paste pattern to copy whole train.

Thanks a lot!

@t_ar_ik It's a good point although I would expect it to work exactly the opposite. Duplicate should copy the entire entire query train while copy/paste should only copy the selected query.

 

To put this in equivalent terms. If I select a particular word or character in Microsoft Word and copy and paste, it does not copy and paste the entire sentence or paragraph. If I select an Excel spreadsheet cell and copy and paste, it does not copy and paste any cells referenced in a formula in that Excel spreadsheet. The behavior is simply counter-intuitive.

Polls
What is your favorite Power BI Feature release in July 2021?