How to create DirectQuery (Composite) using an existing Power BI Dataset and a local Excel file
If you are looking how to use DirectQuery (Composite) models in Power BI I show you how to do this with an existing Power BI dataset from the Power BI Service and then importing an Excel file from SharePoint.
In my working example I already have got a dataset which contains the Power BI Audit Log data.
I want to further enrich my data with the users Role and Description of their role.
Below I explain how to do this.
Ensuring the Preview Options has been enabled
The first thing is to ensure that I have enabled the Preview option for
I click on File, Options and settings and then Options
I then click on Preview Features and then I selected DirectQuery for Power BI datasets and Analysis Services
- I would then close and then re-open Power BI Desktop.
Connecting to an Existing Power BI Dataset
The first thing that I need to do, is to create a new PBIX File.
Once it is open, I then click on Power BI datasets
I then select my Certified dataset called “Power BI Audit Log – Blob Storage”
Once this dataset is loaded, I can then see tables in the fields list
Changing the Power BI Desktop File to use a DirectQuery connection
The next step is to create a DirectQuery connection along with imported data.
In my PBIX file, on the bottom right-hand side I click on “Make changes to this model”
I then get a Window letting me know that it is going to make changes changing it from a Live Connection to DirectQuery
- I click on Add a local model
Once this has completed, I can then see my Storage Mode on the bottom right-hand side now is “DirectQuery”
- The options in Power BI have now changed where I can add additional data.
Importing an Excel file into my DirectQuery Power BI file.
The next step is for me to add the Excel file to my DirectQuery Power BI file.
I click on Transform data and Transform data
- This then opens the Power Query Editor
I then connect to my Excel file using the Web connector
- I then put in my Excel file details and then work through getting the data into the right format as I would with any other data that is being imported using Power Query.
- I then click Close and Apply to load this data into my model.
When this loads into the dataset there is a window warning the Potential Security Risk which details that queries could go across either dataset.
- I then click OK
Next, I click on Model to see the different tables in my model
Creating a relationship to the DirectQuery Power BI Dataset
The final step is to create a relationship from my imported table to the DirectQuery Power BI Dataset.
This is really where the power comes in, allowing me to merge data from totally different sources together.
I click on Manage relationships
When the Manage Relationships window opens I can see the existing relationships from my power BI Dataset
To create the relationship I click on New, and then create my relationship.
- The table “Power BI Audit Log” is my DirectQuery table.
- The table “UserDetails” is my imported data.
I then configured it as shown below.
- I then clicked OK
I could now see the relationship between my tables
- I can create a Visual where I am now combining data from my DirectQuery table and my imported table
- NOTE: The [Total Events] is from the DirectQuery table and the Role is from my imported table via the Excel file.
- I then uploaded the PBIX file to the Power BI Service.
Configuration of the data source credentials
Once I have uploaded the PBIX I will then need to configure the data source credentials in order for the datasets to work and for the imported tables to be able to be refreshed.
- I go into the data source settings for my dataset and I can see that I need to configure my credentials.
- I then click on Edit Credentials and put in the valid details.
- I can then see that it is configured correctly.
- If required I can then configure the scheduled refresh for my Excel file.
Finally, I can interact with the Power BI report.
In this blog post I have shown how to use the new DirectQuery feature in Power BI and importing an Excel file and then creating a relationship between the DirectQuery table and the Imported table.
This is really fascinating, and no doubt opens a whole lot of doors going forward.
Any comments or suggestions are most welcome!