skip to Main Content

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.

Invoices

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!

DOH

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.

Krissy Dyess

Prior to joining PowerPivotPro, Krissy spent 16+ years at a Sales BPO company and held roles in Reporting, Analytics, Modeling, Strategy, Management, and Consulting teams. She primarily used data from MS SQL Server, SalesForce.com, Tableau, Cisco, and Avaya for clients including: Facebook, Google, MS Bing, Skype, AT&T, T-Mobile, Sprint, IBM, Lenovo, Toshiba, FedEx, TIAA CREF, and Nissan. Krissy is a SQL data ninja queen but decided to explore learning DAX and Power BI in her quest to clean and wrangle “dirty” data from disparate client systems and to put that data into the hands of the Business Analysts. Her passion is helping others discover the full potential that Power BI & PowerPivotPro can provide.

This Post Has 21 Comments
    1. Hi Matthew! Good to know that I’m not the only one 🙂 Hopefully you didn’t have a lot of lost measures and/or broken visuals!

  1. I picked non of the above, BUT, wasn’t sure what I would have done. Thanks for the reminder. By the way, I too am having trouble with the all white. I wish they would give us options, even if limited like Office and Visual Studio.

    1. I guess I should have had a 5th option for “E.) Not sure” 🙂
      Yes! Isn’t it interesting how our brains get in the habit of seeing applications a certain way. Just think, people new to Power BI Desktop (> June-2019) will never know the experience of Power BI Desktop black! … unless, someone submits an idea to give us a color option: https://ideas.powerbi.com/forums/265200-power-bi-ideas

  2. So don’t rename your query or your data model will break? Even if you aren’t doing a Shimmy Shake?
    Must be a way to rename a source query without a catastrophe.

    1. Catastrophe indeed! But I will remember the next time – right!

      You can rename a query – that is okay. Power BI will update the table name in the Data Model and maintain your measures and even update any existing measures with the new table name… which is AMAZING! This wasn’t always the case!

      However, the way that I had attempted to 1.) Rename “Invoices” to “Invoices OLD”, 2.) Create a new query set of instructions as “Invoices” and then 3.) Delete “Invoices OLD” was the catastrophe!

      Create a mock-up example and give it a try to see 🙂

  3. That’s a great blog post. I would possibly look at keeping the measures in the invoice table. Based on your method above they should still work.

    The reason to keep them in the invoice table is when using Q&A. CurreQ&A needs to be on the same table as the measures in order to successfully get the right answers. With the new Q&A features coming it will even more reason to keep it with the table.

    1. Hi Gilbert!

      Thanks for leaving a comment and reminding us about Q&A functionality – that is an excellent point! I’ve personally never been a fan of Measure Tables, but it would an option to avoid the Power Query fail. But could then lead to a Q&A fail 🙂

      Likely, since the Nov-2018 Power BI Desktop monthly update, people are using Folders in the Model View versus Measure Tables. Chris Webb has a Blog Post illustrating Folders in Power BI: https://blog.crossjoin.co.uk/2018/11/14/nested-display-folders-in-power-bi/

      However, those Folders are attached to the source table from Power Query – so instead of just losing Measures and Visuals then you would lose those Folders too 🙂

  4. Perhaps take the approach to split out the Connection / Processing queries from the Loads, then the queries in the back end can change without any impact to the loaded tables, and any measures connected to them.

    1. Hi Phil!

      Yes, that would work.

      If I understand your suggestion correctly… you would have a “Staging_Invoices” query and a reference query called “Invoices” that references “Staging_Invoices” as it’s source. Then you could change the applied steps in the “Staging_Invoices” query and those changes would flow through to the final “Invoices” query… which then gets loaded as the “Invoices” table to the Data Model. No lost measures or broken visuals!

      … but does this technique have a fun name like the “Shimmy Shake” 🙂

  5. I follow Phil for that one. It’s one of the best practice explain in the really good book “M is for data monkey” to actually avoid the as well Firewall error PQ can send you from time to time.

  6. Hi Gilbert!

    Possibly behind the scenes they could create a solution or at least warn you 🙂 Power Query is pretty AMAZING but I’ve found this Power Query fail and there are 2 others that I’ve run into to be aware of:

    1.) If you change the Source and Navigation steps using M code in the formula bar + the user interface, you will get a warning asking if you are sure to change the Navigation step. Once you say YES, you will lose all the Applied Steps occurring after the Navigation step – so watch out for that one! Again, I usually use the Advanced Editor to make these kind of changes to avoid the loss of remaining Applied Steps… now that I know what happens 🙂

    2.) Also, be careful of the Expand operation! If you make changes upstream, the Expand doesn’t pick up on those downstream… yet! For example, if you follow best practices and Remove Rows and Columns not needed as one of your very first steps… And later in the instructions you have an Expand operation. If you decide to go “back in time”, likely in the Choose Columns step and now include new columns that weren’t part of the initial step of Applied Steps leading up to the Expand and you want those new columns to flow through… the Expand will not automatically detect them. You need to open the Expand interface using the gear icon and then check the box to include those columns going forward.

    Would love to hear other Power Query fails from the community!

  7. Hey Krissy, thanks for reporting this issue. I’m from the PQ team and we’ll track this with a bug and work with the modelling team to either prevent you from deleting queries with measures, or at least present a confirmation.

  8. Thanks for the great tip Krissy. Your tips have been very useful for our team and have helped us elevate productivity to levels never seen before 🙂
    Hope you had a great 4th of July week

  9. This is a great post that I wish I’d read and re-read on several occasions over the last three years. It also highlights two issues that should be fixed: The difficulty in assigning a different data stream to an existing powerpivot model, (without doing the shimmy shake) and the inability to recover from answers a,b, and c above.

  10. Regarding Fail 2.) that you mention in response to Gilbert’s comment:

    There’s a way to ensure that you always expand all the rows in a table, regardless of whether the number or names of columns change for any reason.

    You can create a list of all the columns in the table using the Table.ColumnNames function, and pass this into the Table.ExpandTableColumn function that’s generated when you click the expand button. It’s a bit more complicated than I make it out to be; you also have to throw a List.Union in there; but you can set up a custom function for yourself so you can always have the functionality handy (I’m not sure if there’s a suggestion to make “Expand.AllTableColumns” an out-of-the-box function).

    Imke Feldman gives a great explanation in the Mr. Excel Forum
    https://www.mrexcel.com/forum/power-bi/952568-power-query-expand-all-columns.html

    and goes a bit deeper in the Power BI Community
    https://community.powerbi.com/t5/Desktop/Expanding-Multiple-Data-Tables-with-Unknown-Column-Names/td-p/471874.

    Regarding Fail 1.) I’ve also started to branch off my queries, usually by referencing them, almost every time they require drilling to a new directory, moving from the directory level to the file level, or when there is a navigation step (like the way I used to save video games every time I was walking into somewhere I thought I might die).

    It makes them so much more portable and flexible. Since sometimes I work for places that don’t have Office 365 or Power BI Pro licenses, if I structure my queries that way I can then use this nifty trick
    https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/
    To (sort of) mimic row level security using parameters and/or folder permissions (instead of the countries in the article, the user could enter a file directory to which they have access).

    It also just saves a lot of hassle if people change the names of directories or move things around.

Leave a Comment or Question