Power BI: Merging a direct query and an Imported table in a Power BI composite Model

I have a Direct Query table in a composite model, all the other tables are Imported into Power BI.

The whole reason for the Direct Query table is to use it with a PowerApp. The PowerApp can be used to update the data in this table from the PowerApp and the update should appear in the report straight away.

However, I need to use it against an imported table, so the report will remove records that are in the Direct Import table. Here is the example...

I need to join the tables together to ensure that the customer in the direct Query table is not only updated here, but pulls out the record from the Customer Table.

Joining doesn’t work because it needs to be a left outer join. You can’t filter for a NULL value in the staging table if you join the tables.

Therefore, one of the best ways of dealing with this is to merge the tables in Power Query Editor using a Left outer Join.

This blog post looks at what happens when you merge a direct query and an Imported table...

Go into Edit Queries Mode

First of all,  I click on the main Customer table, Then merge Queries

Straight away, I notice that the record I have just added isn’t in the direct query table within Merge Query

 

Now add in the customer number of the direct query table and there it is, with the latest Customer ID linked to the Imported Data set

the CustomerID is back and I also have the Null values because a left join has been used

This works back in the test page (After closing and applying).

Adding the filter removes this customer from the table.

 

To remove another using the PowerApp I have to refresh in the 'desktop' to see what happens.

You can have a direct Query table working with a PowerApp and use it to Merge with another table so you can manipulate this without it being in Direct Query mode.

If this guide was useful, and you feel you would benefit from further training, our Power BI Training Course can help you develop your practical knowledge and skillset.

If your organisation would like more information on Power BI and how to get started download our quickstart PDF below, designed to provide you with relevant actionable dashboards and the internal skillset to use them, or call us on 01246389000 or email us on enquires@peakindicators.com to discuss the opportunities Power BI and Azure can create for your business.

 


Subscribe to our Newsletter

If you enjoyed this article why not get great insights straight to your inbox

Comments (1)
kalyani Cynixit 4 months ago

Enjoyed reading the article above, really explains everything in detail, the article is very interesting and effective. Thank you and good luck for the upcoming articles