cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heytherejem Member
Member

Referencing a table and column in SQL statement for inner join

Hi there

 

I have a HUGE dataset over 10 million rows and a small dataset of 2501 rows.

I need to do an inner join from the small to the large - so I only get matching rows from the large dataset based on Invoice ID, that way I don't bring in the entire large dataset.

 

The large dataset is called stg.vw_ActrlMthlyClaimSnapshot and Invoice ID is "Invoice ID"

The small dataset is called "OverPayments" - which I've already imported into Power BI so it's an existing table and Invoice ID is called "InvoiceId"

 

Below is my attempt at SQL but I'm not referencing the OverPayments table or Invoice ID column correctly. Help!

 

select*from stg.vw_ActrlMthlyClaimSnapshot
inner join Table.OverPayment 
on "Invoice ID" = STG.vw_ActrlMthlyClaimSnapshot.InvoiceId

 

 

 

 

 

5 REPLIES 5
Super User
Super User

Re: Referencing a table and column in SQL statement for inner join

 
Super User
Super User

Re: Referencing a table and column in SQL statement for inner join

Hi @heytherejem 

 

You can use Merge Queries, if you getting your data from SQL server it will perform Query folding.

image.png

You can check your SQL syntax by clicking on "View Native Query" 

image.png

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

heytherejem Member
Member

Re: Referencing a table and column in SQL statement for inner join

@Mariusz  sadly view native query is greyed out for the merge step. 

 

Basically, if I knew how to reference the OverPayment table and the InvoiceId column in a SQL statement, it should work. I've tried Table.OverPayment and that doesn't work either. 

 

I absolutely don't want to import the entire huge dataset and perform a merge within PBI. 

Super User
Super User

Re: Referencing a table and column in SQL statement for inner join

Hi @heytherejem 

 

Please check how you importing your tables, do you just select them from the list or do you write a sql do it?

Some of the transormations are not suported by Query Folding so its importent to make the merge before you insert any function that can not be translated to a native langage.

Please check this article and video
https://exceleratorbi.com.au/how-query-folding-works/

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
heytherejem Member
Member

Re: Referencing a table and column in SQL statement for inner join

The huge table is on a SQL server, so I can use the SQL editor within Power BI to define what I want before I import it. What I want, is an inner join to an existing table within Power BI.

 

I have attempted to define an inner join, however I don't know how to reference the Power BI table called OverPayment.

 

If anyone knows how to reference a table in the SQL query editor, that would be great.


Thanks :-) 


Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 21 members 849 guests
Please welcome our newest community members: