Forum Discussion
mohammed_essam
Helper I
Helper ITransform Data from one single row into a table
Hello, I have lookup data (Key, Value) saved inside one single data field, I would like to split, transform and transpose into a tow coulums table, but there are some challanges when I tried to u...
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
ImkeF
Community Champion
Community Championare 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
mohammed_essamHelper I
Helper IHi ImkeF
Thank you so much, instructions and steps you have shared really help me to figure it out. I didn't work at the beginning but I found there was a line break between each item of my long string, this is why splitter didn't work, so I have tweaked your code a little bit by using Splitter.SplitTextByDelimiter("#(lf)") until it finally worked.
Thank you again, please check code I am using below and advice if it need to be adjusted?
let
Source = Oracle.Database("XXXX"),
Navigation = Source{[Schema = "PS", Item = "GEN"]}[Data],
#"Choose columns" = Table.SelectColumns(Navigation, {"CAT", "NAME", "VALUET2"}),
#"Filtered rows" = Table.SelectRows(#"Choose columns", each ([CAT] = "logentrycodes" and [NAME] = "Discipline_IncidentType" )),
#"Choose VALUET2 column" = Table.SelectColumns(#"Filtered rows", {"VALUET2"}),
RemoveDots = Table.ReplaceValue(#"Choose VALUET2 column", ".", "", Replacer.ReplaceText, {"VALUET2"}),
RemoveHeaders = Table.ReplaceValue(RemoveDots, ";Please Select;*", "", Replacer.ReplaceText, {"VALUET2"}),
#"Split column by delimiter" = Table.SplitColumn(RemoveHeaders, "VALUET2", Splitter.SplitTextByDelimiter("#(lf)"), {"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", "Column1.29", "Column1.30", "Column1.31", "Column1.32", "Column1.33", "Column1.34", "Column1.35", "Column1.36", "Column1.37", "Column1.38", "Column1.39", "Column1.40"}),
GetAllValues = Table.AddColumn(#"Split column by delimiter", "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), {"Custom.1", "Custom.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter", {{"Custom.1", type text}, {"Custom.2", type text}}),
#"Filtered rows" = Table.SelectRows(#"Changed Type", each ([Custom.2] <> null)),
#"Renamed columns" = Table.RenameColumns(#"Filtered rows", {{"Custom.1", "ID"}, {"Custom.2", "Type"}})
in
#"Renamed columns"