Microsoft PowerQuery is a tool that helps users find and prepare data for analysis.

What Microsoft PowerQuery can do:

• PowerQuery can combine data from multiple Excel files into one table. This is an easy way to coalesce data charts to help expedite your internal analytics.
• With PowerQuery you can use dynamic parameter values that will give users the ability to adjust their query results without deep scripting. An example of something you can do with this, is to make adjustable query results with all your human resource data and have Excel formulas included to produce values.
• With PowerQuery you can scrape (extract) data from websites of your choosing to make unique analytical data and reports. Now imagine being able to access other organizations “failure data” so your organization can learn and benefit from what they have done in the past.
• With PowerQuery you can create a query from your Facebook likes that will render to an Excel chart. This is a good way to analyze your social media like’s data. This allows you to see how your qualitative data is perceived through your social media efforts.

Where Microsoft PowerQuery gets data sources from:

• Excel – CSV files
• Web pages
• XML files
• Text files
• SQL server databases
• MySQL databases
• Facebook
• Microsoft products & services
  • Microsoft Azure Marketplace
  • Microsoft Azure Table Storage
  • Microsoft Azure HDInsight
  • Microsoft Azure SQL Database
  • Microsoft Exchange
  • SharePoint List

PowerQuery in action: analyzing your non-profits Facebook data

When a charitable non-profit needed a way to better understand their contributors needs, they turned to their Facebook page that had the biggest conversation about what their contributors wanted to see most from them. They used Power Query to query their Facebook friends, analyze their Facebook feed, analyze feed activity by application and more.

IBIS Inc shows you how to do this Facebook/Power Query analysis here. [/tab_item] [tab_item title=”PowerPivot”]PowerPivot is a tool/window inside Excel that lets you perform powerful data analysis with huge amounts of data.

What Microsoft PowerPivot can do:

• With PowerPivot you can transform huge amounts of data with super fast speeds to information that will be used to analyze your organizations “mission impact”. A good example of this would be, your organization merging the whole year’s data from each individual that your organization helped, into a single report quickly.
• With PowerPivot you can create informative yet visually stunning data models. Imagine seeing the data of your efforts in a specific region or demographic throughout the year, in a visually appealing analytical model.
• PowerPivot allows power Excel users to manipulate data in ways that they were not able to before. In Power Pivot there is no limitation on rows of data. You can build a million row data sheet that includes your worldwide statistics.
• Once you have merged your huge data lists you can share your new found analysis easily online. So imagine being able to share millions of rows of data to a specific group in your organization quickly without having to use the resources of the IT pros.

PowerPivot in action: faster and fuller insights The MACPA (Maryland Association of CPA’s) was the first non-profit using the open standard of eXtensible Business Reporting Language (XBRL) to tag their internal financials. MACPA used this basic tagging to create a Business Intelligence dashboard. That has helped with efficiencies in internal financial reporting and better insight with their employees. MACPA accomplished this by using Microsoft Power Pivot tables that are linked directly to XBRL tagged data. This also allowed for them to have immediate distribution of financial data, user controlled graphic analysis and financial statements. [/tab_item] [tab_item title=”PowerView”]PowerView is an interactive canvas where you can display maps, tables and charts all in one window.

What Microsoft PowerView can do:

• With PowerView you are able to select a region of data in your charts and other components in your PowerView dashboard will respond to only show you data from that selected region. You can use this feature to visually investigate and edit segmented data.
• In PowerView you can geographically chart rows as a cluster on a map, and use city and state to generate locational data. This feature could be used to visually track your region by region success rate.

PowerView in action: Data Management and Analytics Platform

A major non-profit healthcare provider was lacking a data warehouse from where they could develop their business strategies. They relied on their analyst’s ability to seek out the correct information and data from different tools, which produced largely different sets of data. This left them with uncertainty in their abilities to find the right direction for the company. This also left newly hired analysts with the daunting task of learning a number of different platforms, tools, and processes, which in turn cost more for the healthcare provider.

These type of issues occur all the time and can be hard to deal with, even for huge organizations. The Plaster Group was able to build a data warehouse that integrated data from multiple sources and that gave them the ability to retrieve the data faster, more efficiently, more effectively and more reliably. PowerView was one of the tools used along with PowerPivot, which helped the healthcare provider see their data in a platform that was developed for the analysts to extract reliable data from.

What Microsoft PowerMap can do:

• • Plot millions of rows of 3D data on Bing maps from merged Excel tables. PowerMap also gives you the ability to see your data color coded on an interactive map.
  • Find links, connect dots and discover insights by viewing your data in PowerMap’s geographical space. You can take it to the next step and analyze your time stamped data to uncover hidden data.
  • In PowerMap you can build guided tours that will engage all your audiences simultaneously throughout the world. Power Map gives you the tools to create a video tour of your mapped visual data.

PowerMap in action: helping the business and consumer save on energy consumption

Josh Lich, Solutions Marketer at Opower, said that they used PowerMap as part of their newly launched service, that  visualized the capability of their new systems to deliver millions of personalized messages in short time frames. They also needed to visualize the impact that the energy saving messages had. The data that is collected can then be used to optimize communications over time to maximize the impact of the energy savings.