First, an Important Disclaimer
Not everybody in your organization will have the ability to create the data types we will be showcasing in this post. It’s likely only a select few individuals will have the access and permission to perform some of these tasks.
After reading this post, you may wish to share this information with those in your organization with the ability to make it a reality.
“What do I need to implement Data Types?”
Below is a table that defines the products needed to perform the actions demonstrated in the post.
Some of the actions require both an active Microsoft 365 subscription and a linked Power BI Pro license. Even for the actions performed inside Excel, the Pro license is required to connect to the data stored securely in Power BI Service. Once the data type is created and applied to data in a workbook, anyone with a Microsoft 365 license can interact with it in Excel.
To better understand the actions listed in the above table, we’ll use a bit of role-playing to explore some common scenarios where this feature can be of great use.
Featured Course
Master Excel Power Query – Beginner to Pro
Scenario #1: The HR Intern
It’s your first day on the job as an office intern and you receive the following list from your boss.
What your boss needs you to produce is a report that provides the following information when one of these names is selected from a dropdown list:
- Employee name
- Assigned department
- Number of years employed at the company
Look closely at the data provided by your boss. The names listed have a small briefcase icon to the left of each name.
If you click on one of the briefcase icons an entire card of information appears informing you of various aspects of the employee’s relationship with the company.
If you hover your mouse pointer over any of the listed bits of information, we reveal an ‘extract to grid’ icon that will place the selected information in the adjacent cell.
We can also gain access to this button by selecting one of the cell names and clicking the same icon. This presents a generic list view of statistics that you can select from.
After adding information from the list, you can select the new data and see that a formula was created that points to the adjacent cell (in this case, cell A2) followed by a dot operator and the name of the information type.
All this information is dynamic. Because this information already exists somewhere in the company, we can draw from that data to answer key questions.
We’ll see how to connect to the data source later in the post.
Creating the Requested Report
We begin by creating the dropdown list of employee names to facilitate report queries.
We use the Data Validation tool to validate against a list that points to the list of names.
Because we’re running the latest version of Microsoft 365, we can take advantage of the new searchable dropdown lists.
Adding the Employee’s Picture
To the right of this dropdown, we will write a formula that references the dropdown cell (B2) followed by a ‘dot’ (i.e., a period) to display a list of fields from which we can draw information.
We want to also display the following data:
Cell B3 =B2.DepartmentCell B4 =B2.Tenure & " years" (adding the word ‘years’ to the result makes it a bit more readable)
Changing the name to a different employee will display new information.
This would be the start of an interactive dashboard.
Featured Course
Power Excel Bundle
Scenario #2: The HR Specialist
As the senior HR specialist that is designing the data that the HR intern is drawing from, you need to make sure you have a Power BI Pro license.
Starting with a list of employee names, we select the names and connect them to our data source by selecting Data (tab) -> Data Types (group).
If you don’t see the needed data type listed, select “More from your organization” at the bottom to browse your organization’s data type library.
This will transform the names into our selected data type.
If you have a name that cannot be found in the data source, possibly due to a misspelling or incorrect name, the cell will be displayed with a question mark icon.
A list of possible matches will be displayed on the right for you to choose from if correct.
NOTE: Because this data is cached (stored locally), if you are unable to locate what you believe to be correct and current information, you may need to refresh the cached data by clicking Data (tab) -> Queries & Connections (group) -> Refresh.
Because the data is cached locally after the refresh, you can send this file to others, and they can inquire about different aspects when they click on the briefcase icon.
But what if you need a custom data type that isn’t currently available? Let’s stroll down to the IT Department and have a chat with…
Scenario #3: The Power BI Lead
As the Power BI Lead, you’ve been asked to create a new data type for the HR consultants.
With the approval of management, you begin the data type creation process by opening Power BI Desktop.
We select Get Data and point to one of the dozens of potential data sources: PDFs, SQL databases, web pages, text files, etc.
Our example will be pulling data from an Excel Workbook.
In a scenario such as ours, it’s best to have the Excel workbook stored on a SharePoint drive as opposed to your local computer. This allows users to refresh their data and provides all users with a “single version of the truth”.
If needed, we can bring the Excel data into Power Query to apply any requested transformations.
Once finished with any transformations, we load the results back into Power BI Desktop.
Selecting the Data tab (on the left) shows the results of the Power Query output.
Formatting the Data
Even though Power Query defines data as specific types, like currency, date, text, etc., it’s not responsible for appealingly presenting the data.
You need to select a column and format the data in that column according to the way you want it displayed in the report.
If you are using images retrieved from URL links, make sure you have set the column of image links to an “Image URL” data category.
The Model View shows our “Consultant” table as an object in the data model.
To set this table as a data source that can be queried, we will select the “Consultant” table scroll down the Properties panel, and toggle the feature labeled “Is Featured Table” to “Yes”.
We provide the following information in the “Set up this featured table” dialog box:
- Description – Consultants
- Row Label – Name (this is the text that you will convert to data type in the Excel report)
- Key Column – ID
After we save the choices made in the dialog box, we are ready to publish our results to Power BI Service.
Select the desired workspace in which to save the results. We will choose “CompanyReports”.
Once published, we can browse our “CompanyReports” workspace and see the uploaded “Consultant” dataset.
Featured Course
Fast Track to Power BI
Granting Access to the Dataset
To grant other users access to your uploaded dataset, select the Options button (3 vertical dots) and select Manage Permissions.
From here you can add users or groups based on name or email address along with the level of access you wish to grant.
Making the Data Type Easily Discovered
To make it easy for your users to find your data type when using Excel, select the Options button and click Settings.
Scroll down to the section labeled “Endorsement and Discovery” and select “Promote”. Ensure the “Make Discoverable” option is selected.
After a few moments, giving the servers time to synchronize, the newly advertised data type should show up in Excel for the HR Specialist to use.
A Note About Non-Cloud Data Sources
If you are connecting to data that is not in the Cloud, like we did in the previous scenario, where the data is on the premises, you need to go back to the Settings section and locate the “Gateway Connection” category.
Depending on the data source you are connecting to, the settings and options will vary. Consult your IT specialist for the settings needed for your environment.
If you are storing your data sources in a SharePoint environment, you will likely need to supply access credentials via the “Data Source Credentials” category of the Settings section.
Testing Access to the Newly Created Data Type
To see if we can connect and utilize the newly created “Consultant” data type in our Excel reports, open Excel and select Data (tab) -> Data Types (group) and click the down arrow to reveal the list of available data types.
We see the new “Consultant” data type listed in the “From Your Organization” section.
If we select a cell and type the first name of one of our consultants (ex: “Alissa”), when we select the “Consultant” data type for this same cell, we get the briefcase icon and the table button to expand the list of related information.
Clicking the briefcase icon shows all the related information we have on file for “Alissa”.
The HR Specialist can create any necessary templates that they want to distribute to others in the department.
Practice Workbook
Feel free to Download the Workbook HERE.
Featured Course
Master Excel Power Pivot & DAX (Beginner to Pro)
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
