skip to Main Content

existed data connections banner

With the Power BI craze that kicked into high gear in 2017, it’s less common to be working in PowerPivot in Excel. However, during one of my recent remote consulting calls, one of our many clients needed some help with a solution created in PowerPivot in Excel.

And I often find if ONE person needs help or has a particular question about how to create a solution then it’s possible MANY other people might have a similar question or solution need. As such, that is the inspiration for today’s post!

I also like this solution idea that I’m about to share because it showcases a use case for why someone might prefer a PowerPivot In Excel solution over Power BI Desktop solution.

You can download the excel workbook to follow along with here.

Use Case

Let’s say you have…

  • a DATA table called ‘TheData’ with [Company], [Department], [Employee ID], [Date], [Amount]
  • a LOOKUP table called ‘Employee’ with [Employee ID] and [Employee Name]

image

You want to be able to create a Pivot Table report with the following information from ‘Employee,’ ‘TheData’ and [Employee Comments] …

  • [Employee Name], [Total Data Rows], [Total Amount] AND [Employee Comments]

Report with comments

Requirements

  • The source data comes from a SQL database or some other system source that you do not have access to edit
  • The list of [Employee ID] and [Employee Name] must update dynamically – to include and new Employees or Employee changes
  • The [Comments] will be manually entered and maintained in the Excel Workbook Solution File

 

Solution

The solution will use Data > Existing Connections > Tables to load the results from the ‘Employee’ table in the Data Model to a worksheet where the [Comments] column can be added, edited and loaded back into the Data Model.

Solution Steps

1.) Connect to the ‘TheData’ & ‘Employee’ data sources using Power Query

2.) Choose Home > Close and Load… Only Create Connection and Add this to the Data Model

only create connection

3.) Create a Relationship from the MANY (*) side ‘TheData’[Employee ID] to the ONE (1) side ‘Employee’[Employee ID]

many to one

4.) Then, in a new worksheet, select Data > Existing Connections

existing connections

Now, here’s the trick…

5.) From the Existing Connections choose the Tables tab and Show: Connection Tables, pick ‘Query – Employee’ and select Open

show connection table

6.)  Choose the Table, New Worksheet then select OK

Table New Worksheet

7.)  This will load the data from the ‘Employee’ table in the data model into a new worksheet

8.)  Select Design from the menu and give the table a new name ‘ToTable_Employee’

ToTable_Employee

9.)  Now let’s add a column called [Comments] to the ‘ToTable_Employee’ table

Ad Column Comments

10.)  Select Data > From Table/Range to bring this new dynamic table into Power Query – let’s call it ‘Comments’

Select Data From Table/Range

11.)  Choose Home > Close and Load… Only Create Connection and Add this data to the Data Model

12.)  The ‘Comments’ table loads to the data model, then create the Relationship from the MANY (*) side ‘Comments’[Employee ID] to the ONE (1) side ‘Employee’[Employee ID]

Data Model with comments

13.)  Lastly, we can create the PivotTable report!

Report with comments final

Now, any time a user enters comments on the worksheet, select Refresh on just the ‘Comments’ query in your Data > Queries & Connections pane, and the comments will be added to the report worksheet.  And when you add new Employees or change Employees in the source data, a Data > Refresh All will update the ‘Table_Employee’ information, and you can enter the comments.

Conclusion

PowerPivot In Excel workbooks can be a great solution when you want to easily allow an end user to inject information into a data model report or dashboard!

So, in staying committed to PUG memes for 2019 – I thought this one was entirely appropriate for two reasons…

Reason #1

PowerPivot In Excel is indeed an OLD friend, now that Power BI Desktop has taken center stage! But there ARE many other reasons that a PowerPivot in Excel solution is just what you need.

Reason #2

For our northeastern friends experiencing the Winter 2019 polar vortex, sending you WARM sunshine vibes.

Hello Sunshine

