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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
rjs2
Resolver I
Resolver I

Pull Records where ID#s are from another Query

Hi,

 

Ok, I have asked this in another format (dates, with no solutions).  The difference here is that I have a unique list of IDs (Sequence #s) from a query.  I want to pull records from a large table and only the records that match the ID from the other query.  I do not want to load all those records and then filter.

 

I am connecting to an older Oracle database in a fragel environment.  I want to limit my data pull and load only the records from the other query.

 

The field in the database is a number field.  Once I create a list from the other query, I get an error when trying to combine from the list to pull just those IDs.

 

Again, I do not want to pull 100s of thousands of records just to filter down to 90 - 500 records.  I have exhausted my search and have tried many different solutions with no results.

 

Can anyone help?

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@rjs2 Best thing, create a view on the Oracle server that does an inner join between the tables. Or, use a Merge query to do the same in Power Query (might fold)


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

@Greg_Deckler  I think I will just pull the history table with the record table, then reference that to a new table to build the history table off that.  And just clean up the record table to remove duplicates.  It may even run faster that way. 

 

I really wish there was a way you could do more in the WHERE clause.

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@rjs2 Best thing, create a view on the Oracle server that does an inner join between the tables. Or, use a Merge query to do the same in Power Query (might fold)


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

I should have added that I cant make any views or stored procedures in our environment due to restrictions.

 

The table I am connecting to is a history table for the customer record that logs changes.

 

So you are saying there is no way for me in PBI to make a list from query1 of seq# and then use that list in the WHERE clause of query2 from the history table to only pull and load records associated with those seq#? 

@Greg_Deckler  I think I will just pull the history table with the record table, then reference that to a new table to build the history table off that.  And just clean up the record table to remove duplicates.  It may even run faster that way. 

 

I really wish there was a way you could do more in the WHERE clause.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.