What happens when you merge 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 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

3 Replies to “What happens when you merge a direct query and an Imported table in a Power BI composite Model”

  1. Hi, thx for your helpful post. I’ve been doing sth very similar, joining in query editor a Azure SQL table in DQ mode to an imported excel file. It does work nicely indeed in Desktop, but as soon as I tried to run this on Service, then

    1- on demand or schedule refresh won’t happen (error message “more information is needed to combine tables”). I’ve spent an hour with 3 engineers from the PowerBI support team trying to work on data privacy settings and other things, without any success,

    2- any update on my SQL table generated by the embedded PowerApps will -refresh the DQ table, but not the composite table (maybe because of 1-).

    Did it work for you on Service?
    Thx in advance for your help, and kudos for your work!
    Ben

    Like

    1. I will have another look at this. The hope always is that every month gets rid of a few more glitches. I don’t think an embedded power app visual can update an imported table of data. I have another post on that somewhere but it is a while ago and Power BI is constantly changing.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s