Posts

How to Export from Power BI to Excel – An Overview of Methods

Whenever we talk about data tools we always joke about the time-to-Excel. It stands for the amount of time a user will be in the tool before they choose to export to a more familiar software like Excel to customize the data exactly the way they want. This happens more often than many would like to admit, and it’s hardly something to be shy about. If you’re wondering how to export from Power BI to Excel we’ve got a handy chart for you to compare the available methods. Below, I explain how to get from different parts of the Power BI ecosystem back to Excel when preferred.

Power BI, as a tool, is primarily meant for data exploration and analysis within the tool – that is, Power BI Desktop, the Power BI Service (powerbi.com), or other parts of the Power BI ecosystem. Exporting for further analysis is not the primary goal of the platform.

Luckily for those who would much rather work with the familiar worksheet view, there are ways to manipulate data from Power BI in Excel. I’ve listed them below in order from the easiest and most complete to the most difficult and least complete.

Exporting from Power BI to Excel

MethodRequirementsOutput FormatRow LimitOther LimitationsPremium/
Embedded
Pro
Connect to Data Mart/Warehouse Directly
(No Power BI)
View Access to Data Mart/WarehouseAny (as allowed by tool)NoneNoneNoNo
Export from Paginated Reports (Desktop)Paginated Report Builder, View Access to Data Source or Build Permission to Shared PBI DatasetCSV, Excel, Word, PowerPoint, PDF, XML, MHTMLNoneNoneNoNo
Export using DAX StudioView Access to Data Mart/WarehouseCSV, SQL ServerNoneLocal HardwareNoNo
Connect to Data Mart/Warehouse with Excel Power QueryView Access to Data Mart/WarehouseExcelExcel – 1,048,576None*NoNo
Analyze in Excel from Power BI ServiceView Access to Workspace or AppExcelExcel – 1,048,576NoneNoMaybe*
Export from Paginated Reports (Published to Service)View Access to Workspace or AppCSV, Excel, Word, PowerPoint, PDF, XML, MHTMLNoneUnable to change table structure from how report was builtYesMaybe*
Copy from Power BI Desktop Data View (Table View) to ExcelPower BI Desktop, View Access to Data Source or Build Permission to Share PBI DatasetExcelExcel – 1,048,576Local HardwareNoNo
Export from Power BI Service VisualView Access to Workspace or AppExcel or CSVExcel – 150k
CSV – 30k
Limited by Visual Summarization*NoMaybe*
Export from Power BI Desktop VisualPower BI Desktop, View Access to Data Source or Build Permission to Share PBI DatasetExcelExcel – 30kLimited by Visual SummarizationNoMaybe*

*Please note that the values above are for Import reports. DirectQuery reports have slightly different export limits when limited.

Further Reading:

Excel Power Query Limitations – https://support.office.com/en-us/article/power-query-specifications-and-limits-5fb2807c-1b16-4257-aa5b-6793f051a9f4

Paginated Report Limitations – https://docs.microsoft.com/en-us/power-bi/paginated-reports/paginated-reports-report-builder-power-bi#limitations-and-considerations

Paginated Report FAQs – https://docs.microsoft.com/en-us/power-bi/paginated-reports/paginated-reports-faq

Helpful Links:

Power BI Desktop Download: https://www.microsoft.com/en-us/download/details.aspx?id=58494

Power BI Paginated Report Download: https://www.microsoft.com/en-us/download/details.aspx?id=58158

Excel Power Query Download: https://www.microsoft.com/en-us/download/details.aspx?id=39379

 

Information Barriers for Office 365: Enhancing Control over Communications

Employees are the source of corporate information. They constantly create documents and data records, generating gigabytes of corporate information every single day. And this corporate information needs to be protectedMany organizations trust Office 365 and SharePoint Online as a secure platform to run their corporate intranet. This puts some pressure on Office 365 to ensure that sensitive corporate information is not just securely stored, but also that it complies with stringent regulations and laws (like FINRA). The new Information Barriers policies for Office 365 (Microsoft Teams and soon SharePoint) help administrators achieve just that.

Information Protection in Office 365

