Forum Discussion

cottrera's avatar
cottrera
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

Split rows into columns by

Hi    I have a long single column with 'job number' and 'phone'data on the row.    Comments Job-1235 Phone-1494879604 Job-1358 Phone-149484757 Job-8695 Phone-01494988765 ...
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Use this

    let
        Source = Csv.Document(File.Contents("C:\Users\cottrera\OneDrive - xxxxxxxxxx.co.uk\Localz\DATA Sent.csv"),[Delimiter=",", Columns=6, Encoding=65001, QuoteStyle=QuoteStyle.None]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", type text}, {"Column6", Int64.Type}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "A"}, {"Column2", "B"}, {"Column3", "C"}, {"Column4", "D"}, {"Column5", "TO Clean"}}),
        #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column6", "A", "B", "C", "D"}),
        #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each Text.Contains([TO Clean], "Phone") or Text.Contains([TO Clean], "Job")),
        Custom1 = Table.FromColumns({Table.AlternateRows(#"Filtered Rows",1,1,1)[TO Clean],Table.AlternateRows(#"Filtered Rows",0,1,1)[TO Clean]},{"Job","Phone"})
    in
        Custom1