Hello -- I haven't combined direct query and imported data together and am wondering about the best approach to do so. It seems that there are limitations to the formulas that I can use with Direct Query, yet there is too much data to Import.
Here is the scenario:
The Direct query has Employee Name, Client ID, Actual Hours charged for client and Date
The Import table has predicted Hours for each Employee, Client and Hours. We lump the hours by week (sunday date)
The goal is to create a table that has:
Employee Name | Client ID | Week Beginning (Sunday) | Predicted Hours | Actual Hours |
I can pull the data from the direct query easily enough and assign a week beginning to it.
I can import the data in excel with the predicted ok.
THe question is, how do I best combine these two data sources?
I can't seem to write any column formulas using the direct query tables/columns. Why is this?
Also, I will need a formula that says 'if employee name (import) matches employee name (direct query) AND client (import) matches client (direct query) AND the week beginning (import) matches week beginning (direct query) then bring back the hours (either actual or predicted depending on where I should be pulling everything together).
How would you approach this problem? Should I try to create a table? If I create a formula that pulls the hours either from the direct query or the import, where should this formula reside?
