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”.
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 t data type in the Excel report)
- Key Column – ID
After we save the choices made in the dialog box, we a ready to publish our results to the Power BI server.
Select the desired workspace with which to save the results. We will choose “CompanyReports”.
Once published, we can browse our “CompanyReports” workspace and see the uploaded “Consultant” dataset.
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.