Hi; I have this data in excel file and I want to split the dash separated values into different rows within the same cell (splitting the dash separated values cell only).

I want to count the total credit hours for each SP & WD without replicating the credit hours, How can I do that in Power bi and Excel?

Any help will be appreciated.

CodePudding user response：

You should model this as two separate tables.

The basic approach here is to have two copies of the query. One where you remove SP, eg Course(Course Name, Credit hours, WD), and a second one like CourseSP(Course Name, SP). Then in the second one you can split SP to rows without duplicating Credit Hours. Then in your Datamodel create a 1-many relationship between Course and CourseSP.

CodePudding user response：

Just do a replace on the dash with a break. Your choice of #(lf) or #(cr)

If you bring if back into excel spreadsheet, format with word wrap

Counting credit hours seems totally unrelated. What's the connection to splitting by dash