Reply
Regular Visitor
Posts: 38
Registered: ‎01-06-2017
Accepted Solution

Vlookup replacement in Power BI or SSIS

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.


Accepted Solutions
Regular Visitor
Posts: 38
Registered: ‎01-06-2017

Re: Vlookup replacement in Power BI or SSIS

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!

View solution in original post


All Replies
Super User
Posts: 935
Registered: ‎06-09-2016

Re: Vlookup replacement in Power BI or SSIS

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/

 

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Regular Visitor
Posts: 38
Registered: ‎01-06-2017

Re: Vlookup replacement in Power BI or SSIS

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

Moderator
Posts: 9,487
Registered: ‎03-06-2016

Re: Vlookup replacement in Power BI or SSIS

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor
Posts: 38
Registered: ‎01-06-2017

Re: Vlookup replacement in Power BI or SSIS

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

 

 

Sample Employee Data.PNG

 

 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:

 

 

Sample Pay Rates Data.PNG

 

 

 

 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:

 

 Sample Labor Data.PNG

 

 

 

 

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):

 

NameDateStart TimeEnd TimeHoursJob #NoteClient Name Rate  Gross Labor Cost L&I RateL&I T CostTax Rate Total Taxes  T Labor Cost OH RateT OH Cost

 

Regular Visitor
Posts: 38
Registered: ‎01-06-2017

Re: Vlookup replacement in Power BI or SSIS

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!

Member
Posts: 64
Registered: ‎01-06-2017

Re: Vlookup replacement in Power BI or SSIS

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] ))

Regular Visitor
Posts: 38
Registered: ‎01-06-2017

Re: Vlookup replacement in Power BI or SSIS

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

Highlighted
Regular Visitor
Posts: 38
Registered: ‎01-06-2017

Re: Vlookup replacement in Power BI or SSIS

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!