Acumatica, Power Query, and Power Pivot

I wasn’t able to spend much time on direct Acumatica learning this week, but I did get to entertain an Excel reporting idea that I think would be very powerful if I can get it to work.

First, a word about Power Pivot. I began using Power Pivot early last year and have become a big fan. At my company, we now do a lot of our reporting in Power Pivot. Recently, over the past few months, I have been attending the Cleveland Excel User Group which focusses on Power Pivot and is organized by a guy named Rob Collie. Rob is the most vocal Power Pivot voice on the planet. If you haven’t heard of Power Pivot, Rob has a nice overview on his blog. He also wrote a book on Power Pivot which does a great job of introducing the Power Pivot formula language (called DAX). Rob has a rare gift for writing about technical stuff in an entertaining way.

Microsoft is structuring their whole BI strategy around Power Pivot and they are adding components to the “Power” family. One of these components is Power Query. Now, Excel has had data connections for a while. You can use data connections by going to Data -> Get External Data on the Excel ribbon. Here is a screenshot from the latest version of Excel (Excel 2013).

Power Query is like a revamp of the Excel data connection feature. Actually, the end result of using Power Query is that an Excel data connection still gets created, but Power Query makes it MUCH easier while giving you A LOT more functionality. You can download Power Query for free here. When it installs, it will show up on the Excel ribbon like this:

There are some interesting options in the From Other Sources menu drop-down which include SharePoint, Email (Exchange only currently), Active Directory, and Facebook. Microsoft continues to add features to Power Query so I would expect this list to continue to grow.

Now, you might ask, “what does all this have to do with Acumatica?” Well, I’m glad you asked. In the most recent version of Acumatica (4.1), an Excel Connectivity feature was introduced. This feature allows you to export the results of an explore query to Excel without losing the live link back to Acumatica. During the export to Excel, Acumatica creates an Excel data connection which allows you to refresh Excel with live Acumatica data by simply clicking the Refresh All button on the Data ribbon.

Now you might ask, “what does this have to do with Power Query?” Great question! Power Query has the ability to connect to web pages and extract the data that is currently being displayed in tables on those web pages.

For example, you could connect to this Wikipedia page (http://en.wikipedia.org/wiki/List_of_United_States_cities_by_population) to bring the list of largest US cities into Excel using the From Web button in Power Query. Once you do this, you have created a link to the webpage. Now, if Wikipedia ever updates the data in the table on that webpage, a simple click of the Refresh All button in Excel will instantly grab the current contents of that table and bring them into Excel.

Why not just use the Excel Connectivity feature that Acumatica worked so hard to develop? What’s the big deal about Power Query? The reason to use the From Web button in Power Query rather than the old From Web button under Data -> Get External Data on the Excel ribbon is that Power Query provides much more functionality. You can remove columns, filter, group, insert calculated columns, etc. all before the data even comes into Excel. Power Query has it’s own language so you can do even more, but I haven’t looked into it too much yet. Also, the Excel Connectivity feature loads the Acumatica data into your Excel file, while Power Query gives you the option to load the data directly into the Power Pivot data model by checking the Load to Data Model box. I hope to write a future power about the benefit of bringing the data into Power Pivot rather than into a normal Excel worksheet.

The Excel Connectivity feature that was recently introduced in Acumatica version 4.1 utilizes the old Excel data connection From Web feature. When you open an Excel file that was exported from an explore query in Acumatica, you can go to Data -> Connections on the Excel ribbon to see the Excel data connection that Acumatica creates during the export. I tried exporting the Account Summary explore screen to Excel and the Data -> Connections screen looks like this:

Clicking the Properties… button brought me to this screen:

I then clicked the Definition tab…

and finally the Edit Query… button. A login prompt appears and I entered my Acumatica credentials. Don’t forget to enter both your username and the company that you want to connect to with an @ symbol in between like this:

Now, for some reason, I get a The webpage cannot be found error. However, I am able to refresh the Excel file so something must be working. This puzzles me, but more on that later.

I can click the disk icon in the upper right next to the Options button to save the data connection web query definition to my desktop as a .iqy file. I then open the file using Notepad to see the details of the data connection definition:

And this is where I get stuck. I think that I should be able to take the http://localhost/AcumaticaERP/Export/ExcelQuery.axd?companyid=F100%20Examination URL and paste it into the Power Query From Web feature like this:

He pointed out that res://ieframe.dll/navcancl.html# portion before the URL in the data connection definition file looks funny.

I also tried pasting the http://localhost/AcumaticaERP/Export/ExcelQuery.axd?companyid=F100%20Examination URL into my Chrome web browser, but I get a You can’t be here right now!!! error which seems similar to me to the 404 error that Power Query gives.

So, basically I’m stuck because I don’t understand the Excel Connectivity feature well enough and I’m wondering if someone out there can help me out.

My next step will be to see if I can get myself into the Acumatica forum to get help on this issue.

I definitely think that the combination of Acumatica, Power Query, and Power Pivot would be great for reporting.