August 31, 2015
by David Hager
Power Pivot and Power Query add-ins for Excel have revolutionized the way business intelligence can be done. Further, Microsoft has put a lot of effort into providing extensive online documentation for both of these BI tools. However, there is no direct connection between the add-ins and the online help files. There is a help icon on the Power Query ribbon but in my experience the link is always broken. Wouldn’t it be great if you could access Microsoft’s online documentation for Power Pivot and Power Query directly from within Excel?
Available here is the Power BI Help for Excel file. When you open it in Excel, the custom ribbon displays two buttons: one for DAX functions help and one for Power Query functions help. An userform is displayed which allows you to select the desired function category from a list. This action populates the functions in that category in another list. When one of the functions is selected, the desired Microsoft help documentation for that function is shown. Then, you can either read or print the web page. Please note that the help information available to be viewed is current to the creation date of this file. Any deletions/additions by Microsoft of these functions will not be automatically updated for this file. Further, any changes to the URLs to this online information by Microsoft will break the application. I hope that this will be beneficial to you in your Power BI learning curve.
IMPORTANT NOTE: DO NOT TRY TO OPEN FROM THE LINK BELOW. Instead, save it to your computer. Then, in Windows Explorer, change the file extension from .xlsx to .xls. After this action, you can open it normally in Excel.
Power BI Help for Excel
Among the many new BI features introduced with the recent release of the free version of the Microsoft Power BI desktop is the “new” trigonometric DAX functions such as SIN and COS. Of course, these functions have been around forever in Excel, but for some reason were not included in the first two major releases of Power Pivot. So, now they are available to the DAX language (and also in Excel 2016, if you have a spare computer handy to use in the preview version), and as such can be used to calculate straight-line distances between two points on the Earth as defined by their latitude and longitude coordinates. Also, in the formula, the constant PI()/180 is required a number of times, so the implementation of the new DAX variable comes in very handy here. In Power BI, if you add a new measure named PI_D180 as equal to PI()/180 (0.0174532925199433), you can use that measure in your DAX formula for distance, as shown below.
Distance = ACOS(SIN(Distance[Latitude_1]*[PI_DIV180])*SIN(Distance[Latitude_2]*[PI_DIV180])+COS(Distance[Latitude_1]*[PI_DIV180])*COS(Distance[Latitude_2]*[PI_DIV180])*COS((Distance[Longitude_2]*[PI_DIV180])-(Distance[Longitude_1]*[PI_DIV180])))*3959
Hope that you find this useful!
After I wrote about my opinion on Excel and Power BI, I decided to search trends at indeed.com related to Power BI and VBA. First, I put in the search terms Excel, VBA, and PowerPivot. Interestingly, the following shows an unexpected (to me) result.
Prior, to mid-July 2013, there were basically zero job postings based on those criteria. Then, the job postings grew significantly from that point to now. Obviously, this was long after the introduction of PowerPivot (~4 years), so what happened at this time? The following link is to a posting on the SQL Server blog on July 8, 2013.
This was the announcement of the introduction of Power BI. So, why would this announcement causing a “surge” of job postings base on searching for Excel, VBA, and PowerPivot? It is clear (to me) that there was(is) an expectation by the corporate world that Microsoft would implement a programming language (probably VBA) that would eventually be extended throughout PowerPivot (the DataModel object so far) and the rest of the Power BI offerings, creating the ability to build corporate, non-manual solutions using Excel as the hub.
I will be watching closely to see if this “indicator” gives any clues pertaining to the strengthening or weakening of this expectation.
If I were Microsoft, I would have a plan for the future for Excel and Power BI, and I would be telling everyone about it. This would be my plan:
1) There would be a basic form of Excel. It would NOT have any add-ins to enable. It would not have any enhancements beyond what exists today. It would work on any device. It would NOT include the Data Model. The new Office apps would work seamlessly with it. Without having any hard statistics, I am guessing that > 98% of Excel users outside of a work environment would be extremely happy with this product.
2) Office 365 – no changes – mainly for corporate use.
3) The new product would be Power Excel. Excel as it currently exists would be integrated with the new Power BI tools. The VBA object model would be expanded so that all of the BI “components” would be fully programmable/automatable inside SharePoint and with other Office products. It could toggle between SharePoint installed on corporate systems and SharePoint in the cloud. An app on any device could access and run Power Excel loaded on a server. The argument against VBA for enterprise applications has always been that the code was not secure and not compiled. Code in Power Excel files would be “protected” by SharePoint, thus making an outside programming solution unnecessary.
So, I would do this and make sure that my customers knew the details of my long-term plan so that they could make timely business decisions based on that information.
It’s probably obvious based on my comments here that I have no inside knowledge of what Microsoft intends to do in the future, but it is my hope that their vision is similar to this.