cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mazacini Frequent Visitor
Frequent Visitor

How to restrict table load to records with key field already present in existing table

Hi PowerBI newbie here! I've loaded a fact table of invoices. I want to load a customer table, but only for those customers that have an invoice record in the invoice table. I'm thinking of something that would apply a "where exists" type condition to the customer table load. Any suggestions?
14 REPLIES 14
Super User IV
Super User IV

Re: How to restrict table load to records with key field already present in existing table

If this is SQL Server and you have the ability to submit SQL code for a query or have someone create a view or stored procedure, you could do this in straight-up SQL join. Otherwise, @ImkeF might have some suggestions for you. You could look at doing a Merge query, for example in Power Query. Essentially import your customer table and then merge it with correct type of merge with your invoices table.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Mazacini Frequent Visitor
Frequent Visitor

Re: How to restrict table load to records with key field already present in existing table

Thanks Greg.

 

The tables in question are in csv format.

 

I am a Qlikview user, and have been accustomed to load only relevant records, where possible, from my dimension tables ie I've excluded non-relevant data from the model altogether.

 

So I was wondering if there is an equivalent option in PowerBI.

 

I can see that the merge option should work from a modelling point of view, but I still would have a pile of non relevant customer info hanging around the model.

 

If you follow.

Super User IV
Super User IV

Re: How to restrict table load to records with key field already present in existing table

No, I don't follow. If you use a Merge query with the right type of join then you should only end up with relevant customer data in the model. If you use an Inner join, then it should only include matching rows. There is a way to put this all into a single query. See my article on that here:

 

https://social.technet.microsoft.com/wiki/contents/articles/32915.power-bi-merge-query-with-m.aspx?R...

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Mazacini Frequent Visitor
Frequent Visitor

Re: How to restrict table load to records with key field already present in existing table

Thanks Greg. Let me work through your article - it looks like it is right on the money!

Microsoft v-cherch-msft
Microsoft

Re: How to restrict table load to records with key field already present in existing table

Hi @Mazacini

 

Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution. Here is the article about Merge queries for your reference. You may check the join types in the article.

2.png

Regards,

Cherie

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Mazacini Frequent Visitor
Frequent Visitor

Re: How to restrict table load to records with key field already present in existing table

Hi Greg.

 

I worked through your article.

 

So I reckon I can structure a Merge Query to append the Customer data to the Invoice table, but only for those customers who have records on the Invoice table.

 

 

What I had in mind was that both tables would remain separate, but that the query that populates the Customer table could be structured so as to load only those records where the customer is present in the Invoice table.

 

As I mentioned, I'm coming to this from a Qlikview background, so this informs my perspective!

 

Thank you for your help so far.

hahsyaj Frequent Visitor
Frequent Visitor

Re: How to restrict table load to records with key field already present in existing table

Hi, I am very new to the Community. It might sound stupid, but how did you manage to post a screenshot of your data? I have been trying to create a message and captured my data using Snipping tool, but I am not able to Paste that capture in the Message Body. Also, I don't see any "Attach" option. Sorry for the inconvenience. Thanks, J
hahsyaj Frequent Visitor
Frequent Visitor

Re: How to restrict table load to records with key field already present in existing table

Hi, I am very new to the Community. It might sound stupid, but how did you manage to post a screenshot of your data? I have been trying to create a message and captured my data using Snipping tool, but I am not able to Paste that capture in the Message Body. Also, I don't see any "Attach" option. Sorry for the inconvenience. Thanks, J
Mazacini Frequent Visitor
Frequent Visitor

Re: How to restrict table load to records with key field already present in existing table

Hi. No, my issue has not been resoled yet. I understand that there are ways to structure the model so that non-relevant data can be excluded, for example by Merged Queries. But I'm trying to establish if cetain data can be excluded from the load by reference to whether an associated record exists in a previous table load.

 

Maybe it's just the case that Power BI does not work that way. I've used other tools where you can attach a "where Exists" condition to a table load which will exclude specific records from a table load. I'm just wondetring if there is an equivalent option in Power BI.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors