Connect a knowledge store with Power BI

Important

Knowledge store is currently in public preview. Preview functionality is provided without a service level agreement, and is not recommended for production workloads. For more information, see Supplemental Terms of Use for Microsoft Azure Previews. The REST API version 2019-05-06-Preview provides preview features. There is currently limited portal support, and no .NET SDK support.

In this article, learn how to connect to and explore a knowledge store using Power Query in the Power BI Desktop app. You can get started faster with templates, or build a custom dashboard from scratch.

Sample Power BI template - Azure portal only

When creating a knowledge store using the Azure portal, you have the option of downloading a Power BI template on the second page of the Import data wizard. This template gives you several visualizations, such as WordCloud and Network Navigator, for text-based content.

Click Get Power BI Template on the Add cognitive skills page to retrieve and download the template from its public GitHub location. The wizard modifies the template to accommodate the shape of your data, as captured in the knowledge store projections specified in the wizard. For this reason, the template you download will vary each time you run the wizard, assuming different data inputs and skill selections.

Sample Azure Cognitive Search Power BI Template

Note

Although the template is downloaded while the wizard is in mid-flight, you'll have to wait until the knowledge store is actually created in Azure Table storage before you can use it.

Connect with Power BI

  1. Start Power BI Desktop and click Get data.

  2. In the Get Data window, select Azure, and then select Azure Table Storage.

  3. Click Connect.

  4. For Account Name or URL, enter in your Azure Storage account name (the full URL will be created for you).

  5. If prompted, enter the storage account key.

  6. Select the tables containing the hotel reviews data created by the previous walkthroughs.

    • For the portal walkthrough, table names are hotelReviewsSsDocument, hotelReviewsSsEntities, hotelReviewsSsKeyPhrases, and hotelReviewsSsPages.

    • For the REST walkthrough, table names are hotelReviewsDocument, hotelReviewsPages, hotelReviewsKeyPhrases, and hotelReviewsSentiment.

  7. Click Load.

  8. On the top ribbon, click Edit Queries to open the Power Query Editor.

    Open Power Query

  9. Select hotelReviewsSsDocument, and then remove the PartitionKey, RowKey, and Timestamp columns. Edit tables

  10. Click the icon with opposing arrows at the upper right side of the table to expand the Content. When the list of columns appears, select all columns, and then deselect columns that start with 'metadata'. Click OK to show the selected columns.

    Edit tables

  11. Change the data type for the following columns by clicking the ABC-123 icon at the top left of the column.

    • For content.latitude and Content.longitude, select Decimal Number.
    • For Content.reviews_date and Content.reviews_dateAdded, select Date/Time.

    Change data types

  12. Select hotelReviewsSsPages, and then repeat steps 9 and 10 to delete the columns and expand the Content.

  13. Change the data type for Content.SentimentScore to Decimal Number.

  14. Select hotelReviewsSsKeyPhrases and repeat steps 9 and 10 to delete the columns and expand the Content. There are no data type modifications for this table.

  15. On the command bar, click Close and Apply.

  16. Click on the Model tile on the left navigation pane and validate that Power BI shows relationships between all three tables.

    Validate relationships

  17. Double-click each relationship and make sure that the Cross-filter direction is set to Both. This enables your visuals to refresh when a filter is applied.

  18. Click on the Report tile on the left navigation pane to explore data through visualizations. For text fields, tables and cards are useful visualizations. You can choose fields from each of the three tables to fill in the table or card.

Clean up

When you're working in your own subscription, it's a good idea at the end of a project to identify whether you still need the resources you created. Resources left running can cost you money. You can delete resources individually or delete the resource group to delete the entire set of resources.

You can find and manage resources in the portal, using the All resources or Resource groups link in the left-navigation pane.

If you are using a free service, remember that you are limited to three indexes, indexers, and data sources. You can delete individual items in the portal to stay under the limit.

Next steps

To learn how to explore this knowledge store using Storage Explorer, see the following article.