Transform Data from one single row into a table

Hi mohammed_essam 

are you sure you didn't miss existing delimiters, when importing (as your sample data is perfectly splitted by line breaks).

Anyhow, the only general pattern in your dirty data I can spot is a change from Lowercase to Uppercase. Fortunately, there is a command for it in the UI now:

 

 

But first you need to replace "." by nothing:

 

 

And also replace the header with nothing (rename the table after the splitting has been done).

 

Here's the code of all steps to follow-up:

 

let
    Source = "Please Select;*C1D;Classroom DisobedienceC2IN;Insubordination/ DefianceC1PR;ProfanityC1CD;Classroom DisruptionGBC;General Behavior ConcernsC1EXT;TardyC2HCV;Honor Code ViolationC1MU;Mobile/ Unauthorized Use of ElectronicsC1DC;Dress Code ViolationC2ISR;Sexual HarassmentC1UA;Found in Unauthorized AreaC1T;Tobacco Possession or UseC1TR;TruancyC1DT;Ignoring Assigned DetentionOther;Other/See CommentsC2V;VandalismMIR;Multiple Minor InfractionsBR;Bus ReferralBS;Bus SuspensionC2I;Bullying/IntimidationC2FT;FightingC2HB;Harmful BehaviorC3DU;Illegal Substances - Use, Possession or Dist.C1BV;Bus ViolationIIP;Initial Registration ProbationC3UPA;Physical AssaultC2DT;Disrespect of Teachers and/ or SchoolC2DM;Digital Citizenship MisconductC2P;Plagiarism",
    #"Converted to Table" = #table(1, {{Source}}),
    RemoveDots = Table.ReplaceValue(#"Converted to Table",".","",Replacer.ReplaceText,{"Column1"}),
    RemoveHeaders = Table.ReplaceValue(RemoveDots,"Please Select;*","",Replacer.ReplaceText,{"Column1"}),
    #"Split Column by Character Transition" = Table.SplitColumn(RemoveHeaders, "Column1", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15", "Column1.16", "Column1.17", "Column1.18", "Column1.19", "Column1.20", "Column1.21", "Column1.22", "Column1.23", "Column1.24", "Column1.25", "Column1.26", "Column1.27", "Column1.28"}),
    GetAllValues = Table.AddColumn(#"Split Column by Character Transition", "Custom", each Record.FieldValues(_)),
    #"Removed Other Columns" = Table.SelectColumns(GetAllValues,{"Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom", "Custom", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Custom.1", "Custom.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}})
in
    #"Changed Type"

 

also attaching the file below