Happy Power Pivoting in Excel 🙂 

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 20 Comments
  1. I tried adding an employee and then refreshing. The comment “This employee rocks!” changed from Mat to the new one. If by any reason someone changes the sort order on the employee list, all comments get shuffled, right?

  2. Super nice and easy explanation for a real case. I am using something similar, but instead of loading data from some database, I connect to several excel files where different employees enter their piece of planning, and the consolidation file is for the manager to analyse and adjust the data. But – the whole thing is extremely slow. In total it results in just around 4000 rows, but I even started getting “out of memory” errors. Do you know what are the limits of how many queries can be handled in Excel before it becomes too much?

    1. Hi Domantas! Thanks for dropping by and taking time to share your use case and challenge! Quick question, is your Excel 64-bit?

      1. No, it is 32 bit. As there are different people going to use these files, I cannot count on all of them having 64 bit version, so trying to make it work on 32 machine. For the test, I downloaded 64 bit version on my test computer, and that is helping to avoid occasional “out of memory” issue, but I also don’t find it faster to calculate for some reason.

        1. Excel 32 bit is not good. If your organization wants to empower you and your teams to BE AMAZING, they really need to understand that 64-bit is required to best use these tools. And depending on projects, goals and the needs of your organization – you may even need to adopt other Power BI solutions & architectures 🙂

          One solution to the Excel 32-bit problem is to use Power BI Desktop because it is 64-bit, free to download and it doesn’t conflict with any existing Excel 32-bit add-ins. However, then you’ll be in the Power BI Desktop environment versus the Excel environment 🙁

          If you prefer Excel, then my suggestion would be to work with your technology teams to help them better understand why 64-bit is needed. This is not always easy and PowerPivotPro can help you and your organization navigate how best to integrate these technologies.

          Please reach out via our CONTACT US for more information on how we can help!

          1. I am also using Power BI quite extensively, but as you said yourself – for some cases the best is to stay within Excel. But as now we have an option to simply download another version from office.com and don’t even need IT support, I believe this is what I need to pursue 🙂

  3. I have to say, I’m in this position and the reality has not lived up to the potential. I’m building an Excel tool which handles data input and processing for some less Excel-literate team-members, and I used PowerQuery/Powerpivot to handle the translation between input and processing, as well as managing external lookup tables. Despite it being a relatively small book, the sheet takes some 40 secs every time it “Establishes a data connection” for the first time, and 1:20-1:40 to load the datasets via Powerquery (the tables range from single lines of data to 8 rows max). I believe this is due to the “breadth” of the data model (11 tables and 13 calculated columns), but it still feels like absurdly long load-times for small-time data, and I’ve had a hell of a time streamlining it for little-to-no return.

    Maybe I can figure it out, but I have to say, I’m wondering whether a different approach wouldn’t have been better…

    1. M Smith – Install Power Pivot Utilities from The Italians over at SQLBI. It will really help you improve your performance. It will show you unused columns that might seem insignificant but add up. They also have a great video on DAX Studio, which is part of the PPUtilities and how to test the performance of your measures as well.

    2. Hi M Smith, Sorry to hear that your Power Pivot In Excel solution is taking a long time to refresh, load, and process. A few ideas: I typically don’t use calculated columns. I will do all data transformation work in Power Query, before loading the data to the data model. It’s also possible that you need to optimize your Power Query steps (if there are references/merges, that can cause slowness on the Power Query side). If the source files are stored on a SharePoint or other network location, it’s possible the slowness starts there? You could try to import the Excel data model into Power BI Desktop (PBID is free to download) – to see if you get faster performance of the same solution in the Power BI Desktop environment. Just some ideas… it’s hard to pinpoint the exact cause without taking a closer look but I suspect there is a root cause that could be resolved 😉

  4. By the way, I am missing one point. How do you make comments in your table to “stick” to the person? If there are changes in your data source – for example, new employee with ID 100000 added, that makes all the previous names move down by one row, but the comments stays and then appears to the next name, and not the one where it was originally made.

    1. Domantas,
      Did you enter the new employee in both the Employee table on ‘mock-up data’ tab and the Comments table on the ‘enter comments here!’ tab? I did both and when I refreshed, the new employee and comment appeared on the Report.

      Mt

    2. Yes, you are correct 🙂

      The TRICK would be to maintain an [Employee ID] sequence/ordering and insert any net new rows between the existing [Employee ID]’s and a fictitious/dummy ending [Employee ID].

      For example, if you were to have the initial starting set of data… for [Employee ID] and [Employee Name] as follows:

      ‘100001’, ‘Krissy’
      ‘100002’, ‘Ryan’
      ‘100003’, ‘Mat’
      ‘999999’, ‘fictitious/dummy – ending [Employee ID]’

      And then when loading the source data into the ‘Employee’ table within Power Query, you can add a Sort By [Employee ID] in ascending order step. This will maintain an order. Then, anytime net new [Employee Id]’s are added, they would sort prior to the fictitious/dummy [Employee ID] = ‘999999’.

      NOTE: Normally, we would NOT have any duplicate [Employee ID]’s and likely the [Employee ID] would be an AUTO INCREMENT primary key. Another option would be to use a [Insert Date] to maintain an expected ordering and the fictitious/dummy [Employee ID] would always be set to the most recent date/time so that it always sorts last.

      1. I see. But in reality this is a bit risky to count on sequence like that if you are not really in control of data source and numbering of employee ID, projects, or whatever that is. So I think the more reliable way is then to use query-generated table with IDs or names just as a source for drop down list to make a new table and then that table is used to make a new query and load to the model.

        1. This is the way to go. It takes a little more manual work, but the comments are manual anyway.

          I would add a lookup back to the downloaded Employee list that reminds you to delete if #N/A and add comment if missing, just in case an employee leaves. It is not really required since the relationship back in the model would filter out these comments in the final report anyway, but a good practice for a clean workspace.

          This row-level change has burned me in the past with this trick when the ID was not an auto-increment, or a status change included a lower ID than in the past.

          There is one other way to get a “First Uploaded” date through Get & Transform when you don’t have access to an employee start date:
          Stage your fact table in the final report workbook.
          Then, in a new transformations-only Excel workbook. you have to run a Left Anti-Join against your staged table to find the records you have not added before,
          Then take a Sysdate/now() + row(),
          Then append that query back to the original staged table,
          This appended final product is the source for the staged table back in the final report workbook.
          It’s overly complicated, and only should go over your process when your Data Validation List becomes too long to manage; IDK, a few dozen records?

          With this process, you just have to open the transformations workbook, refresh, open the stage workbook, refresh, and use Krissy’s trick.

          1. Hi Jonathan,

            That’s the spirit! Thanks for adding your additional thoughts here! There is always a way 🙂

            A database is usually the best source! And the solution built really depends on requirements and end goals. PowerPivot In Excel is great for creating Proof Of Concepts! But if you are working on a more large scale/enterprise project, likely we would choose a cleaner, more reliable solution 😉

  5. Great article Krissy! As usual I must say. This will come in handy. We haven’t made the push in PBI yet; however, PP has saved me on multiple occasions. Thanks for sharing 🙂

    1. Hi Sudhir!

      Thanks for stopping by and leaving a comment 🙂

      All of THIS… which is now collectively called the “Power Platform”, started with PowerPivot In Excel – about 10 years ago now! And while PowerPivot in Excel might not be as fancy as PBI, sometimes PowerPivot In Excel is just the right solution that you need… to be AMAZING! So just keep on, keeping on with PowerPivot In Excel… and maybe soon your organization will join in on total adoption of the Power Platform!

      Also, just announced this week… PBI is a Leader again for 2019 in Gartner’s Magic Quadrant for Analytics and BI Platforms!
      https://powerbi.microsoft.com/en-us/blog/microsoft-a-leader-in-gartners-magic-quadrant-for-analytics-and-bi-platforms-for-12-consecutive-years/

      Thanks again for sharing and let’s hope that Punxsutawney Phil was right with his prediction for an early spring, it’s chilly even here in Phoenix!

  6. hola Kristine! muchas gracias, Genial, pude replicarlo. Lo que mas me impreiono y que no sabia es que con DAX puedes agregar la misma al area de valores de la tabla dinámica sin importar si es texto. Simplemente Genial!. Muchas gracias! solo queda analizar la función lógica del si que utilizaste. 🙂

    1. Hi Osiel!

      You are very welcome 🙂

      Yes, I believe you are referring to… if you Right Click on the “ToTable_Employee” table in the example file and the worksheet called “enter comments here!”, you will see an option for Table and then you can click the Right Arrow to select “Edit DAX”. You can change the Command Type drop down from a Table to a DAX. And then any valid DAX query can be used to display data from the workbook’s Data Model.

      For example you could enter the DAX query: EVALUATE(ADDCOLUMNS( Employee, “All Rows”, COUNTROWS(Employee) ) )
      And return the ‘Employee’ table columns plus a new column with the total number of rows in the table.

      NOTE: No intellisense is available here to help build the DAX expression 🙂

  7. Hi Kristine, I attended the PowerPivotPro training in Phoenix last year and I have been learning ever since. I like your tie in with this example because almost every Power BI report that I make the users want to add their own data and this is a great example on how to do just that. Your example is much appreciated.

    Matthew

Leave a Comment or Question