User Profile
Bone
Frequent Visitor
Joined 4 years ago
User Widgets
Contributions
Composite Model Approach - combining data through matching columns
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?Re: Look up with direct query and with imported table as the lookup table
Hello Rico - I probably haven't explained the situation very well, so let me do that now. Time Entry Table is a Direct Query It has many rows with (the pertinent columns listed below). I am trying to create a column that contains the Sunday date for each of the rows in the table (the green column is what I am after). Power BI is kicking back an error message because dates are repeated in each row. The formulas only seem to work if the date in the DATE column only happens once.Re: Look up with direct query and with imported table as the lookup table
amitchandak thanks, I like this idea. I get the below error. In general, whatever I try anything, Power BI seems to be telling me that it doesn't like the fact that many rows in my direct query have the same date. Of course it does because there are many entries every day. How do you interpret the below error message?
