Split rows into columns by

[cottrera]

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

I require a power query function that splits the 'Jobs' into one column and the 'Phone' into another columns.

Here is my expected result

Job	Phone
Job-1235	Phone-1494879604
Job-1358	Phone-149484757
Job-8695	Phone-01494988765

regards

Richard 

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spP0jU0MjZVitWJVgrIyM9L1TU0sTSxMLc0MzABC4JVGJtaoKswMTc1hyuwMLNENsIApMLSwsLcDCgaCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Comments = _t]),
    Custom1 = Table.FromColumns({Table.AlternateRows(Source,1,1,1)[Comments],Table.AlternateRows(Source,0,1,1)[Comments]},{"Job","Phone"})
in
    Custom1

[Vijay_A_Verma · Accepted solution]

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

[cottrera]

Amazing thank you again 😀

[cottrera]

Hi thank you for your quick reponse . You function works fine on the sample data I provided. How could I could add your code to my existing table in power bi?

Here is my code from the advanced editor.

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"))
in
#"Filtered Rows"

thank you RIchard