Day: September 19, 2019

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 way of dealing with this would be to merge the tables in Power Query Editor using a Left outer Join

This blog post is to look at what happens when you merge a direct query and an Imported table

Only one way to find out. 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 we also have our Null values because a left join has been used

Could this work? Back in my test page (After closing and applying)

It certainly seems to. Adding the filter removes this customer from the table.

If I remove another using the PowerApp, will it work?

I have to refresh in the desktop to see what happens.

Well this is good news. 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

Great news