Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
jcampbell474
Helper IV
Helper IV

Yet another Qlikview developer asking for help 🙂

For those who might not understand what we need / are looking for.  I'll try to explain it.

 

My Qlikview development primarily contains SQL.  Once my SQL is ready, I place it into the load script and Reload the application.  It executes the SQL and loads the table(s).  

In the example, below, the LOAD snippet is Qlikview's preceding load function.  Users can load from their data source, then further transform the data using Qlikview's proprietary syntax/function.  It compares values in the EmpID field to values in the previously loaded EmpID2 field and loads records where they match. 


Keep in mind that all of this happens during the load.  Nothing exists or is committed until the entire load script is successfully finished.  When it's done, data will exist only for those employees in the initial table (EmpID2).  This is a VERY valuable function when loading from multiple servers...especially for huge data sets.  It's fast and efficient.

Adherence:
LOAD
  Date(date)&'|'&EmpID as %DateEmpKeyAdherence
, num(adhPct) as adhPct
WHERE EXISTS (EmpID2,EmpID);


SQL SELECT
  cast(date as date) date
, Upper(EmpID) AS EmpID
, adhPct
FROM DB.dbo.Adherence
Where cast(date as date) >= '1/1/2020';

 

The reason I'm posting this is because I currently need to load data from tables on two different servers.  Linked server is too slow and the data set is too large.  I don't need all of the data, just data for those in the initial load.

Reference: https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Inter...

 

Any and all help will be appreciated!

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

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.

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

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

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.

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.

Anonymous
Not applicable

HI Mazacini,

 

Did you figure this out? I am also a prior Qlik user and I'd like to use a WHERE EXISTS from another table as well.

Anonymous
Not applicable

Hi Mazacini,

 

Did you find a solution to this? I am also a prior-Qlik user and have been looking for a WHERE EXISTS() equivalent as well.

One thing you might try would be to create a column called 'customer_exists' on the invoice table and then do a lookup on the customer table to retreive some value from customers if the lookup succeeds.  Then you can set a filter on your report to not show records where customer_exists is null.  This still loads all the customer records but restrics visuals to those that have invoices.

 

I come from the Qlik world as well and some of this takes a different approach.

 

hth

Hi,

I come from a SQL background so I had to get used to a limited join ability as well. I still think that Merge Queries can solve your problem.

 

As an example (before any merges):

WhereExistsSample1.PNG

 

Inner Join in Power Query:

WhereExistsSample2.PNG

 

After merge:

WhereExistsSample3.PNG

 

I think of Power Query editing as sort of a pre-model "staging" area (similiar to a SQL view or, from what it sounds like, Qlik parameters) so in that way, the extra customers aren't really in your model once your changes are applied (if that makes any sense in your context). Any DAX code or subsequent visualizations will not include those customers...

 

 

 

 

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.