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 
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.
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!
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.
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…
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 
So, here’s what I should have done instead…
- Add a new query called ‘Invoices New’ using Get Data > From Folder
- Use the Advanced Editor to copy the M code from ‘Invoices New’
- Paste the M code into the existing ‘Invoices’ query
- 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
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.
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.
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.
