How to Separate a column (, as delimiter) and add numerical value to each Individual Value

[sumanth_v]

Hello BI Community,

I have this requirement for my project that I am working on, where i need to calculate hours of each instructor separately. But the problem is some classes are taught by multiple instructors. Below are the details of the data and the problem.

My Data looks like - 

Problem - If you take 1st row as example, I need to calculate the Hours taught by instructor by class duration, where Abby =3 and John =3. Because they both taught the class together.

My final data should look like -

How to achieve this in power bi. Any help would be appreciated.

Thanks

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[sumanth_v]

Thanks for your reply, But that would create duplicate values right, In my case Class ID should be unique. Do you have any other solution.

[Ritaf1983 · Accepted solution]

Hi @sumanth_v 

Once you want to perform analysis at the instructor level, the class ID can no longer be unique at the table level.

This is not a tool issue, but rather a data modeling and logic issue.

The best practice for working with Power BI is to use a star schema.

This consists of a fact table with events/orders/transactions, etc.,

And dimension (lookup) tables that detail these transactions.

In your case, this would be an instructor table and a course table.

The large table will contain duplicates, while the small tables will be one-to-one.

more information about the modeling here :
https://www.datacamp.com/tutorial/data-modeling-in-power-bi-tutorial

The updated pbix is attached

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

[sumanth_v]

I accepted your comment as a solution, Is there a way to follow you in linkedIn?

[Ritaf1983]

@sumanth_v 
Happy to help, and yes of course.
linkedin : https://www.linkedin.com/in/rita-fainshtein/

Blog : Rita Fainshtein | Profile (madeiradata.com)

[Ritaf1983]

Hi @sumanth_v 
You can split the column of instructors names to rows with PQ :

Result :

pbix is attached

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