cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mazacini
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.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Mazacini
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.

Highlighted
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...

 


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Mazacini
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

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.

Anonymous
Not applicable

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
Anonymous
Not applicable

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

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors