Manipulating Data Source

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.