Learn how to unpivot static tables in Excel 2016

With the new Get & Transform capabilities in Excel 2016, you can now import, transform and combine data from different sources—thanks to the integrated Power Query technology in Excel. Today we are going to focus on one of the most useful capabilities that Get & Transform offers—the ability to unpivot to transform ANY table in your workbook to make it ready for powerful analysis using PivotTables and PivotCharts.

excel1 managed solution

excel2 managed solution

excel3 managed solution

Transform a static table into a PivotTable ready for deep analysis.

Let’s start with a common scenario. You have an Excel workbook that is dedicated for manual data entry to record the hours you put in for each relevant project:

excel4 managed solution

But analyzing this data is not easy and sometimes you wish you could easily create charts on this data, or even better—PivotCharts.

excel5 managed solution

Here is a common, yet challenging scenario—challenging, because it would take a lot of effort to get to the point you can gain insights from this data in the current format. Fortunately, through the new Get & Transform section in the Data tab of Excel 2016, the challenge is over.
Simply select any cell inside an existing table or range and in the Get & Transform section, click From Table.

excel6 managed solution

You will see a preview of your data inside the Query Editor window. From this window, you can start transforming your data in powerful, yet simple ways. Each change you make is recorded as a transformation step that is saved with your workbook. All the steps are kept as a sequence that can be performed again and again each time you refresh your data.
In the Query Editor, you will usually need the help of the Unpivot transformation to change your table into a format that can be used by PivotTable.
In the example below, I can select the first column that includes project names and click Unpivot Other Columns to transform my data from columns of Project Name / 2010 / 2011 / 2012… to the desired column structure of Project / Year / Duration.

excel7 managed solution

And here are the results in the Query Editor:

excel8 managed solution

I can now rename the columns to Project, Year and Duration inside the Query Editor and then load the data into a PivotTable or PivotChart.

Learn More:

If you are familiar with the Unpivot functionality and want to test your skills, read this post. Even simple tables such as a class schedule may require some cunning approach.

excel9 managed solution

The most common scenario includes nested tables with hierarchical date information such as years, quarters and months. Read here how to transform these tables.

excel10 managed solution

And if you wish to apply your magic on ANY nested table, read this post that will first show you how to unpivot three levels of nested rows and columns. It is so simple.

excel11 managed solution

On many occasions your data contains comma-separated values, like a table of events in which all participants are listed on a single column. You can learn here how to split such data and unpivot it.

excel12 managed solution

Finally, you can learn here how to transform and unpivot all these challenging tables in Power BI Desktop, and build amazing dashboards in Power BI.

excel13 managed solution

Try Office 365 to get the new Office 2016 apps!




thinkstockphotos-468332479-100632493-primary managed solution

Cloud adoption soars in regulated industries

