Vlookup replacement in Power BI or SSIS

[EnochS]

I'm trying to create a method of calculating job costs per job. I've been successful doing this with excel, but I'm moving everything into the SQL Database and trying to replace my lookup functions with SSIS/ETL through SSDT. I've created the necessary Tasks and everything is loading correctly from my flat files into SQL and finally into Power BI. The problem is that I no longer want to pre-calculate my labor costs, taxes, L&I, and Overhead in an excel file since this seems to defeat the purpose of "automation". I know that Power BI has some complex abilities with nested DAX formulas and I'm trying to use them to replace the calculations from the excel file. Basically I had a table for all of the changing rates (listed earlier in this paragraph) and I was using "=vlookup(...)" function in excel to calculate the appropriate rates with the appropriate dates. One sample would be the hourly rates. Employees get raises, and data 6 months before that rely on their previous hourly rate, so I need to match the rows date with the effective hourly rate at the time. I'm not sure if this process is best made in SSIS or in Power Bi (measures, custom columns, etc..) or lastly pre-ETL (excel). The data is coming from two sources (regarding hourly rate): SQL Database (Hourly Rate, Effective Date, Employee ID; Employee ID, Employee Name) and a flat CSV file that is ETL'd through SSIS into SQL Database (Name, Date, Hours, Job #, etc...).

I need somehow to have Power BI or SSIS add this column or lookup up the appropriate hourly rate so that I could create a custom column or measure to be able to multiply the two columns to compute the gross labor cost per line and all of the other costs. For some reason I, hitting a wall regarding which formulas to use. I'm still adapting and learning DAX (used to excels nested formulas).

Anything helps, even if someone points me in the right direction,

Thank you.

[v-qiuyu-msft]

Hi @EnochS,

On Power BI side, you can create a calculated column or measure use LOOKUPVALUE Function (DAX). It would be better if you could share some sample data for our analysis.

If you want to find the replacement of VLOOKUP in SSIS, you can post a thread in SQL Server Integration Services forum.

Best Regards,
Qiuyun Yu

[EnochS]

Here is the sample data for the employee Id data that I relate the data to.

 Here is the Employee Rate Change Table that is linked to Access and soon to be sharepoint form that generates employee raises and records the logs of when the raise became effective:

 Here is the labor data that needs to have the above data populated from the other tables. I would prefer to take care of this in the SQL database first and then upload it to Power BI, but I don't mind doing some of the calculations in Power BI if i have to:

 

Lastly, the end result should have all of these columns populated but if i successfully populate the data for the hourly rate then i should be able to replicate the same thing for the rest of the data since some of the data is static or is only "looking up" one criteria (where hourly rate is looking up employee name and date):

Name

Date

Start Time

End Time

Hours

Job #

Note

Client Name

Rate

Gross Labor Cost

L&I Rate

L&I T Cost

Tax Rate

Total Taxes

T Labor Cost

OH Rate

T OH Cost

[MarkS]

Hi Enoch,

I think the formula for the BI calculated column for the hourly rate would be

Rate = CALCULATE (
    Max ( 'Employee Rate Change Table'[Rate]),
    FILTER ( 'Employee Rate Change Table',LabourData[Date]>'Employee Rate Change Table'[Effective Date] ))

[EnochS]

Thanks, @MarkS. I'm going to store that script for future use.

[EnochS · Accepted solution]

I found the solution!!

http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range

Thank you @v-qiuyu-msft and @BhaveshPatel for both pointing in the right direction. That heped me resolve the issue!

[EnochS]

My progress report:

This formula is working great and was able to give me the data that I needed. I did run into an issue but it was only because of my own inexperience. The issue was that there was an error in the SSIS package when i would run it due to not have 2 records of employees to match the data to. Once I created a "multicast" tool to receive the "no-match output" from the "lookup" tool and then after adding a "data viewer", I was able to see the 2 references that were missing. If you run into this issue, just do the same thing and you'll see what data was missing. I'm realizing that SSIS has some powerful tools and options and that I have to instruct the software what to do in any given situation, including errors. Good luck!

[EnochS]

Thank you for replying to this query, @BhaveshPatel! I'll try this and let you know the results.

[BhaveshPatel]

You can use LEFT OUTER JOIN in the Query Editor ( Merge Queries) or LOOKUPVALUE function in DAX. 

You can also refer to Ken Puls blog for this:

https://www.powerquery.training/portfolio/replicate-excels-vlookup-function/