Forum Discussion

klehar's avatar
klehar
Helper V
3 years ago
Solved

Split a row into many rows

Hi I have a dataset that looks like this

 

I want that every subject should be in its own row and not under respective names

Note I cannot use Fill down as English and Science for Name = Rohan are in the same row (not seperate rows)

How can i solve this?

NameSubjects
RohanEnglish
 Science
MohitMath
 Science
JamesPhysics
 Maths
  
Need Help
  • klehar's avatar
    klehar
    3 years ago

    Ahmedx i found the solution

    you have to use #(lf) for next line split

     

9 Replies

    • klehar's avatar
      klehar
      Helper V
      3 years ago

      Here you have split it into 2 columns. 
      I want it in the same column but 2 rows

      Remeber : The delimitter I have here is just an enter key "Next line"

    • klehar's avatar
      klehar
      Helper V
      3 years ago

      Thanks for your prompt reply but this is how my data looks like.

      As I mentioned I cannot use the fillup or down feature here

      let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsrPSMxT0lEKTs5MzUtOjclzzUvPySzOUIrViVbyzc/ILEGW9E0sySgGS3kl5qYWI0sFZFQWZyYDJWMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Subjects = _t]),
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Subjects", type text}})
      in
      #"Changed Type"

      • Ahmedx's avatar
        Ahmedx
        Super User
        3 years ago
        let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsrPSMxT0lEKTs5MzUtOjclzzUvPySzOUIrViVbyzc/ILEGW9E0sySgGS3kl5qYWI0sFZFQWZyYDJWMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Subjects = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Subjects", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Subjects", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Subjects.1", "Subjects.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Subjects.1", type text}, {"Subjects.2", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Name"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"
    • klehar's avatar
      klehar
      Helper V
      3 years ago

      Ahmedx i found the solution

      you have to use #(lf) for next line split

       

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcs1Lz8kszlCK1YlWCsrPSMwDigUnZ6bmJaeCxYBc38QSiLxvfkZmCaZ8QEZlcWZyMZjrlZibWgzVAxSJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Subjects = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Subjects", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Name"}),
    #"Filled Up" = Table.FillUp(#"Replaced Value",{"Name"})
in
    #"Filled Up"