By Kenneth Corbin as written on cio.com
New study from cloud security firm finds that government agencies and businesses are rapidly warming to Google Apps and Microsoft Office 365.
The past year-and-a-half has seen a steep increase in the rate of adoption of cloud computing applications, with some of the biggest movers found in the government and regulated industries.
That's according to a new study by the cloud security firm Bitglass, which analyzed traffic from some 130,000 organizations in North America and reported a 71 percent surge in cloud usage across the board in the verticals it evaluated.
Within the government sector, Bitglass reported a spike of more than 300 percent in the proportion of agencies that have moved to the cloud.
Quantifying the cloud
Rich Campagna, vice president of products at Bitglass, says the study seems to confirm what industry observers have seen anecdotally, quantifying "the sheer rate at which cloud adoption has taken off in the last year-and-a-half."
Importantly, Bitglass' analysis was not looking at all facets of the cloud. The firm evaluated only the use of public cloud applications, and, of that large subset, confined its analysis to the popular productivity suites Google Apps and Microsoft Office 365. Those applications, Campagna explained, are generally deployed on an enterprise-wide scale and are "key indicators" that an organization has committed to a "cloud-first strategy."
"The decision is made at the CIO level to move to Office 365 or to Google," he says. "What we wanted to find is what is the best indicator of an organization-wide adoption of cloud-based applications."
So that approach weeds out firms where a small pocket of employees might have incorporated some niche application into their workflow, but where the enterprise writ large continues to run processes like email and collaboration applications through a traditional, locally housed data center.
Among U.S. government agencies, 47 percent have adopted either Google Apps or Office 365, according to Bitglass' findings. A slightly larger proportion of agencies with more than 1,000 employees have made the move, but Bitglass is still seeing much of the cloud activity happening at the state and local level.
"They have a thin IT shop and this allows them to focus on value-added parts of their business, rather than on having to spend on a headcount on managing and maintaining applications that are not part of their core [mission]," Campagna says.
The most dramatic increase has come in the education sector, where, by Bitglass' estimate, 83 percent of organizations have adopted one of the cloud apps that it evaluated, up from 23 percent a year-and-a-half ago.
"That's easily explained by the incredibly compelling licensing that Microsoft and Google offer," Campagna says, referring to the free distribution those companies have been offering to education customers.
But less dramatic gains can still be observed in other regulated industries, such as healthcare, where 36 percent of the organizations Bitglass reviewed have moved toward an enterprise deployment of a cloud app, up from 8 percent last year.
In the financial sector, adoption increased to 37.5 percent from 9.5 last year, and would likely be higher still with more permissive industry regulations governing the use of IT.
Microsoft and Google cloud offerings promote adoption
Campagna sees a variety of factors at work in the rise in cloud adoption, and credits Microsoft with a successful sales strategy that has aggressively promoted Office 365 while also offering more flexibility in the licensing and renewal terms of its contracts. Google, too, has recently been stepping up its efforts to push its Apps suite in the enterprise, Campagna says.
But there is also a larger shift underway that has seen the security concerns about the cloud at least partially abate. Young companies like Bitglass, which describes itself as a "cloud access security broker," have been popping up with the express purpose of helping enterprises lock down their data in a public-cloud environment. And within IT circles, where CISOs might not have even considered going to a public cloud a few years ago, many have lately been softening their stance on the issue as they aim to reposition security as a driver of the organization's mission.
"Within that risk-averse subset of the IT department, I've seen the attitudes just shift quite a bit over the last couple years," Campagna says. "The mindset of security practitioners in general -- and there are still some holdouts -- has shifted from one of control to one of enablement over the last couple of years."

mobile virtual reality is the ghost of christmas past managed solution

Mobile Virtual Reality Is The Ghost Of Christmas Past

