Whoa! I can’t believe it is summer and almost 4th of July! It’s been WAY too long since I’ve blogged – I’ve just been SUPER busy here at P3! But I can’t let my Pug Meme readers down Open-mouthed smile

It seems that Microsoft has been SUPER busy too! I don’t know about you but the June 2019 Power BI Desktop update to the change colors from BLACK to WHITE is still taking me some time to get used to. One of Microsoft’s favorite things to do… just when you get comfortable with the look and feel of the application… NOPE, they’ve got to change it up!

In any case, today I want to share a SUPER quick lesson learned from one of my Power Query Fails! They say that you learn and grow by making mistakes… ha, ha – indeed!

Here’s the simplified GIVEN scenario:

1. You’ve created a Power BI Desktop solution using Power Query to connect to your data sources.

2. During initial development, your data sources are coming from one Excel file for each source.


3.  You develop a Data Model with Relationships, Measures, and Visuals – it’s beautiful! Done.

Data Model with Relationships, Measures, and Visuals

And now for the all so real-life MODIFICATION scenario:

4. You now need to change Power Query for your “Invoices” data table to instead of using just the one Excel file with the Invoice data to now use multiple Excel files with Invoice data. No worries, you can just use the From Folder data source connector in Power Query!

Get Data

So, do you…

A.) Delete the current ‘Invoices’ query and then create a new query called ‘Invoices’

B.) Change the query for ‘Invoices’ to ‘Invoices Old’ and add a new query called ‘Invoices’

C.) Add a new query called ‘Invoices New’

D.) None of the above

Take a minute and decide your answer.

Choose Wisely

Well, I must admit I was multi-tasking… and when I came back to Power Query, I went with option B.) and changed the query called ‘Invoices’ to ‘Invoices Old’ and added a new query called ‘Invoices’ using the Get Data > From Folder. I was happy with my changes in Power Query so Close and Load it is! And then…

The answer is D

Yes, that’s right! The answer is D.) None of the above! Did you choose wisely? And there is no undo in Power Query after a Close and Load Sad smile

So, here’s what I should have done instead…

  1. Add a new query called ‘Invoices New’ using Get Data > From Folder
  2. Use the Advanced Editor to copy the M code from ‘Invoices New’
  3. Paste the M code into the existing ‘Invoices’ query
  4. Delete ‘Invoices New’

This technique is also known (by me & now you) as the “Shimmy Shake”. It’s where you shift objects around to swap out an existing object with a new object – in this case, the object is a query in Power Query.

And the reason why I should have done the “Shimmy Shake” is because I chose option B. (Changing the query called ‘Invoices’ to ‘Invoices Old’ and adding a new query called ‘Invoices’).

I lost all of my measures that were created and attached to the ‘Invoices’ data table!

And all of my visuals with those measures were broken as well!


In that moment, the way that my brain was thinking, was that the query name just needed to be ‘Invoices’ and then all would be well in the Power BI Data Model world.

Nope! In fact, all of my measures had been dropped and my visuals broken Sad smile

Fields that need to be fixed

The Data Model remembered the initial query ‘Invoices’ as the query that was loaded to the Data Model. Power Query did not understand my intentions of the “Shimmy Shake”!

Another technique that would have avoided the problem of losing measures attached to the ‘Invoices’ table would have been to have created a Measure Table and then to attach the measures to the measure table.

Measures to measure table

Now, with the holiday coming up, or really any day for that matter… save yourself the headache and frustration from this Power Query Fail and choose wisely when making modifications in Power Query!

Let me know in the comments if you’ve had this or some other Power Query Fail.

Sharing is Caring

Microsoft’s platform is the world’s most fluid & powerful data toolset.  Get the most out of it.

No one knows the Power BI ecosystem better than the folks who started the whole thing – us.

Let us guide your organization through the change required to become a data-oriented culture while squeezing every last drop* of value out of your Microsoft platform investment.

* – we reserve the right to substitute Olympic-sized swimming pools of value in place of “drops” at our discretion.