How to link csv file with excel file with a key column

[Anonymous]

Hello Power BI Community,

I'm a novice in Power BI and I'm currently struggling with linking data from a .CSV file with an excel file through a key column.

On the one hand, the .CSV file which I would name "Deliveries" contains a list of deliveries prepared during the day.

Basically, the "Deliveries' file looks like this :

Delivery number (String type)	Date of preparation (Date type in dd/MM/yyyy format)	Hour of preparation (Time type in hh:mm:ss format)
00001	01/04/2021	08:01:59
00002	01/04/2021	08:35:42
00003	01/04/2021	08:52:06
00004	01/04/2021	09:03:18
00005	01/04/2021	09:19:26
00006	01/04/2021	09:28:32
00007	01/04/2021	09:47:41
00008	01/04/2021	10:15:36
00009	01/04/2021	10:28:14
00010	01/04/2021	10:51:22

So in the "Deliveries" file I've got 10 deliveries prepared on 01/04/2021 :

- 3 deliveries prepared in the 8h time slot

- 4 deliveries prepared in the 9h time slot

- 3 deliveries prepared in the 10h time slot

On the other hand, the excel file which I would name "Capacity" contains the production capacity of the team for the day and would be updated before the start of the day (file dropped in a declared folder).

The "Capacity" file looks like this :

Date (Date type in dd/MM/yyyy format)	Hour time slot (Time type in hh:mm:ss format)	Amount of preparators planned for the time slot (float type)
01/04/2021	08:00:00	4,5
01/04/2021	09:00:00	6
01/04/2021	10:00:00	6
01/04/2021	11:00:00	3
01/04/2021	12:00:00	6
01/04/2021	13:00:00	6
01/04/2021	14:00:00	6
01/04/2021	15:00:00	4,5
01/04/2021	16:00:00	6
01/04/2021	17:00:00	6
01/04/2021	18:00:00	6
01/04/2021	19:00:00	3

I've created a measure named "hourly productivity target" based on the product of "Amount of preparators planned for the time slot" and a constant named "Target Productivity" = 10.

The DAX expression is : 

hourly productivity target = 'Capacity'[Amount of preparators planned for the time slot] * Measures[Target Productivity]

What I would like to calculate and show in the report is for each time slot of the day, the hourly productivity target versus the effective hourly productivity. The result would look like this :

Date (Date type in dd/MM/yyyy format)	Hour time slot (Time type in hh:mm:ss format)	Amount of preparators planned for the time slot (float type)	Hourly productivity target	Effective Hourly productivity	Delta
01/04/2021	08:00:00	4,5	45	3	-42
01/04/2021	09:00:00	6	60	4	-56
01/04/2021	10:00:00	6	60	3	-57
01/04/2021	11:00:00	3	30
01/04/2021	12:00:00	6	60
01/04/2021	13:00:00	6	60
01/04/2021	14:00:00	6	60
01/04/2021	15:00:00	4,5	45
01/04/2021	16:00:00	6	60
01/04/2021	17:00:00	6	60
01/04/2021	18:00:00	6	60
01/04/2021	19:00:00	3	30

The DAX expression for effective hourly productivity is 

= CALCULATE(COUNT(Deliveries[Delivery number]), Deliveries[Date of preparation] = TODAY()) + 0

When I create a table with only "Deliveries" data it looks good. I can show :

Date of preparation (Date type in dd/MM/yyyy format)	Hour time slot (Time type in hh:mm:ss format)	Effective Hourly productivity
01/04/2021	08:00:00	3
01/04/2021	09:00:00	4
01/04/2021	10:00:00	3

In the data model view, I create a join between "Deliveries" and "Capacity" through "Deliveries".Hour of preparation and "Capacity".Hour time slot.

And when I add the "Effective hourly productivity" in the table result it looks like it can't match the data between the 2 datasets.

Could you please help me and tell me what am i doing wrong ?

Feel free to ask for more information.

Best regards.

Vincent.

 

[Anonymous]

Thank you very much for all the details, Dedmon Dai.

It works perfectly ! 😁

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

You need to create a new column in Deliveries:

new Hour of preparation = TIME(HOUR(Deliveries[Hour of preparation (Time type in hh:mm:ss format)]),0,0)

Then you can create relationship between capcity and deliveries by the new column

For your expected output, you can use the following measure:

hourly productivity target = MAX(Capacity[Amount of preparators planned for the time slot (float type)])*10

effective hourly productivity = CALCULATE(COUNT(Deliveries[Delivery number (String type)]),FILTER(Deliveries,Deliveries[Date of preparation (Date type in dd/MM/yyyy format)] = MAX(Capacity[Date]) && Deliveries[new Hour of preparation] = MAX(Capacity[Hour time slot])))

Delta = [effective hourly productivity]-[hourly productivity target]

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

Best Regards,

Dedmon Dai