That same workbook was imported into Power BI and an equivalent Matrix visualization was created with the same information as the pivot table in the Excel version of the model.The data was then landed from the Data Model into a Pivot Table.
#Get and transform excel 2013 download 64 Bit
The catalyst for this was that the 52,000 row model was taking ages to refresh. My client sat down with his IT department (he is lucky enough to have one that is super cooperative) and worked through some performance testing. Many of us build models like this.Now, let me ask you a skill testing question… when you click Data –> Refresh All, how many times is the stored procedure executed?The answer is 1, right? Trust but verify… Pretty normal really it pulls data from a SQL stored procedure, 4 excel tables and a custom function. In fact, this is based on a real model that one of my clients built. If you’ve been working with Power Query for any amount of time, you’ll start building models that look like this: I mean, let’s face it, it only makes sense, right? Connect to the data source once, and re-use that information over and over again as you spin off different shapes for the rest of your tables.So what if I was to tell you that – from a performance standpoint – this is only actually a good practice in Power BI desktop, and probably the worst thing you could do to your Excel model? That doing this will drive Power Query refresh speeds down? How’s that for a click-bait paragraph? Let’s take a look… A sample model It’s a technique that I’ve been preaching on my blog for years, and is something that we teach in the Power Query workshop as well. Back on May 18, Reid posted a great post on how you should set up your Power Queries using referencing.
0 kommentar(er)
