Manipulating Data Source

[jaw19883]

Hey all - Background: I work in K-12 Ed and am diving into Power Bi. I am working on a project that essentially will create a dashboard for every unit in a course all in one file. 

My problem continues to be the fact that my data source is an exported gradedbook in CSV/Excel. My biggest problem in that is the fact that the LMS includes all units/sections a student is enrolled in, in one field. So several records look like this in my data source: (keep in mind there are many other fields, such as test, quiz, notes, etc.)

Studnet Name      Student ID        Section                        
Student A             9999999            Unit 1 and Unit 2

Student B             1999999            Unit 1, Unit 2, Unit 3

Student C             2999999            Unit 4

What I want it to look like is this so I can isolate the Section, then easily apply filters, slicers, etc. based on Unit...

Studnet Name      Student ID        Section
Student A             9999999            Unit 1

Student A             9999999            Unit 2

Student B             1999999            Unit 1

Student B             1999999            Unit 2

Student B             1999999            Unit 3

Student C             2999999            Unit 4

I would eliminate the Student Name field at this point and create a different table with Name and ID and have a seperate table with ID and Section. Problem is I dont know:

1) Should I do this before I connect the data source to Power Bi or can I do it while transforming the data in Power Bi? AND

2) The syntax/methods to use. I assume it would be some variant of concatenate from Excel with the ability to read the whole string/array and break it up, but it has to bring/duplicate ID with it too. 

Here is a screenshot of data with names and Ids changed. Ids are unique and the key

Any help, ideas, or holes to go down appreciated. 

[v-yalanwu-msft · Accepted solution]

Hi, @jaw19883 

According to you description, You could replace and split columns ,then unpivot column. The steps are as follows
Step1: replace values

Step2: split column

Step3: unpivot columns

Step4: remove columns

Step5: format trim(Clear the first blank)

The final output is shown below::

In addition, M language is as follows:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pTUnNK1FwVNJRsoQAICs0L7NEwVAhMS9FAcw0UorVQah1UlAAqjGEqFYAAbgWHah6KG2Mos8ZqMwISRcUwDSbKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Studnet Name" = _t, #"Student ID" = _t, Section = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,"and",",",Replacer.ReplaceText,{"Section"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Section", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Section.1", "Section.2", "Section.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Studnet Name", type text}, {"Student ID", Int64.Type}, {"Section.1", type text}, {"Section.2", type text}, {"Section.3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Studnet Name", "Student ID"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}})
in
#"Trimmed Text"

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FrankAT]

Hi @ ,

this link may help: http://sqljason.com/2013/06/split-delimited-row-into-multiple-rows.html

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[jaw19883]

Using the language on the page of the link you provided I was able to construct a better google search and there is a way to do it in the Powery Query Editor by simply splitting columns by a delimeter, then using the advanced tools to have the result shown in rows. 

https://community.powerbi.com/t5/Desktop/How-to-split-the-the-Column-into-Multiple-rows/m-p/253361

[jaw19883]

Looks good. Can't wait to dig into it...Thank you