Connect a knowledge store with Power BI
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.
Follow the steps in Create a knowledge store in the Azure portal or Create an Azure Cognitive Search knowledge store by using REST to create the sample knowledge store used in this walkthrough. You will also need the name of the Azure Storage account that you used to create the knowledge store, along with its access key from the Azure portal.
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.
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
Start Power BI Desktop and click Get data.
In the Get Data window, select Azure, and then select Azure Table Storage.
For Account Name or URL, enter in your Azure Storage account name (the full URL will be created for you).
If prompted, enter the storage account key.
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.
On the top ribbon, click Edit Queries to open the Power Query Editor.
Select hotelReviewsSsDocument, and then remove the PartitionKey, RowKey, and Timestamp columns.
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.
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.
Select hotelReviewsSsPages, and then repeat steps 9 and 10 to delete the columns and expand the Content.
Change the data type for Content.SentimentScore to Decimal Number.
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.
On the command bar, click Close and Apply.
Click on the Model tile on the left navigation pane and validate that Power BI shows relationships between all three tables.
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.
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.
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.
To learn how to explore this knowledge store using Storage Explorer, see the following article.