My company requires an excel sheet to calculate multiple factors for employees. I currently have this set up to calculate for one employee at a time by have a named cell, EMPID, which is thrown into Power Query to automatically calculate this information. However, this method only allows one employee’s information to be ran at a time and there are occasions where 10+ employees will need to be ran.
The current set up is:
- named cell is created into a query
- this query is referenced by 3 SQL queries in Power Query to automatically grab the employee’s information
- all queries are merged into 1 excel table output
I previously had it set up to run multiple employees at the same time by having the employee entered as a table, not a named cell, but in order for the SQL queries to get the information, I would need to merge the table within each query (so, 3 times + the final output). This caused the refresh time to be 1 minute +, depending on the amount of employees.
I’ve attempted to do this as a named range, but the data is automatically thrown into a table – which can’t be used in SQL as I’m referencing the data with the & operator
Current use:
- sql code xyv employid = ‘” & EID & “‘ abc code
The & cannot be used with a table or text type.
Assuming you have a table of EIDs in Power Query named TableEIDs similar to this:
| EID |
|---|
| a1 |
| b2 |
| c3 |
Right click on that query and select Reference. Then in the formula bar, update it to:
= "'" & Text.Combine(TableEIDs[EID], "','") & "'"
This will give you a text query looking like:
'a1','b2','c3'
You can now use this in your SQL like:
sql code xyv employid IN (" & theReferenceQueryText & ") ....