Before we dive into the new Information Barriers feature in Office 365, let’s first spend a few moments on Information Protection and examine what it means for organizationsThe obvious choice to protect information within a corporate intranet is using access permissions. With access permissions, organizations can decide which user has access to which siteFor example, in an organization with a legal department, only a narrow group of users will need access to the sites of this department 

Besides access permissions, Office 365 and SharePoint Online provide additional options to protect sensitive corporate information such as Retention PoliciesData Loss PreventionAzure Information Protection, and Compliance Sensitivity LabelsThese features ensure that corporate information stays protected within the organization and control access to specific documents. 

When they are well configured, organizations are in an excellent position to keep data and documents safe. However, for some organizations, that might not be enough. 

Limitations to information security

Although security options provide a significant level of protection, there are some limitations you need to be aware ofThe most apparent threat to Information Protection is what I like to call the human factor. For instance, there is no technical way to protect corporate information if employees meet outside of the organization and, for example, verbally share sensitive information.  

And even when it is available, technology comes with some limitations too.  

For instance, a user who does not have access to specific site may obtain sensitive files from a user who does. And while a sharing invite does not provide access to the entire site, the user who receives an invite can open, download or potentially edit the document. This means that even though strict compliance policies regarding access permissions are in place, the SharePoint Sharing mechanism can be used to bypass those policies quite effortlesslyOf course, external sharing can be disabled in SharePoint Online, but since SharePoint was built around sharing information initially, internal sharing can’t be disabled. This is just one example of how corporate compliance policies can be sidestepped 

Another example is online chats and remote meetings initiated via Microsoft Teams. Even if a user does not have access to a particular site, this user may still be invited to join the team chat, thus getting information that shouldn’t be shared with anyone else outside of the defined team. 

Finally, access permissions can sometimes be accidentally given to the wrong person. This happens surprisingly often when a couple of people in the organization share the same name. Mistakes are a part of human nature, and sometimes, they are hard to avoid.  

Need for additional layers of security 

For many companies, these restrictions are not necessarily critical. If there are trust and appropriate employee education about the importance of the company’s compliance policies, companies can do a lot to protect their sensitive information. However, some organizations need to follow stringenCompliance and Security stipulations and laws. For those, being able to bypass policies by just sharing a document is a severe threat.  

Information Barriers Policies in Office 365

This is when the new Information Barriers come into play. With Information Barriers, organizations can encapsulate or separate specific corporate entities from the rest of the organization, even though all corporate entities share the same corporate intranet and technically, the same Office 365 tenant. 

Let’s see what this means. At the beginning of this blog post, I explained why even strict access permissions might not be enough for some organizations. Sharing, the feature that makes SharePoint great, can be used as a loophole to bypass policies.  

Information Barriers policies in SharePoint and OneDrive for Business go much furtherSpecifically, because they prevent users from sharing documents with others outside of a specific corporate entity. But that’s not all. Users of an encapsulated corporate entity won’t even be able to lookup users of a different department.  

This is because Information Barriers in SharePoint and OneDrive for Business acts as a separate (logical) tenant, even though the organization technically uses just a single tenant. The following screenshot shows an example of how this might look like in SharePoint: 

Limiting file sharing in SharePoint with Information Barriers Policies

 

Configuring Information Barriers Policies in Office 365

Now that we know how Information Barriers will work in SharePoint and OneDrive for Business, let’s see how these Information Barrier Policies can be configured.  

Information Barriers rely on user account attributes defined in Azure Active Directory. These attributes can include information like department, job title, location, and team name. Organizations can create segments based on these user account attributes. Those segments can be entire corporate entities, but also groups of users (like all users with the job title ‘Financial Advisor’). The concept of segments is very flexible as it is based on user account attributes. User account attributes are defined in Azure Active Directory, but segments will be defined in the Office 365 Security & Compliance Center. With segments defined, Information Barrier policies can be created based on two kinds of policies. Companies can create policies to Block access or to Allow access. There is a significant limitation, though: a user can only be part of one (1) segment (as of December 2019), and the segments must not overlap. 

