Transform Data from one single row into a table

[mohammed_essam]

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 use seperator charaters to split data before transpose, here is the sample below

Current format before transform:-

Desire Format we are looking for:-

Please advice how to transfore data from first figure to the second one, using DAX or M Language?

[mohammed_essam]

I have tried to do the following steps but didin't work with this case, Can't split use Space as I have some data values have space already between (some data values contains mulit words) ? can you give be more detailed steps ?

[amitchandak]

Can you share some sample data

[mohammed_essam]

Please Select;*
C1D;Classroom Disobedience
C2IN;Insubordination/ Defiance
C1PR;Profanity
C1CD;Classroom Disruption
GBC;General Behavior Concerns
C1EXT;Tardy
C2HCV;Honor Code Violation
C1MU;Mobile/ Unauthorized Use of Electronics
C1DC;Dress Code Violation
C2ISR;Sexual Harassment
C1UA;Found in Unauthorized Area
C1T;Tobacco Possession or Use
C1TR;Truancy
C1DT;Ignoring Assigned Detention
Other;Other/See Comments
C2V;Vandalism
MIR;Multiple Minor Infractions
BR;Bus Referral
BS;Bus Suspension
C2I;Bullying/Intimidation
C2FT;Fighting
C2HB;Harmful Behavior
C3DU;Illegal Substances - Use, Possession or Dist.
C1BV;Bus Violation
IIP;Initial Registration Probation
C3UPA;Physical Assault
C2DT;Disrespect of Teachers and/ or School
C2DM;Digital Citizenship Misconduct
C2P;Plagiarism

[amitchandak]

They all are in one line in actual data?

[mohammed_essam]

All data in one single cell.

[amitchandak]

@ImkeF, Please see if you can help.

[ImkeF · Accepted solution]

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

[mohammed_essam]

Hi @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"

[amitchandak]

 In Transform data/Edit Query, You have the option to split the column into rows based on a delimiter.

You have to do it twice.

1. Split on space

2. Then split ;

//Change order as required

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/