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.
Summary
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!
Hi Gilbert – this is great. However, it is my understanding that the people that can view this currently on the service are those with admin or create capabilities. Users that have read only permissions cannot view reports/visuals that originate from the composite models. I believe they are working to change that but for now it really limits the audience.
Hi Christopher,
Thanks for the comment.
I would have to double check this I do recall seeing something along those lines. Let me check and get back to you.
No Worries. I only learned the hard way after being excited to use the feature and pushing the report to production. Then I started getting some emails that folks couldn’t view the visuals on the report tied to the DQ table. However, myself and admins could view them. Thanks!
Yeah thanks for reminding me about this!
Hi Christopher, I reviewed your publication and it is exactly what I am doing, it works perfectly, however, as Gilbert indicates, it is not possible to find use of this function because the users with whom the reports are shared cannot see the visuals, you know if this was already passed and how to get the report to work well for all users?
I have problems with this step: I then connect to my Excel file using the Web connector. Can you explain me how can i connect to Excel file with the web connector? Thank you so much!!!
Hi Joan,
Here are more details on how to do this: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-excel
My users are having the same issues as highlighted by Christopher Majka. As a Admin of the Workspace and owner of the Dataset I can access the Visuals on report. However when share it to my end users, they are not able to see any data on the visuals. Clicking on details – tell them -“Couldn’t load the data for this visual”
Even Build permission doesnt help. I read in the Power BI community that making a user as “Contributor” eliminated the issue. But there is no way I am going to add business users as Contributors to my workspace. Hope you have some more information regarding this issue
Hi Sam
What you need to do is to move your reports to an App Workspace
Then give the users the build access to the dataset. And finally you can then grant the users viewer permissions or allow them to access it via an App
The reason for this error is because it is coming from “My Workspace”