Guest Post by Avichal Singh
Intro from Rob: Never fear, last week’s series is still slated for completion, and in a special way. Watch this space on Thursday for some fireworks. For now, please enjoy Avi’s thoughts on the new forecasting component of Power View / Power BI.
PASS Business Analytics conference saw the announcement of a pretty cool Power View feature: Forecasting. I felt lucky to have been there and also to have had the opportunity to attend both of Rob Collie’s sessions (Data Revolution, Industrial Strength Excel). The Data Revolution session, I must say, was unlike anything I expected. No DAX formulas, no bullet points; just a path to data nirvana
The Power View forecasting feature was cool enough that I just had to play with it! I wanted to try it out with a few real world data sets. I ended up using Climate Data and Stock Market performance.
– First a quick look at the Power View forecasting functionality
– Then I show you how I built the files using Power Query (The more I use that tool the more I like it)
You can find the link to the finished Excel file here. You can also watch me walk through the whole process in the video below:
Power View Forecasting in a Nutshell
In the ‘cloud first’ spirit that Microsoft has been following, the forecasting feature is only available in the online Power BI site (See microsoft.com/powerbi for more and to sign up for a free trial). To enable the forecasting feature, after opening your file on the Power BI site, you need to switch to the HTML5 mode by clicking on the icon at bottom right.
|Click on this icon to enable the HTML5 mode with forecasting functionality|
For more details on how Forecasting works see announcement by Power BI team.
Using Power Query to build the file
Data Sources Used
· Northern Hemisphere Temperature Anomalies
Temperature Anomalies = deviations from the corresponding 1951-1980 means
See: Why we use temperature anomalies and not absolute temperatures?
· Disaster Affected: Gapminder by Hans Rosling
For Subcategory=Disasters, I downloaded affected annual numbers for: drought, epidemic, extreme temperature, flood, storm and tsunami.
Check out Hans Rosling’s epic TED talk: The best stats you’ve ever seen
No, the Stock Market dataset is not related with the previous two ; I just wanted to try out something different.
This is an ugly file! Without Power Query I would probably never even consider pulling it in to Power Pivot. But Power Query actually makes the whole process fun!
|Header rows need to be removed|
|Columns need to be split and Unpivoted (moved to rows)|
|Extraneous columns need to be removed|
|Extraneous rows need to be removed|
Below are the steps I applied to this unruly file and pulled it into Power Pivot; watch the video for detailed walkthrough.
Quick Note on Load Settings in the Power Query Pane:-
See main steps below for loading this dataset; watch the video for detailed walkthrough.
|Import first data set (Drought Affected)
Then Duplicate that to make copies
|In the Power Query Editor go to
View tab > Advanced Editor
|Here I made simple changes to the script to get different datasets
Learn more about Power Query Formula Language (also known as “M”)
|Once I had all datasets saved as queries…|
|I Appended them together and loaded into the Power Pivot model|
Stock Market Historical Quotes
This was fairly straightforward; I followed same approach as above to compile the Queries and save them separately before I append it and load it into Power Pivot model.
Temperature anomaly is certainly trending up . But look at the shaded area showing a Confidence Interval of 1σ which covers ~68% of possible values. It covers a wide swath, indicating that a lowering of temperature anomalies is perhaps a possibility. There is hope for humanity still!
Also there certainly seems some correlation between the increased temperature anomalies and people affected by natural disasters; both ticking up in the early and late 1980s.
Given all the amazing work Microsoft Power BI team has been doing by building features like Forecasting, it’s no surprise that the forecast for MSFT stock is looking good! Oh, I better put the disclaimer : Information is provided “as is” and solely for informational purposes, not for trading purposes or advice.
The opinions and views expressed in this post are those of the author and do not necessarily state or reflect those of Microsoft