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!

Oracle SQL query based on separate Excel table column

Looking for suggestions on how to achieve the following:

 

Our customer has it’s own complaint database that we export into excel each week to vet the complaints as proven vs. not proven.

To vet and analyze these on our end, I run a SQL query based on parameters from our customers data to pull the location it was picked from, current inventory status, etc.

 

So for example,

Complaint on Excel sheet consists of:

 

Carton IdRP Order IDDate ShippedCategoriesPart OrderedLot OrderedQty Ordered
CTN9993223DCSH34974210/7/2022Shortage1100247735580808

I concatenate the Carton ID, Part, & Lot and then I would take that information to run something like:

 

SELECT DISTINCT Date, Location, User, Device, INVSTS

from x

where CTN||PART||LOT = 'CTN9993223110024773558080' (concatenated from the above complaint)

 

I’m wanting to attempt to automate this in Power BI. Does anyone know if there a way in power bi/dynamic M query to set the red portion that comes from my complaint table and automatically set it as a parameter in my sql query so that it’s only querying based on the complaint data?

 

I've loaded the complaint data from the excel sheet into power BI and created a concatenated column within power query, but I want to somehow use the concatenated column as a paramenter in my SQL statement to basically say where CTN||PRT||LOT in (column name from excel sheet")

 

Any tips are appreciated!

Status: Needs Info

Hi @HALEYSTODDARD 

1. You mentioned that you want to attempt to automate this in Power BI , can you explain it in detail ?

2. You also mentioned that you want to somehow use the concatenated column as a paramenter in my SQL statement to basically say where CTN||PRT||LOT in (column name from excel sheet") , can you provide screenshot or operation steps to explain your needs ?

 

Best Regards,
Community Support Team _ Ailsa Tao

Comments
v-yetao1-msft
Community Support
Status changed to: Needs Info

Hi @HALEYSTODDARD 

1. You mentioned that you want to attempt to automate this in Power BI , can you explain it in detail ?

2. You also mentioned that you want to somehow use the concatenated column as a paramenter in my SQL statement to basically say where CTN||PRT||LOT in (column name from excel sheet") , can you provide screenshot or operation steps to explain your needs ?

 

Best Regards,
Community Support Team _ Ailsa Tao