Accessing Power BI confidential data in Excel for internal organization users

Background

Microsoft Excel is a popular and preferred spreadsheet solution for quick daily use reporting by the majority corporations and businesses in the world. Many times, corporate users need access to organization’s data in Excel for further development of MIS Reports. Power Query is a powerful tool embedded in Excel which can connect to internal Database Server, Online CRM/ERP Services, Excel files etc. hosted within an organization’s network or over the cloud. Apart from that, users also heavily use hyperlink to another Excel files reusing existing source data already prepared by other people.

Challenges / Issues

Numerous challenges or issues occur when a user attempts to refer to data stored in another Excel file (via formulas) or Database Server / ERP (via Power Query) as below:

• There is a high probability that the original source Excel file to which references are made, might get deleted by the owner, or the structure may get modified, eventually breaking cell references. This would certainly lead to errors in reports.
• Data imported via Power Query arrives in the form of table. Tables are generally not suitable for reporting, as various reporting authorities prescribe a fixed and pre-defined format in which to prepare and submit a report.
• Power Query is a bit technical, and not every user might be able to grasp it.
• Database connection requires a server IP address, credentials, etc. Exposing of such critical information might lead to security lapses, allowing easy access to the organization’s internal data.

Solutions

Microsoft offers 2 ways to connect to Power BI published data from Excel

1. Direct connectivity to Power BI published dataset via Power Pivot, which supports creating DAX measures too.
2. Access of designated tables of Power BI datasets, which can be used as an Organizational Data Types.

Power PIVOT

A dataset is essentially a collection of tables co-related using the relationship feature of Power BI. Excel lacks an easy and direct way of analyzing relational data. One needs to use VLOOKUP, XLOOKUP, MATCH, INDEX like functions for co-relating data. Power Pivot provides an easy way of reusing Power BI published dataset, having already defined relationships to design reports in the form of Pivot Table or Pivot Charts directly in Excel itself.

Steps to analyze Power BI published dataset using Power PIVOT is as below

1. In Excel navigate to Data tab > Get Data > From Power BI <organization_name>
   
2. Right-hand pane window will appear. Select target dataset from the list
   
3. Excel will create a new worksheet with blank Pivot Table designer. Drag & Drop fields into Pivot just like regular Pivot.

Organizational Data Types

Excel supports 3 types of data types natively:

1. Text
2. Number (includes Date-Time)
3. Logical (True/False)

Formula is not a data type but evaluates to any of these 3 data types. Office 365 version of Excel introduced support for a new data type called Linked Data Type, which is of type record. Linked data type holds a reference to a record (or row) containing multiple fields. So virtually it is a cell which can hold multiple values internally (refer screenshot below).

(linked data types have an icon as prefix in cell value)

Value of a linked data type cell can be extracted into another cell by referencing the linked data type cell using formula =cell_reference followed by a dot sign, which further enumerates field names in that record type cell (refer screenshot below)

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

(evaluated screenshot below)

Excel includes a few built-in linked data type sources such as Stock Market, Currency, Geography, and so on. Apart from that, any Power BI dataset table can be promoted to include itself as a custom linked data type, which is available only to Excel users of that organization. Such data types are available as organizational data types.

Organization Linked Data Types can be created using Power BI, by setting a table as a featured table and then publishing it (screenshot below).

Advantages of Organization Linked Data Types approach:

• This approach prevents exposure of the source Excel file or Database to the end user, thereby enforcing privacy.
• Power BI Service supports an elegant system of access control by designating workspace access to a specified user group, which also gets applied to Organizational Data Types.
• User does not need to import source data again into some separate Worksheet to setup VLOOKUP to fetch values of other fields. So, the resulting excel file is light-weight with smaller size and fewer formulas in it (explained in case study).
• Organizational Data Types work seamlessly in Excel Online (browser-based Excel). User does not even require to be on an organization’s VPN to access source data. Power Query or external Excel file references require the user to be on an organization’s LAN, which is a downside.

Case Study: VLOOKUP vs Organization Data Types

Scenario:

HR of an organization is required to prepare an Excel file where we need to do some analysis for individual employees. He exports an Excel file of Employee Master from ERP and then manually copies and pastes Employee Master data from that Excel file every month. Currently he is referencing and linking to this master data using the VLOOKUP function (as per below screenshot). He is maintaining this Master worksheet in many Excel files & has to manually update it.

In the above approach, if the HR fails to update Employee Master sheet, it can lead to incorrect reporting & decision-making. Also, if any column gets added or removed from Employee Master in future, VLOOKUP function will require modification of column references manually.

As a BI Consultant what solution can you offer ?

Solution:

Power BI supports connectivity to popular ERP, Database, Excel files etc. We will simply create a dataset in Power BI, extracting this data from ERP (via Power Query transformations if required). And then, without creating any visualization, we will simply publish the dataset, setting Featured table in the modelling window of Power BI, to the desired workspace of HR. This will enable HR to view tables of Power BI datasets shared with him. Afterwards, we will simply remove VLOOKUP and replace it with cell references as demonstrated below:

Compatibility

All the things explained and demonstrated in the blog are compatible on an Office 365 version of Excel (Desktop + Web). User needs to be on Office 365 Business or Enterprise subscription. Office 365 Personal / Home subscription or perpetual editions of Office like 2013, 2016, 2019, 2021 etc. do not support all these features as they require associated domain, which is missing in these editions.