Discover, link, explore Power BI data in Excel


In this blog post, I will share the easiest method to bring data from the shared dataset to Excel sheets using “Featured tables”.

Remember, your users must be an Excel subscriber with Microsoft 365 and have Pro license to use the featured tables. Your Power BI admin should have enabled “Allow connections to featured tables” in your organization tenant.

Featured tables are a way to link your data in Excel to data in Power BI. These tables will be listed in Data Types gallery in Excel.

Identify the tables in your dataset that your users would be interested in. Set “Is featured table” to Yes to these tables. Featured tables in DirectQuery datasets and live connect datasets won’t be listed

Set “Key column” field to unique id of the row. Excel will use the unique id to link the cell to a specific row in the table. Then, the field mapped to “Row label” will appear as the cell value for a linked cell (Data selector pane and Information card)

Model view in Power BI desktop

After you publish this dataset, other users can see these featured tables in Data Types gallery in Excel.

They can select a cell or a range in the Excel sheet containing a value that matches the value in a featured table. Row matching is based on text columns in the featured tables except if the row label or key column are numeric. A row’s text fields are compared to each search term for exact and prefix matches.

In the above selection, Excel found one high confidence match for customer id 11021 and links the cell value to the matched row. Also, cell value 11021 is replaced with the row label of the matched row with a briefcase case icon. Notice the cell value 130 is displayed with a question mark as it has many matching rows. Data Selector lists the matched rows sorted by the confidence level. Once a suggestion is selected it will be linked to the cell.

Data Selector

Now the cell values in Excel are connected to the row in Featured table, your users can view and insert related data of the matched row.

Selecting the briefcase icon in the cell shows a card with data from fields (including measures).

They can add values of specific fields either by using the “Insert Data” icon or inserting a new column with the name of the field.

It is important to remember Excel retrieves and saves all field values of the linked row in Power BI featured table. So, anyone to whom you share this file with can refer to any fields of the linked row without requesting data from Power BI.

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s