Creating segments and Information Barrier policies require thoughtful and thorough planning as Information Barriers are rigorous policies, which have a massive impact on users and the entire organization. Microsoft provides an Excel-based workbook, which organizations can use to create and configure policies. The workbook also offers support for managing policies via PowerShell. You can download the workbook here  

The following two screenshots show how you can create segments and policies in the Office 365 Security and Compliance Center: 

Create segments in Office 365 Security and Compliance Center

Blocked File Collaboration based on Information Barriers settings

Information Barriers in Microsoft Teams 

In the previous section of this blog post, we looked at options to secure SharePoint and OneDrive for Business. Still, more applications in Office 365 allow communication and collaboration within users of different corporate entities – like Microsoft Teams. Since Microsoft Teams uses SharePoint Online technology under the hood, some protection already exists therebut it may not be enough. Information Barriers in Microsoft Teams offer added security. 

Microsoft Teams allows users to communicate with each otherBut in a strictly regulated environment, this kind of electronic communication needs to be secured by policies. Information Barriers in Microsoft Teams can be used to prevent team members from communicating with other teams and sharing documents. Also, Information Barriers can be used to encapsulate a team in Microsoft Teams entirely, restricting communication to that team only. All communicationincluding sharing with anyone outside of that team, can be blocked.  

But, there’s more you can do to secure and monitor information exchange in Microsoft Teams. Information Barrier policies can also be applied to the following: 

  • Adding members to a team 
  • Requesting a new chat 
  • Invited user to join a meeting 
  • During screen-sharing 
  • During VOIP calls 
  • Guest access in teams (includes guest users) 

The next screenshot shows how Information Barrier policies are activated in Microsoft Teams: 

Activating Information Barriers policies in Microsoft Teams

User Experience 

Information Barriers can be very restrictive, and organizations should be fully transparent regarding the implementation of Information Barriers in Office 365. The entire staff (including new hires) need to know about the existence of Information Barrier policies and how these policies will affect their daily business. Educational workshops, recorded training sessions and tailored communication are an absolute must. Regarding the user experience, there are many areas where Information Barriers affect the regular usage of SharePoint. Here are some examples: 

  • Users cannot see blocked users in the People tab and People Picker. 
  • Posts of blocked users won’t show up in the activity tab. 
  • Blocked users won’t show up on the org chart and the list of suggested contacts. 

Technically, Information Barriers will affect employees when they are collaborating and trying to get in touch with each otherBasically, mostif not all, of the collaboration and information sharing possibilities in SharePoint, OneDrive for Business and Microsoft Teams will be affected or restricted by Information BarriersA full list of what users will experience if another user is blocked by Information Barrier policies can be found here. The following screenshots show how this looks like in Microsoft Teams. The left screenshot shows the user experience when trying to add blocked user to a channel, the right screenshot shows the user experience if you try to send a message to a blocked user directly: 

Couldn't add member to team due to Information Barriers policy

Requirements and Roadmap 

To be able to use Information Barriers, organizations require an Office 365 E5 license. The following roles can create information Barrier policies: 

  • Global Administrator 
  • Compliance Administrator 
  • Information Barrier Compliance Management (new role) 

My recommendation is to split administrative tasks in Office 365 to multiple roles. Each role in Office 365 (including the new Global Reader role) is supposed to be used for a specific task only. This is done to provide an additional layer of security to sensitive administrative activities in Office 365. I know that many organizations utilize the Global Administrator role for all configuration tasks, but that is definitely not best-practice and it threatens security. Organizations should associate the Compliance Administrator role or the IB Compliance Management role to specific users and use only those roles to manage Information Barrier policies. 

Information Barriers are rolling out now, but they will only be available in Microsoft Teams for now (as of January 2020)Information Barriers for SharePoint and OneDrive for Business are still in development and are expected to roll out later in Q1/2020. If you are interested, there is a Preview Program you can subscribe to. 

Conclusion 

Information Barriers are a great addition to the existing Security and Compliance policies in Office 365. While they do contradict the original idea behind SharePoint (after all, it is called SharePoint), they come in response to a growing demand for advanced security policies. Once Information Barriers are fully supported in Office 365, they will be welcomed by organizations that need to follow strict Security and Compliance regulations. 