By Sunny Dhillon as written on TechCrunch.com
The worlds of gaming, sports, live music, commerce and adult entertainment are all primed for a revolutionary consumption shift with the advent of virtual reality (VR). Fantasy shall become reality; a new age of immersive entertainment lies just around the corner.
And just as the world progressed from radio to television, so too shall it do so with VR.
Lo and behold, we have the Samsung Gear VR as this Christmas’ must-have VR toy. Virtual reality, nestled under your tree, for the low, low price of only $99 (plus the $500 Samsung smartphone you need to actually power the Gear VR headset).
Whoa, whoa, hold up just a minute. We are a long ways off from the aforementioned VR Shangri-La with Samsung’s current VR headset. This “VR,” dubbed “mobile VR,” relative to true VR, is like thinking of your car phone (brick phone) relative to today’s powerful smartphone.
Gear VR allows us to sit front row at an NBA Warriors game or go backstage at a Nicki Minaj concert (thanks to content from NextVR and Jaunt) —  but, it is a passive experience. Being able to move your head around does not make for much interaction, and that’s all you can really do with the Gear VR.
More importantly though, I could reach my hand out to high-five Stephen Curry, but Curry would just run right past me. I’m invisible…the people in this virtual world can neither see nor hear me. It’s like I’m Ebenezer Scrooge, floating alongside the ghosts of Christmas past, present and future, through Charles Dickens’ ethereal journey in A Christmas Carol.
While I am excited to show my family many Gear VR experiences this Christmas, Christmas 2016 is the real holiday to watch. The big push in consumer marketing from Oculus, Sony and HTC will be coming then. We’ll have an install base of about 25-35 million Sony PlayStation 4s, and Sony’s VR headset, PlayStation VR, will have an avid and waiting fan base of gamers to buy it. Go to Best Buy, pick up a discounted PlayStation 4 and VR headset, plug it in and you’re good to go. Easy.
Marketing the headset as a peripheral plug ’n play device is easier than the uphill battle Oculus and HTC/Valve may have ahead. That said, Oculus, HTC and Sony have developed “true VR” —  i.e., games and content that allow the user to feel truly immersed, so far as they can interact with the world and other VR users around them  —  a stark difference to the largely passive experience offered by mobile VR to date.
While the VR experience from Oculus, Sony and HTC/Valve VR headsets will be leagues ahead of that provided by the Gear VR, such headsets warrant another Scrooge analogy at this point. It is unlikely that the miserly Ebenezer would ever consider paying the non-trivial $1,000 (or more) to buy an appropriate PC to run these advanced VR headsets.
The high price point (and more technical user requirements for a PC-based headset) presents an impediment to building and scaling an audience quickly. This means that any kind of ad-supported, free-to-play business models are a ways off; you need lots of eyeballs and players before you can make meaningful revenue as a content creator.
A rich, easy-to-monetize content ecosystem, like that provided by Google and Apple on mobile, is necessary for the flywheel of content creation and consumption to spin faster and faster. In the near term, we will only see VR content funding from the headset manufacturers themselves, and from brands who see VR as a way to look cool and edgy.
But for now, we have 360-degree videos. Yes, they provide a more immersive viewing experience than watching your living room TV. But you are stuck viewing the action from a fixed position. You can spin around on your office chair or stand up and turn around to get 360-degree views from where you’re seated, but try rolling forward. Things do not look quite like they should in real life, which is enough to shatter the illusion and immersion.
John Carmack, chief technical officer at Oculus (a partner of Samsung’s on the creation of the Gear VR device and software ecosystem), stated that “positional tracking” will be possible on future versions of the Gear VR. A chance to move around freely within replayed memories would be pretty cool.
For example, imagine having free range of motion inside a family video replay, walking around your child’s birthday party video and seeing the looks on everyone’s faces, enjoying these memories in a whole new way.
True social and environmental interaction in VR will come from Oculus, HTC/Valve and Sony. Oculus’ in-house content team has tight links to the world’s largest social network through its parent company, Facebook. Valve has deep gaming chops with hardcore PC gamers. And Sony has a long history of success in developing hit triple A games for its PlayStation console.
All of them stack up better than Samsung when it comes to making gamers happy. And gamers are usually some of the earliest adopters of new technology platforms, so it is important to win them over at VR’s launch. The Xbox did well because of Halo, and gamers await VR’s Halo equivalent with bated breath. Frankly, I don’t think it’s going to come from a Samsung platform.
Expanding VR to the masses will take experiential demos at Best Buy and Target. It will require ladies of The View, hosts of Sports Center and stars on YouTube and Snapchat to evangelize the hardware and platform.
More importantly, if Google’s failed augmented reality headset, Glass, serves as any kind of example, you can partner with fashionistas such as Diane von Furstenburg, have beautiful ad campaigns with beautiful models, et cetera, et cetera, but asking people to put something “techy” on their face can be very weird.
So, getting the hardware right is important. That means a design sense like that which Apple’s Jony Ive brings to technology products, making a device that is ergonomically and aesthetically beautiful. All the Madison Avenue ad dollars in the world cannot make VR cool if it follows Google Glass’ path.
For example, photos of old tech guys in the shower, wearing Glass, did not exactly help market the device beyond its nerdy, Mountain View roots. And we really need folks to think this is for everyone — not just geeks!
The good news is that mass-pleasing sports, music and games are all high on the roster for major VR headset manufacturers at launch. Oculus, HTC and Sony are funding top-tier content creators to stock digital store shelves with fun experiences, bundled and ready-to-go on 2016’s ship date.
Mobile VR is a great stepping stone to true VR, which, in itself, is a stepping stone to even more mass-pleasing platforms such as augmented reality (a discussion for another day).
In the meantime, enjoy your Gear VR this Christmas. Float through Victorian England with Ebenezer Scrooge and the ghosts of Christmas past, present and future. Just know that you, too, are a ghost in there, paralyzed from the neck down, and mute.
“These are but shadows of the things that have been,” said the Ghost. “They have no consciousness of us.” Wait until next year for that to change.
Source: http://techcrunch.com/2015/12/19/mobile-virtual-reality-is-the-ghost-of-christmas-past

Contact us Today!

Chat with an expert about your business’s technology needs.