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.
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:
But analyzing this data is not easy and sometimes you wish you could easily create charts on this data, or even better—PivotCharts.
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.
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.
And here are the results in the Query Editor:
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.
The most common scenario includes nested tables with hierarchical date information such as years, quarters and months. Read here how to transform these tables.
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.
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.
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.
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."
[vc_row][vc_column][vc_column_text]
Carlsberg Group
The Carlsberg Group produces and markets more than 500 brands. Following an unprecedented growth period, the company wanted to optimize global business operations, while remaining close to local markets—a new operational model it calls “GloCal.” Carlsberg is achieving this goal by using the productivity and collaboration tools in Microsoft Office 365 to connect with colleagues around the world.
J. C. Jacobsen founded his family-owned brewery more than 160 years ago to serve the Danish market. It wasn’t until 1990 that the Carlsberg Group began a period of acquisitions and global growth that today represents its biggest success—and its biggest challenge. Managing more than 500 beer brands that cater to diverse tastes and cultures around the world, the company needed to realign its organizational structure and corporate culture in a way that would help it succeed in a highly competitive global market, while staying true to loyal local customers.
Need for Global Collaboration The Carlsberg Group is organizing itself around a new operational model—called GloCal—designed to find the right balance between working together at a global level and allowing its many regional beer brands to flourish. “If we are going to be successful as a GloCal organization, we have to be more cohesive in how we do business across the world. And for that to happen, we need to enable our employees to communicate and collaborate seamlessly, no matter where they are,” says Etienne Dock, Vice President of IT Architecture and Sourcing, Carlsberg Business Solutions. “One way we are helping our employees work better together is by deploying Microsoft Office 365.”
When choosing productivity and collaboration tools, the Carlsberg Group also evaluated Google Apps for Business, but employees wanted to work in the familiar Office user interface and the IT group valued the level of integration built into the Microsoft cloud-based communication, collaboration, and social networking services. The company also felt that a Microsoft offering met its security and availability requirements. “Coming from a position where we had disjointed email services, inconsistent web conferencing tools, and isolated web portals for different countries, integration was important to us,” says Dock. “The more we grew, the more obvious was the need for a single, consistent, reliable collaboration platform.”
Optimizing Operations with Global Supply Chain
The Carlsberg Group is minimizing the significant costs of delivering beer to local markets and optimizing brewing capacity at its more than 80 breweries around the world thanks to the formation of a new subsidiary in Switzerland, the Carlsberg Supply Company (CSC) that generates global efficiencies and cost savings across the company’s vast supply chain.
“The formation of CSC points to our growing, global business agenda. Office 365 was a key IT initiative that we put in place to support this agenda,” says Dock. “Beer is heavy, so we don’t want to transport it for long distances. Breweries are capital intensive, so we don’t want to build too many. The CSC will derive huge business value from using Office 365 as a global collaboration tool to interact with our global markets and exchange the information we need to fine-tune the balance between these variables to optimize our operations and save money.”
The Carlsberg Group worked with Microsoft Services Consulting to validate the company’s overall architecture, assess the readiness of its IT infrastructure for deployment, and support the company through the migration. To promote videoconferencing usage, the Carlsberg Group posted a series of educational videos on its intranet.
Accelerating Business with Integrated Tools
The company is accelerating the pace of business through impromptu and planned videoconferences that are quickly being adopted by employees at corporate headquarters and in local offices. A recent videoconference attended by more than 100 supply chain leaders, the CEO of the Carlsberg Group, and several members of the board successfully communicated the CSC three-year strategic plan to a global audience in under two hours. “Not only did we broadcast our strategy to a global audience, but we gained feedback from participants through a Q and A session and a poll asking if this was a useful way to share information. The majority of responses were favorable,” says Werner K. Budek, PhD, Vice President, Strategy at Carlsberg Supply Company. “It would have taken us three weeks to travel around the world to meet and greet everyone on that call. Sharing knowledge online is one way we are improving efficiency and boosting business agility.”
Personal productivity is also on the rise, thanks to anytime, anywhere access to documents that are automatically synced between different devices. When employees have everything they need at their fingertips, the pace of business accelerates. “I’m on the road frequently and I carry at least two phones, one laptop, and a tablet,” says David Fang, Corporate Affairs Vice President, Asia, at the Carlsberg Group. “Now I can access all my email and documents with any of my devices, so I can get work done quickly without worrying about technical issues.”
“We are currently testing online data storage and anticipate that when we roll it out, users will benefit from secure access to their documents anywhere they have Internet connectivity,” adds Dock.
When the Carlsberg Group quietly launched its new enterprise social network, employees in the Product Innovation Group discovered it themselves and began sharing ideas to innovate.
“People are using Yammer to discuss innovative packaging concepts, more inviting store displays, and new technologies for beer cans,” says Anders Munck, Enterprise Architect at the Carlsberg Group. “Compared with an official innovation system, it’s so easy to get involved. Already we are seeing ideas flowing all over the company.”
Local Presence, Global Family
The Carlsberg Group is benefiting from increased knowledge of regional markets because sales representatives who are out all day meeting customers at bars, pubs, and restaurants can use their smartphones to connect and share information with colleagues and management directly from the field. This flow of information enriches the company’s understanding of regional markets. “The local flavor of our beer is a huge part of our value, so the more we learn about our customers in different parts of the world, the better we can satisfy their unique tastes,” says Dock.
Today, the Carlsberg Group is much closer to becoming a true GloCal organization. With user-friendly tools and an enterprise social networking platform that opens up the lines of communication via any device anywhere, the company is breaking down business silos to achieve what the Carlsberg Group defines as a true “family of brands in a world of diversity.”
“Because employees around the world are working and communicating better together, we can satisfy customers at less cost,” says Dock. “That’s because we gain the efficiencies of business done on a global scale, while staying close to our local markets. Thanks to Office 365, we’re becoming a true GloCal organization.”
The Technologies Involved
Most employees at the Carlsberg Group use the Microsoft Office 365 Enterprise E3 plan. This plan includes Office 365 ProPlus, the familiar Office desktop suite, which employees can download on more than five devices and work productively with synced files on all of them. Employees use Microsoft Exchange Online for email and calendaring and Microsoft Lync Online to connect via web conferencing and instant messaging. Virtual teams are beginning to adopt Microsoft SharePoint Online to collaborate on projects, and employees are adopting the Yammer enterprise social networking platform for messaging and collaboration on product innovation. And when the company deploys OneDrive for Business for online document storage, employees will be able to access the latest version of their files on any device anywhere.
Read customer success stories to learn how Managed Solution helps businesses implement technology productivity solutions.
[/vc_column_text][/vc_column][/vc_row]
Discover how #Skype4B simplifies communications in the cloud.
This video illustrates how Office 365 and Skype for Business simplify all of your meeting essentials to just one relationship, one work stream, in the cloud and at a reduced cost.
The tech giant is pushing Skype for Business, now part of Office 365, as an alternative to separate services for video or audio conferences.
The line that separates Microsoft’s cloud collaboration suite, Office 365, from its corporate communications services, marketed under Skype for Business, is getting blurrier.
On Dec. 1 the tech giant will officially unleash new conferencing, meeting, and cloud telephony options for Office 365 that are meant to consolidate and replace the separate services many businesses use to host audio and/or video conferences.
“Most of our customers have more than one of these [services], they’re putting a lot of money into them, but aren’t satisfied,” said John Case, corporate vice president for Microsoft Office. “This turns Office 365 into a modern communications platform.”
For example, the new capabilities will allow companies to set up and initiate all-hands meetings for up to 10,000 attendees—that can be attended via Web browser or mobile device—in a matter of minutes, Case told Fortune. Additionally, any questions that arise during the large broadcast can be submitted via the company’s Yammer messaging application.
The new services aren’t exclusionary: Teams can also create conferences that connect with traditional phones using the public switched telephone network (PSTN). “You can dial in from pretty much anywhere,” Case said. Meanwhile, Microsoft MSFT 0.81% is also pushing Skype for Business as nothing less than a replacement for existing corporate private branch exchange (PBX) systems—and it’s offering “Fast Track” funding to help businesses make the switch. The Office 365 pricing plan, which includes all of the above features, costs $35 per user per month, plus another $24 per user for international and domestic calling plans, according to a Microsoft pricing sheet.
Competitors Facebook FB 0.87% and Google GOOG -0.61% have also busy adding video-calling features to their platforms in recent years—to compete with both Skype and Apple’s FaceTime app—but Microsoft has been far more aggressive about embedding these options into its existing business applications. Over the coming months, you can expect Microsoft to forge relationships that embed conferencing and calling features into applications from other software companies.
For example, in the future, customer service agents using software from call-center company Genesys may be able to initiate support or telemarketing conversations by simply clicking on contact information within a customer’s record. Right now, that same person might be forced to jump back and forth between several systems to track the conversation and make changes, Case said.
The consumer-grade Skype service boasts almost 300 million registered users. Microsoft bought the company in May 2011 for $8.5 billion, and promptly began merging the cloud-delivered communications service with its existing Lync communications products.
[vc_row][vc_column][vc_column_text]
If you’re on a budget, use Excel to plan out how much you can afford to spend on each person this holiday season.
Try Office 365 to get the new Office 2016 apps including Excel!
