Use Organization data types from Power BI (preview)

Your browser does not support video.

Not only can you get data on subjects like cities, foods, stocks, geography, and more, but you can also get data types provided by your company or organization through Power BI.

Using data types linked to Power BI is as simple as entering text into a cell and selecting a button. You can view data type cards to get specific information about your converted data and even extract that info to your Excel workbook. Refresh to keep your data up to date and see Power BI data without ever leaving Excel.

Notes: In order to access and use data types from Power BI, you must meet the below requirements. If you do but still don't see the feature, please contact your IT admin for help.

Convert your data to a data type from Power BI

  1. In an Excel workbook, enter the text you want to convert in different cells. For example, in a single column, you might enter the names of employees of your organization.

  2. Select all the cells with the text you want to convert to data types.

  3. With the cells still selected, go to the Data tab, and in the Data Types group, select Organization. This button will only search across Certified datasets in Power BI.

    Excel Data tab showing Organization Data Types icon

    Tips: If you already know what data type to use, you can specify it from the dropdown gallery. This helps Excel better match your text and retrieve the correct data. You can specify a data type from these two places:

    • Under From your organization, you'll find up to ten data types available to you, showing datasets marked as "certified" and "promoted" in Power BI.

    • Select More from your organization to open the Data Selector pane and view all data types available to you from Power BI.

  4. If Excel finds a match between the text in the cells and Power BI, it will convert your text to the appropriate data type. You'll know they're converted if you see the data type icon next to the cell value.

    Note: If you see   Question mark icon  instead of the data type icon, then Excel is having a hard time finding a match. You'll need to specify your data. To do this, select the  Question mark icon  to open the Data Selector pane and Use the Data Selector to specify linked data types below.

  5. After you convert text into a linked data type, an icon will appear in the cell. Select it to view the card. The card shows you a list of fields and corresponding values for the information provided by Power BI. Scroll through the card and expand sections to see all the field/value pairs that you can work with.

  6. You can easily extract data from data types into your workbook from the card or from the data linked cell using the Insert Data button Add Column button .

    • From the card: Hover over any value and you want to insert and select the Insert Data button  Add Column button .

    • From the cell: Select one or more cells of the same data type to extract data from. The Insert Data button Add Column button  appears. Select it to view the field list, then select a field name to insert the data for the cells you selected.

      Tip: If you're using a table, you can enter field names in the header row.

Use the Data Selector to specify linked data types

If the Data Selector pane opens and you see a   Question mark icon  next to your selected cells, Excel needs your help to find the data that matches your cell value.

  1. In the Data Selector pane, review the results. You may see different results from different data types. You will only see data types that are "certified" by Power BI.

    Notes: 

    • If you don't see any results or the result that you need, you may need to refine the content in your cells or specify the data type.

    • If you still can't find what you're looking for, that data type may not be available to you. Contact your IT admin or the owner of the Power BI dataset you're retrieving data from. 

    • Check your spelling and ensure that you're separating different terms into different cells.

    • To search across all data types from Power BI and not just those that are certified, select Change and specify the data type you want to use.

  2. Select the result that matches your data. Once you select a result, the Data Selector will switch to the next result that needs identifying.

  3. When all your cell values are linked to their appropriate data types, the Data Selector will disappear and you can now view cards and insert data.

Refresh data from Power BI

Once you have data types from Power BI in your spreadsheet, you can use Refresh All button  Refresh All button  in the Data tab to retrieve the latest information.  

Other users can open the file and refresh the data, but they need to have access to the capability and Power BI data, otherwise they will just get access to the information previously saved to the file.

Learn more about linked data types

Access Power BI featured tables in Excel

Set featured tables in Power BI Desktop

How to write formulas that reference data types

What linked data types are available?

Linked data types FAQ and tips

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×