Power BI Help for Excel

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?

The Solution!

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

Measuring Distance in Power BI Desktop

Please contact me with any Excel Project work you may have. Written 10/28/2020

Email address: dhExcel1@gmail.com

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!