Organizations should not underestimate the implications of Information Barriers as they will drastically impact the daily tasks of the entire staff. Reason enough to start planning now – even though Information Barriers won’t be available in SharePoint and OneDrive for Business until later in Q1/2020. Planning means not just thinking about potential policies. Implementing Information Barriers comes with an entire process of activities – beginning with checking potential legal regulationsThe implementation process also includes roles and responsibilities, identifying segments, communication to the staff, reviewing existing business processes, defining policies, training, user adoption, change management, etc.  

If your organization needs to implement Information Barrier policies, I recommend starting now to ensure, you have enough time to carefully and thoughtfully plan the entire implementation as Information Barriers will change how your organization is working today 

At DevFacto we are already working on guidelines, best practices, and recommendations to support our customers regarding Information Barriers in Office 365. Want to know more about ensuring compliance with Microsoft tools? Reach out to us. 

References 

Information Barriers Preview thread 

Information Barriers in Microsoft Teams 

Information Barrier Overview 

Define Information Barriers Policies 

User Adoption Matters – How to succeed with your Office 365 rollout 

User Adoption Matters – How to Succeed with Your Office 365 Rollout 

We all know that technology is evolving fast. In fact, new technology has never been released as frequently as it is now, and this couldn’t be more true for Office 365 and SharePoint Online. Since Microsoft came out with Office 365 in 2011, many organizations moved to the cloud platform recognizing the benefits for their business and their employees. In the time since, Microsoft released many updates and many additional applications to improve the usability of its cloud platform.

But for every light, there must be a shadow. While Microsoft works tirelessly to continue improving its Office 365 platform, organizations often struggle to keep up with Microsoft’s pace. A prominent example is the fast update-cycle of Office 365, which can cause issues when organizations introduce new technology to their employees and plan accompanying activities like user adoption and change management. For some organizations, planning and executing user adoption campaigns can take some time, and while the user adoption team is still working on the campaigns, newer features may already be added by Microsoft.

For most organizations, the step towards the cloud (Office 365 and SharePoint Online) is a significant step not only for the organization, but the entire staff as well. As a consultant, I realize this every time I assist organizations with migrating from file-shares to SharePoint Online. Quite often, organizations manage this transformation by augmenting a SharePoint Online rollout with user training. Unfortunately, activities that are proven to drive user adoption, such as internal user adoption campaigns and proper change management, sometimes take the backseat – much to the detriment of the staff. In this article, I’ll discuss how including them in your Office 365 or SharePoint Online rollout can drastically increase the user adoption rate.

Why Office 365 User Adoption Matters?

First, let’s have a look at why proper and tailored user adoption activities matter to every organization implementing Office 365 or SharePoint Online. They:

  • Protect organizational ROI. Rolling out a new technology not only takes effort, but also costs money. Most enterprises justify the project spend with a projected ROI (Return on invest). Because technological investment should generally lead to lowered operational costs and increased efficiency, it is in the vital interest of all organizations to ensure that new technology is used by the entire staff as expected.
  • Benefit the Employees. Modern technology should not only provide benefits to the organization, but also improve the daily work of employees. Unfortunately, this is where the problems begin. Not all employees embrace changes to their daily routines, even at a promise of easing the workload. While tech-savvy ones are eager to try out the new tools and updates, others may remain reluctant or hesitant to change. This is where user adoption campaigns really matter. Organizations can run them to ensure that the new technology is used by the entire staff as expected by the organization – without making employees feel imposed!

The way towards organization-wide user adoption can differ between organizations. When talking to executives and stakeholders about user adoption and change management, I often realize that many organizations think they provided proper user adoption activities by offering tailored training sessions and emailing corporate announcements regarding the new technology. But in most cases, that is far too little to ensure that the new tool is used as expected. User adoption is much more than just training and announcements. It is a long-term activity (or an internal project – if you want to put it that way), which requires extensive planning.

Build a Office 365 User Adoption Team

