Oracle SQL query based on separate Excel table column

[HALEYSTODDARD]

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 Id	RP Order ID	Date Shipped	Categories	Part Ordered	Lot Ordered	Qty Ordered
CTN9993223	DCSH349742	10/7/2022	Shortage	110024773	558080	8

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!

[v-yetao1-msft]

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