Lookup Cost in Another Table

[MikeMCFRS]

Hi All,

I have a table that I am about to load into powerbi but I am not sure how to lookup values yet. The main data table has incdent numbers, dates and other sensitive data that i have removed. The second table is one I created to calculate the cost of human resources and vehicle cost by the hour.

Each incident number is linked to a number of different vehicle type combinations on the same row: example f1900027251 has PE725,A725. The goal is to lookup total cost per hour for each vehicle type based on incident numbers from the main table. I am not sure if I have to split them into individual vehicle/unit number columns to be able to use the lookup feature.

I am attaching a sample excel file for context and possibly someone can show me how to work this out in powerbi. 

https://drive.google.com/file/d/1jO5tmG8WfoQ9q24OjAFAaOWWFa4aUG4g/view?usp=sharing 

[MikeMCFRS]

Hi All,

I am updating the link to the excel file:

https://drive.google.com/file/d/1jO5tmG8WfoQ9q24OjAFAaOWWFa4aUG4g/view?usp=sharing 

Thanks to you all. Looking forward to some great ideas.

[v-joesh-msft]

Hi @MikeMCFRS ,

You can split the unit number column, then create a custom column of the incident number and unit number as the index column, then create the same index column on another table, and finally use the LOOKUPVALUE function.

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EfOpGnObtKZPrpKTh-... 

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MikeMCFRS]

@v-joesh-msft , Many thanks for your quick response. I have reviewed your solution and it seems to be heading in the directionthat I am looking for. However, I think the document I shared did not show you both tables. I have the cost table already prepared and i think you may find it in the first googledrive document. So that means I have table 1, which is what you demonstrated your solution, and I have table 2 which has my pre-made cost table from which I wish to look up cost by incident and unit numbers. I think if you look at the second table up, it will put your solution in context.

The goal is to look up each unit number by the cost set up in the second table. NB, THE SECOND TABLE DOES NOT HAVE UNIT NUMBERS, It shows the vehicle unit number and the cost of operating each. Please review that and let me know if you find it. If not ,I will send it again.

Many thanks and hope to read from you soon. 

[v-joesh-msft]

Hi @MikeMCFRS ,

I did not find the second table. The two tables you shared seem to be the same. Could you please share your other table?

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.