Forum Discussion
cottrera
Post Prodigy
4 years agoSplit 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 ...
- 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
Vijay_A_Verma
Most Valuable Professional
4 years agoUse 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
Custom1cottrera
Post Prodigy
4 years agoAmazing thank you again 😀