It all starts with establishing a user adoption team as any user adoption campaign needs to be handled like an internal project. Here is a high-level list of roles within a user-adoption team, which should be adjusted based on your corporate culture:

  • Adoption Team Lead: Responsible for managing the user-adoption team, planning tasks and scheduling meetings.
  • Moderator(s): Responsible for planning and performing user-adoption campaigns. In most cases, it makes sense to involve Power Users or Key Users. I recommend involving professional moderators or at least employees who are used to public speaking (or have stage experience).
  • Communicator(s): Responsible for all communication around the user-adoption activities. I recommend a professional communicator as the style of communication needs to be engaging, enthralling and carefully tailored to the target audience.
  • Technical Expert(s): Responsible for technical support, knowledge transfer and measurement of the identified success factors (as explained later). Often, Office 365 admins take over this role.
  • Trainer(s): Responsible for delivering accompanying training sessions.
  • Executive(s) and Stakeholder(s): Part of the team to highlight the importance of user adoption and to ensure that organizational interests are considered.
  • Corporate Governance Committee: Although the Governance Committee does not need to take over an active role, keeping them up to date on planning and the current state allows them to chime in if there are any corporate policies which need to be considered.

Establish Goals

If we look at the user adoption team, it becomes clear that a user adoption campaign isn’t a one-time activity like training. Rather, it’s an ongoing process, that strives to accomplish several goals:

  • Introduce a new technology to employees in a way which is tailored to their skill set and technical abilities.
  • Provide individual examples on how to use the introduced technology based on roles and responsibilities.
  • Focus on the benefits that employees can achieve by using the new technology, in other words: show how this new technology can be used to meet individual goals.
  • Explain how the technology fits corporate strategy.

Out of all these, the most important one is certainly focusing on benefits that matter to employees, as it is likely that not all employees will embrace changes to their daily routines. From an organizational standpoint, user adoption activities need to ensure that new technology is used as planned. From the user’s perspective, new technology will affect the daily business and most employees are primarily interested in “what is in it for me,” rather than what are the benefits the organization is hoping to achieve. Basically, this discrepancy is the reason, why user-adoption campaigns are crucial.

So, What Can You Do to Drive Office 365 User Adoption?

Here are some proven tactics:

  • Schedule events to introduce the new technology to all employees. These events should offer a high-level overview of the solution that explains the intended use of the technology and its place within existing applications and procedures.
  • Schedule meetings with individual departments to showcase how employees of particular departments will benefit from using the new technology. Since requirements vary between departments, user adoption activities should be designed to address needs specific to different roles.
  • Use gamification. Create a buzz around your technology and get your employees involved early on. You can try panel games, quizzes or digital scavenger-hunts to draw the attention to the new technology. The most important thing is to be creative and engaging. Based on my long-standing experience, gamification works wonders if done properly.
  • Perform surveys throughout the course of the user adoption campaign to get a sense of how your employees are using the new technology and gauge if the campaign is working.
  • Identify success factors and proper measurements. For example, if you run a user adoption campaign for a OneDrive for Business roll-out, a success factor could be a 50% increase of the data stored to OneDrive for Business accounts within three (3) months of implementation.
  • Offer individual training for employees struggling with using the introduced technology.
  • Schedule recurring monitoring that continues even after the user-adoption campaign has ended. It is important to continue measuring how the introduced technology is used during the coming months.
  • Work with the corporate help desk to understand inquiries and tickets related to the introduced technology. Although an increased number of inquiries is common, too many inquiries are an indicator that the user adoption campaign isn’t working well or isn’t meeting your audience’s needs or expectations.

Finally, when it comes to user adoption, there is no one-fits-all approach. Organizations are diverse, as are their employees. A proper user adoption campaign needs to be tailored to the corporate culture, the skill sets of employees and most importantly, the expected benefits for individual users. Proper user adoption campaign is crucial for rolling out any new technology. Training is just an accompanying activity rather than a replacement for user adoption and change management. For that reason, the cost of a tailored user adoption campaign needs to be added to the costs of the new technology and the corporate rollout. However, when a user adoption campaign is planned and executed properly, these additional costs will pay off soon helping secure a timely ROI.

Additional Resources: