Showing posts with label Power Query. Show all posts
Showing posts with label Power Query. Show all posts

Jul 19, 2017

Enable Power View in Excel 2016

We will soon learn how to create Business Intelligence reports using Power BI and integrate SharePoint & Power BI in this blog. Make sure you subscribe to get emails and updates on latest articles 😎
When I tried to create a Power View report using Excel 2016, I couldn't find where is the Power view option from Ribbon in Excel 2016
Did Microsoft remove it from normal ribbon view? ðŸ˜ĢI feel that this is an important option and it should be in normal ribbon view.
Though there are some articles out there, I want my readers to know the step by step guidelines to enable the Power View in Excel 2016 😀
1. Click Excel -> Blank work book -> File -> Customize Ribbon -> Choose from Commands drop down -> Select Commands not in the Ribbon -> look for "Insert a Power View Report" -> I am adding it in a group which I created called "Reports" as shown below:
2. Once you add this you should be able to see the Power View option in the Ribbon in the section whichever you added (here it is in Insert -> Reports) as shown below:
That's it, you should be able to add Power View reports from the ribbon as usual in a click.
Bonus for the readers of this article:
Where is the Power Query option in Excel 2016?
If you use Excel 2013, after enabling Power Query after installation you will see the option in a separate tab as shown below:
But in Excel 2016, Microsoft changed it as shown below:
ðŸ’ĄðŸ”— If you are interested reading about Power Query and Power Map, read this article
Happy reading! 😃

Aug 9, 2015

Power Query and Power Map in Excel 2013

As you all know, Microsoft is improving the BI capabilities with a lot of new features with exciting releases in the year 2015. Microsoft has introduced new add-ins called Power Query and Power Map for Excel 2013.
Believe me guys, this is really awesome add-ins which taken the Microsoft's BI capabilities one step ahead of all other BI competitors!
Let's try to create a power map by searching a public data using power query in Excel 2013 in this article.
Before trying a sample, let's understand what is power query and power map for excel 2013 and it's download links:
Power Query:
  • Microsoft Power Query for Excel (a data analysis tool) is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery, combine, refine, access and collaborate.
  • Power Query supports importing data from a wide range of Web sources, including scrapping tables from HTML pages, as well as importing data from Web APIs. Power Query supports XML, JSON and OData formats
  • It includes intuitive discovery features, a powerful query language, and a built-in ability to share your discoveries with others

Power Map:
  • Power Map provides interactive, 3D visualization with geospatial and temporal data
  • It enables information workers to discover and share new insights from data through
    • rich, 3D visualization on a globe with fluidic appearance & guided cinematic tours
    • seeing time-stamped data change over time (which you might not have seen in traditional 2D tables and charts) and to share it with others
    • you can create a video tour easily in a single click
    • Geo flow is another feature which shows how the
    • data changed over a period of time

Excel Add-in Links:
  1. Power Query for Excel 2013
  2. Power Map for Excel 2013
Creating a Power Map with GeoFlow:
Let's add a public data from internet by using Power Query - connecting data from the existing excel file.
I have downloaded the U.S. Operable Generating Units in the United States by State and Energy Source, 2011 from the link. Thank you for the data!
Open the excel 2013 -> Power Query tab -> Get External Data -> From Excel

This will open the query editor where you can select the data sets (this downloaded excel sheet has various tabs with different power sources). You can combine the data using Power Query data editor as well.
Let's select only "Coal" worksheet, delete the unwanted columns and create a date column using initial month and year (which is required for showing the growth of coal energy plants across U.S by county in power map) as shown below:

Now, click Insert Map -> Power Map option in the excel file -> Power Map will load the data source and it should automatically detect the geo-location fields from the dataset.
I have created two types of visualizations, one which is heat map visualization which will show the coal power generated in U.S. by state as shown below:
 The other one is coal power generated in U.S. by county with a time by using Initial Month of Operation column as shown below:
    

Power Map gives the options of creating video tour in a single click from the menu. Here is the video, I created from this sample:
Cool, isn't it?
Thanks for reading! Don't forgot to share this post and post your valuable comments which will make me write more.