Forum Discussion

klehar's avatar
klehar
Icon for Helper V rankHelper 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...
Need Help
  • klehar's avatar
    klehar
    3 years ago

    Ahmedx i found the solution

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

     

Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper User
3 years ago

pls try this

klehar's avatar
klehar
Icon for Helper V rankHelper 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
    Icon for Super User rankSuper 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"